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
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.
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.
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