This Solver model uses the QUADPRODUCT function at cell I14 to compute the portfolio variance. | ||||||||

It can be solved for the minimum variance using either the GRG nonlinear solver or the Quadratic Solver. | ||||||||

Stock 1 | Stock 2 | Stock 3 | Stock 4 | Stock 5 | Total | |||

Portfolio % | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 100.00% | ||

Expected Return | 7.00% | 8.00% | 9.50% | 6.50% | 14.00% | |||

Linear QP Terms | 0 | 0 | 0 | 0 | 0 | |||

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% | Variance | 0.68% | |

Stock 4 | -0.50% | 1.20% | 0.65% | 0.40% | 1.00% | Std. Dev. | 8.22% | |

Stock 5 | 1.60% | -0.85% | 0.75% | 1.00% | 2.00% | Return | 9.00% | |

Problem | ||||||||

An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Markowitz method, what is the | ||||||||

best combination of stocks to minimize risk for a given return? The variances are known for each stock, as are the | ||||||||

covariances between all stocks. The returns for all stocks are also known. | ||||||||

Solution | ||||||||

1) The variables are the percentage allocations of our funds to invest in each stock. In this worksheet, the variables | ||||||||

are given the name Allocations. The sum of the allocations (which must be 100%) is computed in the cell named | ||||||||

Total_Portfolio. | ||||||||

2) The constraints are very simple. First there are the logical constraints: | ||||||||

Allocations >= 0 via the Assume Non-Negative option | ||||||||

Total_Portfolio = 1 | ||||||||

Then there is a constraint that the portfolio return should be at least a certain target value (9% in this example). This | ||||||||

return is calculated in the cell named Portfolio_return: | ||||||||

Portfolio_Return >= 0.09 | ||||||||

3) The objective is to minimize portfolio variance, which is calculated according to the Markowitz method in the cell | ||||||||

named Portfolio_Variance. | ||||||||

Remarks | ||||||||

In this worksheet, we use the QUADPRODUCT function to compute the portfolio variance. If you see #NAME? on | ||||||||

this worksheet, you need to open the add-in (DOTPRD32.XLL or DOTPROD.XLL) that provides QUADPRODUCT. | ||||||||

In the Full Markowitz worksheet, we calculate the portfolio variance 'manually' without using QUADPRODUCT. | ||||||||

The Markowitz method can only be used if all the variances of individual stocks, and the covariances between each | ||||||||

pair of stocks are known. In this model we assumed that all the terms are given. In the Full Markowitz worksheet we | ||||||||

actually calculate the variances and covariances from a history of stock prices. | ||||||||