An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks. 'What is the best combination of stocks to achieve a given rate of return with the least risk? Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Total Portfolio % 0.00% 3.57% 0.00% 0.00% 96.43% 100.00% Expected Return 7.00% 8.00% 9.50% 6.50% 15.00% Variance/Covariance Matrix Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Stock 1 2.50% 0.10% 1.00% -0.50% 1.60% Stock 2 0.10% 1.10% -0.10% 1.20% -0.85% Stock 3 1.00% -0.10% 1.20% 0.65% 0.75% Stock 4 -0.50% 1.20% 0.65% 0.40% 1.00% Stock 5 1.60% -0.85% 0.75% 1.00% 2.00% Variance 0.0180255 Variance Terms 0.00% -0.03% 0.00% 0.00% 1.83% Std. Dev. 13.43% Des. Ret 14.75% Return Terms 0.00% 0.29% 0.00% 0.00% 14.46% Return 14.75% This worksheet includes a Markowitz portfolio model that can be optimized by the Solver to find the minimum variance portfolio for a given target rate of return. We then use VBA (Visual Basic Application Edition) code to set the target rate of return to different values (from 10% to almost 15%) and run the Solver to optimize the model for each target return. The VBA code stores the target returns and resulting portfolio variances in cells J21 through K40, which are linked to the X-Y plot shown to the right. When you press the button labeled 'Create Frontier', the VBA code is run and the resulting efficient frontier is drawn on the embedded chart. To see the VBA code controlling the Solver, select Tools Macro... Visual Basic Editor, or press Alt+F11. In the VBA window, in the left-hand Project list window double-click on Modules, then double-click on Module1. To successully run the code and create the chart, you may need to choose Tools References... in the VBA Editor and click to set a check mark next to 'Solver'.