| 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 % | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 100.00% | ||
| Expected Return | 8.70% | 7.10% | 9.40% | 10.70% | 6.90% | |||
| Variance/Covariance Matrix | ||||||||
| Stock 1 | Stock 2 | Stock 3 | Stock 4 | Stock 5 | ||||
| Stock 1 | 0.03% | 0.12% | 0.03% | 0.06% | 0.01% | |||
| Stock 2 | 0.12% | 1.23% | 0.20% | 0.16% | -0.05% | |||
| Stock 3 | 0.03% | 0.20% | 0.04% | 0.04% | -0.01% | |||
| Stock 4 | 0.06% | 0.16% | 0.04% | 0.51% | 0.02% | |||
| Stock 5 | 0.01% | -0.05% | -0.01% | 0.02% | 0.05% | |||
| Variance Terms | 0.01% | 0.07% | 0.01% | 0.03% | 0.00% | Variance | 0.12% | |
| Std. Dev. | 3.49% | |||||||
| Return Terms | 1.74% | 1.42% | 1.88% | 2.14% | 1.38% | Return | 8.56% | |
| Historical data (Returns) on stocks | ||||||||
| Stock 1 | Stock 2 | Stock 3 | Stock 4 | Stock 5 | ||||
| Period 1 | 10.00% | 15.00% | 12.00% | 18.00% | 5.00% | |||
| Period 2 | 12.00% | 17.00% | 13.00% | 16.00% | 8.00% | |||
| Period 3 | 8.00% | 4.00% | 9.00% | 3.00% | 10.00% | |||
| Period 4 | 7.00% | -8.00% | 7.00% | 4.00% | 9.00% | |||
| Period 5 | 9.00% | 15.00% | 9.00% | 8.00% | 5.00% | |||
| Period 6 | 7.00% | 22.00% | 11.00% | 10.00% | 4.00% | |||
| Period 7 | 8.00% | 3.00% | 9.00% | -3.00% | 4.00% | |||
| Period 8 | 6.00% | -14.00% | 6.00% | 15.00% | 6.00% | |||
| Period 9 | 9.00% | 2.00% | 8.00% | 20.00% | 8.00% | |||
| Period 10 | 11.00% | 15.00% | 10.00% | 16.00% | 10.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? In this model, we calculate stock returns, the variance | ||||||||
| of each stock, and the covariances between stocks, using the Excel functions AVERAGE, VARP and COVAR. | ||||||||
| Solution | ||||||||
| 1) The variables are the percentage allocations of our funds to invest in each stock. In this worksheet, the variables | ||||||||
| are cells B6 to F6 (they are not given a name). The sum of the percentage allocations (which must be 100%) is | ||||||||
| computed in cell H6. | ||||||||
| 2) The constraints are very simple. First there are the logical constraints: | ||||||||
| B6:F6 >= 0 via the Assume Non-Negative option | ||||||||
| H6 = 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 cell I19, as the sum of the weighted stock returns: | ||||||||
| I19 >= 0.09 | ||||||||
| 3) The objective is to minimize portfolio variance, which is calculated from the weighted individual stock variances | ||||||||
| and covariances according to the Markowitz method in cell I17. | ||||||||
| Remarks | ||||||||
| The stock variances and covariances are calculated in cells B11:F15 from the historical price data in cells B23:F32. | ||||||||
| Using historical price data to compute estimates of stock returns, variances and covariances is only a first step in | ||||||||
| investment planning. Stock returns, as well as variances and covariances, vary over time. Investors often rely on | ||||||||
| security analysts to provide better estimates of these quantities for the future. | ||||||||
