Posted on

Markowitz Efficient Frontier Spreadsheet

Efficient Frontier Spreadsheet Go to the sheet called “Portfolio Standard Deviation”. Keep pressing F9 and watch the charts change.  Stocks A, B and C are real ones. The returns don’t vary nor do the Variances of each.

What does change is the weighting. When you press F9, close to 14,000 random portfolios are created and each portfolio has slightly different weightings for each of the three stocks. You will notice a sort of “convergence” suggesting that 14,000 random portfolios is quite enough: it generates the efficient frontier, since the shape of the scatter plot hardly changes.  In other words, the chart keeps on producing returns and volatilities for 14,000 differently weighted portfolios and you can choose which you like best along the efficient frontier.

Repeat each rebalance period: e.g.each week/month/quarter, such a system would either have to generate sufficient random portfolios to create the necessary approximate “convergence“ you can see demonstrated here or would need to code an algorithm to generate it for you using optimisation or Markowitz’s Critical Line Algorithm. In any event, the results will be similar whichever way you do it.

Each month for instance the system could choose a portfolio of say 60 ETFs with a target volatility of say 5%, 10% and 15% representing low, medium and high risk strategies. The best return for the target volatility will govern portfolio weights for the following month. It’s that simple.

As you can see at heart it is very simple to understand the concepts involved in the efficient frontier.

You either pick your target vol and choose the portfolio weighting with the highest return for that vol or choose a volatility target and then the highest CAGR for that target from the scatter plot.

Of course in practice you would want to automate this but there are plenty of examples on the Quantopian forum.

I found the spreadsheet route a very simple way to get my head around a basically simple concept which is all too often wrapped in useless jargon and maths speak.

Leave a Reply