|
Solver.com
From Frontline Systems, developers of the Excel Solver.
|
|
|
|
|
Portfolio
Optimization - Single-Index Method |
|
|
|
|
| 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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
