An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks. What is the best combination of stocks to get the maximum return with a given variance (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% Beta-Coefficient 0.577381 1.672619 0.64881 0.934524 0.767857 Alpha-Coefficient 0.047738 -0.04274 0.049881 0.043452 0.016786 Residual Variance 0.000261 0.014236 0.000353 0.006059 0.000389 Weighted Variance 0.00001 0.00057 0.00001 0.00024 0.00002 Portfolio Variance 0.12% Portfolio Return 8.56% Historical data (Returns) on stocks Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Market Period 1 10.00% 15.00% 12.00% 18.00% 5.00% 8.00% Period 2 12.00% 17.00% 13.00% 16.00% 8.00% 10.00% Period 3 8.00% 4.00% 9.00% 3.00% 10.00% 9.00% Period 4 7.00% -8.00% 7.00% 4.00% 9.00% 7.00% Period 5 9.00% 15.00% 9.00% 8.00% 5.00% 4.00% Period 6 7.00% 22.00% 11.00% 10.00% 4.00% 6.00% Period 7 8.00% 3.00% 9.00% -3.00% 4.00% 5.00% Period 8 6.00% -14.00% 6.00% 15.00% 6.00% 5.00% Period 9 9.00% 2.00% 8.00% 20.00% 8.00% 6.00% Period 10 11.00% 15.00% 10.00% 16.00% 10.00% 8.00% Problem An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Sharpe Single-Index method, what is the best combination of stocks to maximize return for a given level of risk (variance)? Solution 1) The variables are the percentage or fractional allocations of our funds to invest in each stock. In this worksheet, the variables are given the name Portfolio_fractions. The sum of the allocations (which must be 100%) is computed in the cell named Portfolio_Total. 2) The constraints are very simple. First there are the logical constraints: Portfolio_Fractions >= 0 via the Assume Non-Negative option Portfolio_Total = 1 Then there is a constraint that the portfolio variance should be no more than 0.0003 (in this example). The portfolio variance is calculated in the cell named Variance: Variance <= 0.0003 3) The objective is to maximize portfolio return which is calculated according to the Sharpe method in the cell named Return. Remarks This model uses historical data on the stocks and market to calculate the returns and variance of the stocks and the portfolio. It also uses Excel's regression tools to estimate the alphas and betas, as well as the residual error, necessary to use the Sharpe Single Index method. A different approach to this problem would be to minimize risk for a certain return. It is easy to adjust the Solver to do this. Simply change the set cell to be the variance and adjust the constraint to hold the return at a given level.