Portfolio Optimization - Markowitz Method |
|
|
|
|
|
| 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'. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|