Following up on prior posts where we built a simple trading model in excel... As promised, we are now expanding into the concept of portfolio risk.  Why is this important?  Each of us likely holds a variety of different crypto currencies.  A simple but naive way to diversify between them is simply to spread the capital out equally across each coin.   However, the risk levels of each coin are all different (excluding stable coins here) so if you don't look under the hood, you may end up with one particular coin that dominates the entire portfolio in terms of risk.  This can and usually does lead to larger than expected portfolio losses.  Knowing how much each coin contributes to the total risk can help you size each position according to your conviction level in that particular asset. 

In this post, I'll illustrate an example of portfolio risk and also show you how to build your own in excel with screenshots pasted below.   When thinking about risk in a portfolio context, the most common measure of it by far is the volatility (vol) of the returns.  In most cases, as volatility begins to rise, losses start to accrue.  This doesn't always have to be the case as one could experience a high vol period of positive only returns but this is exceedingly rare over investment horizons of reasonable length.  Usually, rising vol (rising risk) comes with losses. 

So the question becomes, how do we measure the total vol of a portfolio and then how much each individual position adds to that total.  Below is a simple example of 3 coins:  BTC, ETH, and XRP.   I use daily returns from 1/1/20 to 7/9/2021. 

The portfolio uses a naive allocation strategy where we the capital equally distributed across each of the 3 coins. 

33.4% to BTC

33.3% to ETH

33.3% to XRP

This gives us a portfolio with a total annualized vol of 94.3%.   Of note, relative to traditional assets like stocks, bonds, commodities, this is extremely high.  When we look at how much risk each coin contributes to the total portfolio risk though, despite having equal capital allocations, the risk contributions are quite different.   You'll see below that:

XRP contributes 40.7% of the total risk.

ETH contributes 31.9% of the total risk.

BTC contributes 21.7% of the total risk. 

The charts below illustrate the magnitude of the differences. 

Of note, these numbers do reflect the impact of the returns having correlations less than 1.  Said another way, all of this takes into consideration how the coins move relative to one another so the impact of diversification is properly accounted for.  

The screenshots below show you how you can calculate this yourselves with the coins you own.  Of note, many of these calculations are in array format.  If you're not familiar with that, you'll need to hit ctrl-shft-enter at the same time to get an answer.  The yellow highlighted cells show which numbers are arrays that you'll need to use this for.  The blue highlighted cells are the weights to each coin that you can toggle.

Lastly, the check comes in that the sum of the total risk contribution of each individual asset = the total portfolio risk.  I've circled these two numbers so you can see which ones to check.

Hope this is helpful and happy building.  If you found this useful, please follow and tips are always appreciated!

The daily returns are arranged below the calculations and go from E:21 to G:568.   You can enter your desired allocations in E8:G8 (blue).  As always, this is not investment advice and please double check all my work!  Its correct though as I show you below a simple way to check it. 

Step 1#  Calculate the covariance table.  You can do it in E4, hit ctrl shft enter then drag it down and over.

Step #2:  Calculate the Annualized volatility of each individual coin on a stand alone basis (informational purposes).

Step#3:  Calculate the total portfolio variance. 

Step #4:  Calculate the total portfolio volatility.

Step 5:  Calculate the annualized volatility.

Step #6:  Calculate the risk contribution of each coin.  Highlight the entire range for this one when you enter the formula (E:10 to G:10).  Ctrl Shft Enter.

Step #7: Calculate the annualized risk contribution of each coin.    The 94.3% in H11 is the sum of the E11:G11.  If you've done this correctly, it should match the total portfolio annualized vol in D16.