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