My friend and old colleague, Tom Anichini over at PortfolioWizards.com, has built a fantastic workbook for locating the tangent portfolio on an efficient frontier. Tom’s workbook is based on using up to 20 asset classes, which should be more than twice enough for most models. However it should also not be too difficult to adapt the matrix functions and arrays to whatever number you need. The beauty of the workbook is the use of matrix functions which makes the sheet fast and intuitive to use. It is an excellent example of just how powerful Excel can be when you know both how to use it and the math behind the finance.
To keep things fast, Tom abstracts away from returns forecasting and assumes a zero risk-free rate. The output is a series of portfolios broken down by expected volatility. Most investors typically treat the volatility of their portfolio as either a by-product of their investing, or, at best, as a secondary goal. That is if they pay attention to it at all. This is exactly the wrong way to think about structuring a portfolio. The risk component is every bit as important, and intimately tied to the return component.
In recent times with these extraordinary low returns to capital, rf=0 may not be such a terrible assumption on Tom's part, but the lack of an expected returns component makes the workbook a little less than useful for tactical asset allocations or other serious work. That said, like all good quant tools, the efficient frontier workbook does a good job of providing information which may not necessarily be intuitive or obvious, in an easy to understand manner. It is this information when combined with the users experience, knowledge, and judgement that leads to insight. Wisdom can also be gained by varying the inputs to the workbook and running “what if” scenarios and sensitivity analysis.
Download the workbook yourself and give it a try. After playing with it for a little while I have decided to add more REITs to my portfolio. So I am off to research some real estate funds.