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