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