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.