Excel Spreadsheet Model for Portfolio Optimization

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

Classic Two Security Example Portfolio Optimization

The video tutorial below demonstrates two methods of portfolio optimization in Excel. The first example covers the classic textbook example of the two security case. This gives us the basic idea of diversification in investing. The tutorial discusses how the optimal wieghts are determined graphically and mathematically.

Multi-security Example Portfolio Optimization

The turtorial then moves into the more complex situation where multiple securites are present, thus improving diversification. While it is possible to derive weights using purely mathematical means, we take advantage of Excel's optimization engine Solver to determine optimal captial allocation. The most difficult task with the multi-security model is calculating the portfolio varinace and standard deviation.

To simplify this calculation, we use data analysis toolpak to geenrate a covariance matrix, and then use two of Excel's array functions, MMULT and TRANSPOSE to arrive at a solution.

Weaknesses of Portfolio Optimization

The primary weakness of portfolio optimization is the reliance on historical data to determine portfolio weights. This is often addressed with either subjective methods and or more complex evolutionary models or the combination of Monte Carlo simulation and optimization

You can download the Excel file used in the video here.

Portfolio Optimization in Excel