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