Crypto Portfolio Analytics

With Coinbase (NASDAQ: COIN), the largest Cryptocurrency exchange getting listed, and the crypto rally that we’ve seen, especially bitcoin, the cryptocurrency market is quite a new sensation for many. Nakamoto first introduced cryptocurrencies in 2008, and since 2013 it has seen massive growth.
However, popularity and growth come with many risks as the cryptocurrency market is known for its volatility and speculation. If you’re new to the world of cryptocurrencies, check out our detailed article on Cryptocurrencies here.
But, if you’re a crypto investor or trader and are looking to manage your portfolio, we’ve got it covered for you; keep reading!

Tracking and Analyzing your Crypto Portfolio (Excel Template)

Cryptocurrency investors usually have a portfolio of cryptos varying from 3 to 20 and more because managing the portfolio requires a lot of effort, time, and energy and is even complicated. Having them managed all in an excel sheet along with your other investments makes it a lot less cumbersome, isn’t it? Well, that’s what marketxls is for, to make your investment journey easy and hassle-free.

You can analyze your portfolio and manage it with our template, which provides EOD data for your cryptos, risk-return metrics, charts, and graphs to get a visual summary. 

The template has a sheet that provides a complete list of Cryptocurrency codes you can use to add to your portfolio analysis template to run the analysis.

The Parameters

alt="Crypto Portfolio Analytics">LHDXjbTw1WKddK37TR0EqS-HB26YOjCzL7dLJTfdGrz” alt=”Crypto Portfolio Analysis”/>

The various portfolio ratios will be calculated as we input the codes. It calculates the Sortino Ratio, the CAGR, Maximum drawdown, Portfolio Beta, Volatility, Efficient Frontier chart, Sharpe ratio, and much more, giving you the power to see your portfolio as large fund managers do!

Let’s break down each parameter used in detail:

1. Sortino Ratio

The Sortino ratio measures the risk-adjusted return for the portfolio. It differentiates harmful volatility from total overall volatility and uses the portfolio’s downside deviation instead of the total standard deviation of portfolio returns.

2. Value at Risk 

Value at Risk measures the level of financial risk of the portfolio for a specific time frame. It assesses the potential loss for the portfolio and the probability of the same, for, eg. A 5% one-month VaR of 1% representing a 5% chance of the asset declining in value by 1% during the one-month time frame. 

3. Portfolio Volatility

Volatility is a measure of risk. The portfolio volatility indicates how widely the total value of the portfolio appreciates or depreciates. Standard deviation and correlation among the stocks in the portfolio are two main elements of the risk in the portfolio.

4. CAGR

CAGR or the Compound Annual Growth Rate measures the annual growth of the portfolio over a while. It measures how much you have earned every year during a given time frame.

5. Portfolio Beta

It measures the portfolio’s systematic risk, which is equal to the weighted average of the beta coefficient of all the individual stocks in a portfolio.

6. Treynor Ratio

Treynor Ratio determines how much excess return is generated for each unit of risk taken on by a portfolio. Excess return is the return earned in addition to the return earned by investment in risk-free security.

7. Sharpe Ratio

The Sharpe ratio also measures the return per unit of risk taken. However, here the risk-adjusted return measured in Sharpe ratio is not just the systematic risk but the portfolio risk (i.e., σp)

8. Maximum Drawdown

A maximum drawdown (MDD) is the maximum observed loss from a peak to a portfolio trough before a new peak is attained. Maximum drawdown is an indicator of downside risk over a specified period.

9. Portfolio Efficient Frontier Chart

The efficient frontier of the portfolio is optimal portfolios that offer the highest expected return for a defined level of risk or the lowest risk for a given level of expected return. It plots the expected return on a portfolio of investments on the y-axis and its risk measured by its standard deviation on the x-axis.

The Crypto Portfolio Analytics Template Explained

The Cryptocurrency codes and their respective weight that you hold can be added to the cells.  The list of crypto currency symbols are in the sheet “All Crypto Symbols.” As you can observe the necessary ratios will be calculated automatically once the cryptocodes and the weights have been added. The Efficient Frontier Graph and the Monthly returns is also automatically generated. The Wealth Index calculates the portfolio value of $10,000 invested in the beginning of the period over time.

Note: MarketXLS only provides EOD data for cryptocurrency at the moment. 

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.

References

Efficient Frontier Definition. https://www.investopedia.com/terms/e/efficientfrontier.asp

Maximum Drawdown (MDD) Definition. https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp