Member preview

Build a Portfolio of Cryptocurrencies using Modern Portfolio Theory

Are you ready to strike rich in the modern day gold rush? In this article, I will show you how to build a portfolio of cryptocurrencies using systematic analysis based on modern portfolio theory.

In a previous article, I introduced you to the wonderful world of investing in cryptocurrencies. If this is the first time you have heard about them, I recommend you read that article and come back to this only when you have some experience of buying and holding on to cryptocurrencies. In this article, we will take a look at a systematic method for building and managing your portfolio of cryptocurrencies.

What is Modern Portfolio Theory (MPT)?

The name modern portfolio theory is kinda of a misnomer, since MPT is neither new nor a theoretical framework. It is a mathematical framework for building a portfolio of assets so that the expected return is maximised for a given amount of risk. As an example take a look at the above figure, on the Y-axis we have the average annual returns of an asset while on the X-axis we have the risk measured as the standard deviation of annual returns. If we consider just two asset classes — Stocks and Bonds, we can plot the returns of different portfolios that combine these classes together, from a combination that is 100% bonds to one that is 100% stocks.

The bullet shaped parabola curve that you get with this plot is called the efficient frontier. It is efficient in the sense that for a given amount of risk it represents the portfolio with the higher possible returns. So, in other words if you are not trading on the frontier you are exposing yourself to systemic risk. Another thing to note is that you can increase your expected level of returns by combining different assets together for the same amount of risk. As you can see from the graph having a portfolio with a combination of bonds and stocks actually gives better returns for the same risk compared to one with only 100% bonds.

Assemble a Cryptocurrency Portfolio

Now that you know what is MPT, you may wonder how you can use it to build a diversified portfolio of cryptocurrencies that maximises your returns. There are hundreds of currencies on offer today at various exchanges. The first thing you need to decide is the exchange you are going to use to trade. Some of the popular ones are GDAX, Poloniex and Bittrex. It is a good idea to trade only on the biggest and popular exchanges and avoid new ones since they have questionable security and typically low volumes. Opening an account with an exchange would typically require you to complete their KYC procedures and will take a few days. Once you have your account set up you are ready to trade with cryptocurrencies.

The goal of MPT is to help you come up with the most optimal combination of the assets and allow you to trade on the efficient frontier curve. In order to do this analysis you will need Microsoft Excel enabled with Solver Add-in. The Solver Add-in is not enabled by default so we need to add it using the following steps.

  1. Go to File menu on Excel:

2. Click on the Options button, it will open up the Excel Options menu:

3. Then choose Add-ins from the left menu:

4. Select the Solver Add-in and click on Manage Excel Add-ins Go button at the bottom of the screen:

5. Here you need to check the Solver Add-in box and click OK.

Once enabled you will be able to see the Solver in the Excel Data Ribbon on the far right side as shown in the screenshot above.

Now, we need to collect the data we need to do the analysis. You can do the MPT analysis over any period of time, as an example we will do it for the last one year and for the top 5 cryptocurrencies based on market value. Where can we find the historical data for these currencies? Thankfully, Yahoo Finance now provides a full listing and data about 100s of cryptocurrencies. You can download the historical data in CSV format from the individual cryptocurrency page (e.g. BTC/USD).

Once we have the data, we will create a new Excel spreadsheet with a tab for Data that records the percentage gains made per month as shown below:

Make sure that the returns are all in percentages and across the same time period (for our example this is monthly). The first few rows for Bitcoin Cash (BCH) are zeros because the currency did not exist before the fork. Create another tab labeled MPT, that is where we will do our analysis and eventually once we are done it will look something like below:

There are a lot of details on the image above, so let’s unpack them step by step and see how we can use the Solver Add-in to produce optimal portfolios for cryptocurrencies.

Creating the Covariance Table
We want to figure out the how related are different assets to each other. This will help find the right combination, ideally you want your assets to not be correlated so that if one of them goes down the other goes up and balances the returns. Using the historical data we are going to compute co-variance for all possible combinations of the cryptocurrencies. Since we have 5 assets here, we need 5x5 = 25 cells in our covariance table.

Excel already provides the formula to compute covariance of two series of data. Just use =COVAR(Data!B2:B13,Data!B2:B13) in the formula bar to compute the covariance based on the numbers in the Data tab.

Do that for all combinations of the assets (25 of them). Next, we will add the weights, shown in red in the screenshot above. These weights are going to be computed automatically by the solver, initially just allocate 100% to BTC and 0% of all others (A13 to A17). We will also constraint the weights on the 11th row to be the same, so enter =A13 in the cell C11, =A14 in cell D11 and so on.

Once we have initial weights set up and the covariance computed, we will add another row 18 for the totals. For weights it is just the sum of all the rows from A13 to A17. For the covariance, we will take the sum of product of the weights and covariance. Thus, set =C11*SUMPRODUCT(A13:A17,C13:C17) in the formula bar for cell C18.

This number computes the contribution to the overall returns based on the weights allocated for each combination of the asset. Do this for all 5 columns C18 to G18. This finishes the covriance table, and now we can move to the returns table.

Computing the Returns Table
The returns table is easy to compute, we first take the average monthly returns (=AVERAGE(Data!B2:B13))and then multiply them by 12 to compute the annual returns.

Then we will set the target return, it will be the sum of products of the weights with the annual return, so we enter =SUMPRODUCT(A13:A17,K13:K17) in formula bar for the cell J19. The standard deviation is used to capture the level of risk a particular asset has, it can be computed using the standard formula based on the square root of variance, so enter =SQRT(SUM(C18:G18))*SQRT(12) in cell J20. Note, that we need to multiple it by √12 since we want to compute standard deviation based on annual returns.

Using the Solver to Optimize
Given the covariance and returns data we can now use the solver to optimize the weights for each cryptocurrency based on the constraint that we seek to minimize the standard deviation (since it represents risk). To do that, just enter a target return (say 100) in the highlighted cell (K19). Now press the solver button on the Data ribbon. We will be greeted with the parameters menu as shown below:

First we need to set our objective, here we are trying to minimize standard deviation, thus we choose cell $J$20 and Min in the form. Then, we need to set the variables, here the weights for individual assets are the variables we are looking to solve for, so we put $A$13:$A$17 in the By Changing Variable Cells box. Now, we need to add the constraints, so here we have three constrains:

  1. Since we are looking to achieve a target return we can set $J$19=$K$19.
  2. The weights of the assets cannot be negative, so we set $A$13:$A$17 ≥0.
  3. The sum of all weights cannot be more than 100%, thus we set the total $A$18 = 1.

Once we set the constrains we can click on the solve button to let the solver analyze the system of equations and find us an optimal solution.

The solver may either find a solution as shown above or the target returns may not be achievable, that case the solver will say that the system is infeasible. We can keep the solver solution and it would update the weights in the spreadsheet to the values that give the most optimal portfolio. We can set different target returns that are progressively higher and copy down all the weights that are computed. Finally, we will have something that looks like this:

If you plot the returns with the risk (standard deviation), you can also generate the nice bullet shaped efficient frontier curve as shown above.

Now the question is which portfolio do you use?

It depends on the amount of risk you are willing to take, so if you are willing to take a risk of say 60 standard deviation, then the best portfolio for you is a combination of BTC (60%), BCH (25%) and LTC (16%). Once you buy these cryptocurrencies from an exchange you will have your initial portfolio.

The next questions is how often do you do this analysis and re-balance the portfolio?

For cryptocurrencies since they are quite volatile and have low trading fees, we recommend doing it at least every month. That will ensure that you are always invested in a portfolio that maximizes your returns.

The complete Excel spreadsheet with the MPT analysis for top 5 cryptocurrencies is available here — [xlsx]. You can use it as a basis to build your own portfolio following the steps described in this article. It is quite easy to extend the spreadsheet to include other cryptocurrencies by just adding more data.