Project 2 (Efficient portfolios and Efficient frontier)
You are assigned with five stocks and you are expected to build the investment opportunity set using those five stocks. Please collect monthly stock price from FINANCEYAHOO and employ the data range from Jan 1, 2015 to June 30, 2019. Then using the risk-free rate of 0.33%, you are expected to develop the complete portfolio and develop the efficient frontier of these six securities (T-bill and five stocks).
1) Collect monthly stock price data from Jan 1, 2015 to June 30, 2019 (See my example with the sheet entitled Data)
2) Compute monthly stock returns and standard deviation (See my example with the sheet entitled Data)
3) Develop Table of Covariance of these five stocks (See my example with the sheet entitled Data)
4) Find the Minimum-Variance risky portfolio (See my example with the sheet entitled MVP)
5) Find the Efficient Risky portfolios (See my example with the sheet entitled EF , Result, and Graph)
6) Develop the capital market line using T-bill rate of 0.33% (See my example with the sheet entitled MVP, Result, and Graph)
Company name | Ticker |
Clorox | CLX |
Colgate Palmolive | CL |
Caterpillar | CAT |
Best Buy Co. | BBY |
Apple | AAPL |
Data
Date | Apple | WMT | MSFT | Apple | WMT | MSFT | Asset A | Asset B | Asset C | ||
1/1/00 | 10.97 | 44.68 | 20.69 | Apple | WMT | MSFT | |||||
2/1/00 | 11.63 | 49.42 | 19.85 | 6.02% | 10.61% | -4.06% | |||||
2/29/00 | 13.15 | 49.63 | 18.66 | 13.07% | 0.42% | -5.99% | |||||
3/31/00 | 12.54 | 47.4 | 19.56 | -4.64% | -4.49% | 4.82% | Table of Covariance | ||||
5/2/00 | 13.64 | 46.45 | 19.63 | 8.77% | -2.00% | 0.36% | Asset A | Asset B | Asset C | Things in dark grey are input parameters | |
5/31/00 | 15.82 | 43.76 | 21.37 | 15.98% | -5.79% | 8.86% | Asset A | 1.15% | -0.02% | 0.30% | Things in light grey are computed intermediate things |
6/30/00 | 15.73 | 44.18 | 21.32 | -0.57% | 0.96% | -0.23% | Asset B | -0.02% | 0.21% | 0.05% | Things in yellow are labels |
8/1/00 | 16.77 | 44 | 20.49 | 6.61% | -0.41% | -3.89% | Asset C | 0.30% | 0.05% | 0.46% | Things in red are “Solver” inputs or outputs |
8/31/00 | 18.84 | 44.45 | 20.75 | 12.34% | 1.02% | 1.27% | |||||
9/30/00 | 25.48 | 45.05 | 20.99 | 35.24% | 1.35% | 1.16% | Table of expected returns and risk | ||||
10/31/00 | 32.6 | 43.49 | 22.43 | 27.94% | -3.46% | 6.86% | E(return) | Standard Deviation | |||
11/30/00 | 31.32 | 44.24 | 22.35 | -3.93% | 1.72% | -0.36% | Asset A | 3.94% | 10.74% | ||
1/2/01 | 37.39 | 43.89 | 21.99 | 19.38% | -0.79% | -1.61% | Asset B | 0.56% | 4.56% | ||
1/31/01 | 43.63 | 43.22 | 21.11 | 16.69% | -1.53% | -4.00% | Asset C | 0.70% | 6.80% | ||
2/28/01 | 40.53 | 42.09 | 20.28 | -7.11% | -2.61% | -3.93% | |||||
3/31/01 | 35.07 | 39.6 | 21.23 | -13.47% | -5.92% | 4.68% | |||||
5/1/01 | 38.67 | 39.8 | 21.72 | 10.27% | 0.51% | 2.31% | |||||
5/31/01 | 35.8 | 40.62 | 20.91 | -7.42% | 2.06% | -3.73% | |||||
6/30/01 | 41.48 | 41.59 | 21.56 | 15.87% | 2.39% | 3.11% | |||||
7/31/01 | 45.6 | 38.01 | 23.12 | 9.93% | -8.61% | 7.24% | |||||
8/31/01 | 52.14 | 37.04 | 21.72 | 14.34% | -2.55% | -6.06% | |||||
10/2/01 | 56.01 | 39.99 | 21.7 | 7.42% | 7.96% | -0.09% | |||||
10/31/01 | 65.96 | 41.05 | 23.44 | 17.76% | 2.65% | 8.02% | |||||
11/30/01 | 69.92 | 39.68 | 22.14 | 6.00% | -3.34% | -5.55% | |||||
1/2/02 | 73.44 | 39.1 | 23.84 | 5.03% | -1.46% | 7.68% | |||||
1/31/02 | 66.61 | 38.46 | 22.83 | -9.30% | -1.64% | -4.24% | |||||
2/28/02 | 61 | 40.2 | 23.12 | -8.42% | 4.52% | 1.27% | |||||
4/2/02 | 68.46 | 38.32 | 20.52 | 12.23% | -4.68% | -11.25% | |||||
4/30/02 | 58.13 | 41.38 | 19.32 | -15.09% | 7.99% | -5.85% | |||||
5/31/02 | 55.7 | 41.14 | 19.87 | -4.18% | -0.58% | 2.85% | |||||
7/2/02 | 66.09 | 38 | 20.52 | 18.65% | -7.63% | 3.27% | |||||
7/31/02 | 65.99 | 38.34 | 22 | -0.15% | 0.89% | 7.21% | |||||
8/31/02 | 74.87 | 42.28 | 23.41 | 13.46% | 10.28% | 6.41% | |||||
10/1/02 | 78.85 | 42.25 | 24.58 | 5.32% | -0.07% | 5.00% | |||||
10/31/02 | 89.14 | 39.52 | 25.22 | 13.05% | -6.46% | 2.60% | |||||
11/30/02 | 82.51 | 39.73 | 25.65 | -7.44% | 0.53% | 1.70% | |||||
1/2/03 | 83.38 | 41.03 | 26.51 | 1.05% | 3.27% | 3.35% | |||||
1/31/03 | 82.29 | 41.57 | 24.28 | -1.31% | 1.32% | -8.41% | |||||
2/28/03 | 90.36 | 40.59 | 24.02 | 9.81% | -2.36% | -1.07% | |||||
4/1/03 | 97.06 | 41.43 | 25.81 | 7.41% | 2.07% | 7.45% | |||||
4/30/03 | 117.86 | 41.34 | 26.54 | 21.43% | -0.22% | 2.83% | |||||
5/31/03 | 118.69 | 41.79 | 25.49 | 0.70% | 1.09% | -3.96% | |||||
7/1/03 | 128.14 | 39.91 | 25.07 | 7.96% | -4.50% | -1.65% | |||||
7/31/03 | 134.68 | 38.09 | 24.93 | 5.10% | -4.56% | -0.56% | |||||
9/3/03 | 149.26 | 38.1 | 25.57 | 10.83% | 0.03% | 2.57% | |||||
9/30/03 | 184.74 | 39.47 | 31.95 | 23.77% | 3.60% | 24.95% | |||||
10/31/03 | 177.22 | 41.81 | 29.26 | -4.07% | 5.93% | -8.42% | |||||
12/2/03 | 192.64 | 41.68 | 31 | 8.70% | -0.31% | 5.95% | |||||
1/1/04 | 131.64 | 44.49 | 28.39 | -31.67% | 6.74% | -8.42% | |||||
1/31/04 | 121.59 | 43.48 | 23.78 | -7.63% | -2.27% | -16.24% | |||||
3/2/04 | 139.56 | 46.41 | 24.81 | 14.78% | 6.74% | 4.33% | |||||
3/31/04 | 169.18 | 51.08 | 24.93 | 21.22% | 10.06% | 0.48% | |||||
4/30/04 | 183.57 | 51.09 | 24.85 | 8.51% | 0.02% | -0.32% | |||||
6/1/04 | 162.84 | 49.72 | 24.13 | -11.29% | -2.68% | -2.90% | |||||
6/30/04 | 154.59 | 51.86 | 22.56 | -5.07% | 4.30% | -6.51% | |||||
7/31/04 | 164.88 | 52.47 | 24.04 | 6.66% | 1.18% | 6.56% | |||||
9/1/04 | 110.54 | 53.2 | 23.51 | -32.96% | 1.39% | -2.20% | |||||
9/30/04 | 104.64 | 49.58 | 19.67 | -5.34% | -6.80% | -16.33% | |||||
11/2/04 | 90.13 | 49.64 | 17.93 | -13.87% | 0.12% | -8.85% | |||||
11/30/04 | 83.01 | 50.02 | 17.24 | -7.90% | 0.77% | -3.85% | |||||
1/1/05 | 87.66 | 42.04 | 15.16 | 5.60% | -15.95% | -12.06% | |||||
2/1/05 | 86.86 | 43.93 | 14.42 | -0.91% | 4.50% | -4.88% | |||||
3/1/05 | 102.23 | 46.74 | 16.4 | 17.70% | 6.40% | 13.73% | |||||
3/31/05 | 122.38 | 45.22 | 18.09 | 19.71% | -3.25% | 10.30% | |||||
4/30/05 | 132.08 | 44.87 | 18.77 | 7.93% | -0.77% | 3.76% | |||||
5/31/05 | 138.52 | 43.7 | 21.36 | 4.88% | -2.61% | 13.80% | |||||
6/30/05 | 158.91 | 44.99 | 21.13 | 14.72% | 2.95% | -1.08% | |||||
8/2/05 | 163.59 | 46.14 | 22.27 | 2.95% | 2.56% | 5.40% | |||||
8/31/05 | 180.26 | 44.52 | 23.24 | 10.19% | -3.51% | 4.36% | |||||
9/30/05 | 183.33 | 45.06 | 25.06 | 1.70% | 1.21% | 7.83% | |||||
11/1/05 | 194.42 | 49.48 | 26.69 | 6.05% | 9.81% | 6.50% | |||||
11/30/05 | 204.95 | 48.72 | 27.66 | 5.42% | -1.54% | 3.63% | |||||
1/3/06 | 186.79 | 48.71 | 25.58 | -8.86% | -0.02% | -7.52% | |||||
1/31/06 | 199 | 49.29 | 26.14 | 6.54% | 1.19% | 2.19% | |||||
2/28/06 | 228.55 | 50.97 | 26.71 | 14.85% | 3.41% | 2.18% | |||||
3/31/06 | 253.92 | 49.17 | 27.85 | 11.10% | -3.53% | 4.27% | |||||
5/2/06 | 249.83 | 46.62 | 23.63 | -1.61% | -5.19% | -15.15% | |||||
5/31/06 | 244.63 | 44.32 | 21.08 | -2.08% | -4.93% | -10.79% | |||||
6/30/06 | 250.19 | 47.2 | 23.64 | 2.27% | 6.50% | 12.14% | |||||
8/1/06 | 236.43 | 46.5 | 21.61 | -5.50% | -1.48% | -8.59% | |||||
8/31/06 | 275.96 | 49.64 | 22.55 | 16.72% | 6.75% | 4.35% | |||||
9/30/06 | 292.72 | 50.24 | 24.56 | 6.07% | 1.21% | 8.91% | |||||
10/31/06 | 302.61 | 50.16 | 23.4 | 3.38% | -0.16% | -4.72% | |||||
11/30/06 | 313.71 | 50.29 | 25.86 | 3.67% | 0.26% | 10.51% | |||||
1/2/07 | 330.01 | 52.29 | 25.69 | 5.20% | 3.98% | -0.66% | |||||
1/31/07 | 343.52 | 48.47 | 24.77 | 4.09% | -7.31% | -3.58% | |||||
2/28/07 | 338.94 | 48.88 | 23.66 | -1.33% | 0.85% | -4.48% | |||||
3/31/07 | 340.52 | 51.63 | 24.16 | 0.47% | 5.63% | 2.11% | |||||
5/1/07 | 338.28 | 52.2 | 23.46 | -0.66% | 1.10% | -2.90% | |||||
5/31/07 | 326.46 | 50.23 | 24.39 | -3.49% | -3.77% | 3.96% | |||||
6/30/07 | 379.76 | 49.83 | 25.7 | 16.33% | -0.80% | 5.37% | |||||
7/31/07 | 374.27 | 50.64 | 25.11 | -1.45% | 1.63% | -2.30% | |||||
8/31/07 | 370.85 | 49.42 | 23.5 | -0.91% | -2.41% | -6.41% | |||||
10/2/07 | 393.67 | 54 | 25.14 | 6.15% | 9.27% | 6.98% | |||||
10/31/07 | 371.71 | 56.08 | 24.33 | -5.58% | 3.85% | -3.22% | |||||
11/30/07 | 393.88 | 57.25 | 24.69 | 5.96% | 2.09% | 1.48% | |||||
1/2/08 | 443.95 | 58.79 | 28.09 | 12.71% | 2.69% | 13.77% | |||||
1/31/08 | 527.55 | 56.6 | 30.39 | 18.83% | -3.73% | 8.19% | |||||
2/29/08 | 583.09 | 59.03 | 30.88 | 10.53% | 4.29% | 1.61% | |||||
4/1/08 | 567.95 | 56.82 | 30.65 | -2.60% | -3.74% | -0.74% | |||||
4/30/08 | 561.87 | 63.91 | 28.13 | -1.07% | 12.48% | -8.22% | |||||
5/31/08 | 567.97 | 67.7 | 29.48 | 1.09% | 5.93% | 4.80% | |||||
7/1/08 | 594 | 72.27 | 28.4 | 4.58% | 6.75% | -3.66% | |||||
7/31/08 | 649.76 | 70.88 | 29.9 | 9.39% | -1.92% | 5.28% | |||||
9/3/08 | 651.58 | 72.05 | 28.87 | 0.28% | 1.65% | -3.44% | |||||
9/30/08 | 581.47 | 73.24 | 27.68 | -10.76% | 1.65% | -4.12% | |||||
10/31/08 | 574.27 | 70.31 | 26.03 | -1.24% | -4.00% | -5.96% | |||||
12/2/08 | 522.16 | 66.98 | 26.12 | -9.07% | -4.74% | 0.35% | |||||
1/1/09 | 446.92 | 68.67 | 26.85 | -14.41% | 2.52% | 2.79% | |||||
1/31/09 | 435.62 | 69.49 | 27.41 | -2.53% | 1.19% | 2.09% | |||||
2/28/09 | 436.86 | 73.93 | 28.21 | 0.28% | 6.39% | 2.92% | |||||
3/31/09 | 436.98 | 76.79 | 32.64 | 0.03% | 3.87% | 15.70% | |||||
4/30/09 | 446.78 | 74.39 | 34.66 | 2.24% | -3.13% | 6.19% | |||||
6/2/09 | 393.93 | 74.04 | 34.3 | -11.83% | -0.47% | -1.04% | |||||
6/30/09 | 449.56 | 77.47 | 31.62 | 14.12% | 4.63% | -7.81% | |||||
7/31/09 | 487.22 | 72.98 | 33.4 | 8.38% | -5.80% | 5.63% | |||||
9/2/09 | 476.75 | 73.96 | 33.28 | -2.15% | 1.34% | -0.36% | |||||
9/30/09 | 525.96 | 76.08 | 35.73 | 10.32% | 2.87% | 7.36% |
MVP
Table of expected returns and risk | |||||
E(return) | Standard Deviation | ||||
Asset A | 3.94% | 10.74% | |||
Asset B | 0.56% | 4.56% | |||
Asset C | 0.70% | 6.80% | |||
Table of covariances | |||||
Asset A | Asset B | Asset C | |||
Asset A | 1.15% | -0.02% | 0.30% | ||
Asset B | -0.02% | 0.21% | 0.05% | ||
Asset C | 0.30% | 0.05% | 0.46% | ||
Proporition of wealth invested in | |||||
Asset A | 0.09 | Wa | |||
Asset B | 0.70 | Wb | |||
Asset C | 0.21 | Wc | |||
Total | 1.00 | (this is a CONSTRAINT for “Solver”, this cell B23 must be forced to equal one) | |||
(it is the constraint that the total proportions of our wealth must equal one) | |||||
This bit is used to find the risk of the portfolio | |||||
Asset A | Asset B | Asset C | |||
Proportion | 8.94% | 70.40% | 20.66% | These numbers are a table of, for instance | |
Asset A | 8.94% | 0.0092% | -0.0010% | 0.0056% | proportion of A * proportion of B * |
Asset B | 70.40% | -0.0010% | 0.1030% | 0.0066% | covariance of A and B |
Asset C | 20.66% | 0.0056% | 0.0066% | 0.0197% | |
Expected return on the portfolio of all 3 assets | |||||
0.89% | (if you are minimizing risk for a given return you should tell “Solver” that | ||||
this cell, B35, is fixed–at whatever level of return you want. If you are maximizing | |||||
return for a fixed risk, you should tell “Solver” to maximize this cell | |||||
Risk associated with the portfolio of all 3 assets | |||||
3.93% | (if you are minimizing risk for a given return you should tell “Solver” to minimize | ||||
this cell, B40. If you are maximizing return for a fixed risk you should | |||||
tell “Solver” that this cell is fixed.) | |||||
Now use “solver” to either minimize the risk for a given return | |||||
maximize return for a given risk (ie, maximize B30 for a given | |||||
value of B35). | |||||
In both cases B18 should be constrained to equal 1, this is the | |||||
condition that our “total wealth” is invested in the assets. |
EF
Return | Risk | Wa | Wb | Wc | Total weight | Efficient frontier for risky portfolio | |||||
Table of expected returns and risk | 0.9285% | 3.9301% | 10.1323% | 70.1055% | 19.7622% | 100.0000% | |||||
E(return) | Standard Deviation | 1.1368% | 4.0001% | 16.4903% | 68.5472% | 14.9625% | 100.0000% | ||||
Asset A | 3.94% | 10.74% | 1.3766% | 4.2000% | 23.8082% | 66.7536% | 9.4382% | 100.0000% | |||
Asset B | 0.56% | 4.56% | 1.5392% | 4.4001% | 28.7689% | 65.5378% | 5.6933% | 100.0000% | |||
Asset C | 0.70% | 6.80% | 1.6740% | 4.6001% | 32.8829% | 64.5294% | 2.5876% | 100.0000% | |||
1.7935% | 4.8000% | 36.5238% | 63.4762% | 0.0000% | 100.0000% | ||||||
Table of covariances | 1.9013% | 4.9999% | 39.7109% | 60.2891% | 0.0000% | 100.0000% | |||||
Asset A | Asset B | Asset C | 1.9998% | 5.2000% | 42.6267% | 57.3733% | 0.0000% | 100.0000% | |||
Asset A | 1.15% | -0.02% | 0.30% | 2.0919% | 5.4000% | 45.3509% | 54.6491% | 0.0000% | 100.0000% | ||
Asset B | -0.02% | 0.21% | 0.05% | 2.1792% | 5.6000% | 47.9326% | 52.0674% | 0.0000% | 100.0000% | ||
Asset C | 0.30% | 0.05% | 0.46% | 2.2627% | 5.8002% | 50.4071% | 49.5929% | 0.0000% | 100.0000% | ||
2.3433% | 6.0000% | 52.7876% | 47.2124% | 0.0000% | 100.0000% | ||||||
Proporition of wealth invested in | 2.4215% | 6.2001% | 55.1003% | 44.8997% | 0.0000% | 100.0000% | |||||
Asset A | 1.00 | Wa | 2.4976% | 6.4001% | 57.3519% | 42.6481% | 0.0000% | 100.0000% | |||
Asset B | 0.00 | Wb | 2.5720% | 6.6003% | 59.5561% | 40.4439% | 0.0000% | 100.0000% | |||
Asset C | 0.00 | Wc | 2.6449% | 6.8000% | 61.7112% | 38.2888% | 0.0000% | 100.0000% | |||
Total | 1.00 | (this is a CONSTRAINT for “Solver”, this cell B23 must be forced to equal one) | 2.7166% | 7.0000% | 63.8319% | 36.1681% | 0.0000% | 100.0000% | |||
(it is the constraint that the total proportions of our wealth must equal one) | 2.8221% | 7.3000% | 66.9534% | 33.0466% | 0.0000% | 100.0000% | |||||
This bit is used to find the risk of the portfolio | 2.9256% | 7.6003% | 70.0169% | 29.9831% | 0.0000% | 100.0000% | |||||
Asset A | Asset B | Asset C | 3.0273% | 7.9000% | 73.0236% | 26.9764% | 0.0000% | 100.0000% | |||
Proportion | 100.00% | 0.00% | 0.00% | These numbers are a table of, for instance | 3.1276% | 8.2000% | 75.9900% | 24.0100% | 0.0000% | 100.0000% | |
Asset A | 100.00% | 1.1535% | 0.0000% | 0.0000% | proportion of A * proportion of B * | 3.2266% | 8.5000% | 78.9193% | 21.0807% | 0.0000% | 100.0000% |
Asset B | 0.00% | 0.0000% | 0.0000% | 0.0000% | covariance of A and B | 3.3246% | 8.8000% | 81.8164% | 18.1836% | 0.0000% | 100.0000% |
Asset C | 0.00% | 0.0000% | 0.0000% | 0.0000% | 3.4215% | 9.1000% | 84.6853% | 15.3147% | 0.0000% | 100.0000% | |
3.5177% | 9.4000% | 87.5296% | 12.4704% | 0.0000% | 100.0000% | ||||||
3.6131% | 9.7000% | 90.3520% | 9.6480% | 0.0000% | 100.0000% | ||||||
3.7078% | 10.0000% | 93.1549% | 6.8451% | 0.0000% | 100.0000% | ||||||
Expected return on the portfolio of all 3 assets | 3.8020% | 10.3000% | 95.9403% | 4.0597% | 0.0000% | 100.0000% | |||||
3.94% | (if you are minimizing risk for a given return you should tell “Solver” that | 3.8956% | 10.6000% | 98.7101% | 1.2899% | 0.0000% | 100.0000% | ||||
this cell, B35, is fixed–at whatever level of return you want. If you are maximizing | 3.9392% | 10.7402% | 100.0000% | 0.0000% | 0.0000% | 100.0000% | |||||
return for a fixed risk, you should tell “Solver” to maximize this cell | |||||||||||
Risk associated with the portfolio of all 3 assets | |||||||||||
10.74% | (if you are minimizing risk for a given return you should tell “Solver” to minimize | ||||||||||
this cell, B40. If you are maximizing return for a fixed risk you should | |||||||||||
tell “Solver” that this cell is fixed.) | |||||||||||
Now use “solver” to either minimize the risk for a given return | |||||||||||
maximize return for a given risk (ie, maximize B30 for a given | |||||||||||
value of B35). | |||||||||||
In both cases B18 should be constrained to equal 1, this is the | |||||||||||
condition that our “total wealth” is invested in the assets. |
&A
Page &P
Solve List
MPR
Table of expected returns and risk on the risky assets | |||||
E(return) | Risk | ||||
Asset A | 3.94% | 10.74% | |||
Asset B | 0.56% | 4.56% | |||
Asset C | 0.70% | 6.80% | |||
Riskfree rate | 0.33% | The return on the risk free asset | |||
Table of covariances for the risky assets | |||||
Asset A | Asset B | Asset C | Construct covariance matrix using formulae | ||
Asset A | 1.15% | -0.02% | 0.30% | such as covar(A,B)=risk(A)*risk(B)*correl(A,B) | |
Asset B | -0.02% | 0.21% | 0.05% | ||
Asset C | 0.30% | 0.05% | 0.46% | ||
Proporition of wealth invested in purely risky assets | |||||
Asset A | 0.7041 | Wa | Normally these values are set by “Solver” but it may be necessary to | ||
Asset B | 0.2959 | Wb | reset them to “sensible” values and then re-run “Solver” if “Solver” | ||
Asset C | 0.0000 | Wc | fails to find a solution (eg, B23=1/3, B24=1/3, B25=1/3 ) | ||
Total | 1.0000 | (this is a CONSTRAINT for “Solver”, this cell B26 must be forced to equal one) | |||
(it is the constraint that the sum of the proportions of our wealth must equal one) | |||||
This bit is used to find the risk of the portfolio of purely risky assets | |||||
Asset A | Asset B | Asset C | |||
Proportion | 70.41% | 29.59% | 0.00% | These numbers are a table of, for instance | |
Asset A | 70.41% | 0.5719% | -0.0032% | 0.0000% | proportion of A * proportion of B * |
Asset B | 29.59% | -0.0032% | 0.0182% | 0.0000% | covariance of A and B |
Asset C | 0.00% | 0.0000% | 0.0000% | 0.0000% | |
Expected return on the portfolio of all 3 risky assets | |||||
2.94% | |||||
Risk associated with the portfolio of all 3 risky assets | |||||
7.64% | |||||
Market Price of Risk ( = Slope of the Capital Market Line) | |||||
0.3410881206 | |||||
To find the Market Price of Risk (and hence the Capital Market Line) | |||||
use “solver” to maximize cell B39 subject to the constraint that | |||||
cell B21 is equal to one. | |||||
You access “solver” from the “Tools” menu. |
Results
Efficient frontier for risky portfolio | Capital market line | market price of risk (Slope of CML) | 0.3410881206 | ||
risk | return | risk | return | risk free rate | 0.33% |
3.93% | 0.93% | 0.00% | 0.33% | ||
4.00% | 1.14% | 1.00% | 0.67% | ||
4.20% | 1.38% | 2.00% | 1.02% | ||
4.40% | 1.54% | 3.00% | 1.36% | ||
4.60% | 1.67% | 4.00% | 1.70% | ||
4.80% | 1.79% | 5.00% | 2.04% | ||
5.00% | 1.90% | 6.00% | 2.38% | ||
5.20% | 2.00% | 7.00% | 2.72% | ||
5.40% | 2.09% | 8.00% | 3.06% | ||
5.60% | 2.18% | 9.00% | 3.40% | ||
5.80% | 2.26% | 10.00% | 3.74% | ||
6.00% | 2.34% | 11.00% | 4.09% | ||
6.20% | 2.42% | 12.00% | 4.43% | ||
6.40% | 2.50% | 13.00% | 4.77% | ||
6.60% | 2.57% | 14.00% | 5.11% | ||
6.80% | 2.64% | 15.00% | 5.45% | ||
7.00% | 2.72% | 16.00% | 5.79% | ||
7.30% | 2.82% | 17.00% | 6.13% | ||
7.60% | 2.93% | 18.00% | 6.47% | ||
7.90% | 3.03% | 19.00% | 6.81% | ||
8.20% | 3.13% | 20.00% | 7.16% | ||
8.50% | 3.23% | 21.00% | 7.50% | ||
8.80% | 3.32% | 22.00% | 7.84% | ||
9.10% | 3.42% | 23.00% | 8.18% | ||
9.40% | 3.52% | 24.00% | 8.52% | ||
9.70% | 3.61% | 25.00% | 8.86% | ||
10.00% | 3.71% | 26.00% | 9.20% | ||
10.30% | 3.80% | 27.00% | 9.54% | ||
10.60% | 3.90% | 28.00% | 9.88% | ||
10.74% | 3.94% | 29.00% | 10.22% |
Graph
efficient frontier 0.0393009031542144 0.0400009719507754 0.0420004092763677 0.0440008854773404 0.0460009361463829 0.0480000297433731 0.0499993298720953 0.051999671072642 0.0539998241891924 0.0559998996183066 0.0580023802328983 0.0599999622335149 0.0620008538762859 0.0640006608589746 0.0660032344379133 0.0680004118389888 0.0700003303661607 0.0730003746835499 0.0760032079409236 0.07900021566683 0.0820001665043373 0.0850001298625881 0.0880001022205146 0.0910000811344401 0.0940000648868538 0.097000052249355 0.10000004233583 0.103000034497334 0.106000014275721 0.1074024953028 0.00928544596087443 0.0113680222097479 0.0137661542428896 0.0153918282917927 0.0167400503810674 0.0179353913029367 0.0190127159556099 0.0199983418730154 0.020919177157052 0.021791877470304 0.0226273120610823 0.0234330086453775 0.0242147539556025 0.0249758670000241 0.0257199331999683 0.0264494137605783 0.027166282333763 0.0282214212514793 0.0292559807621554 0.0302733265478869 0.0312760498302146 0.0322662192448926 0.0332455114236848 0.0342153031530757 0.0351767379661397 0.0361307752494081 0.0370782271142095 0.0380197865164753 0.0389560446573528 0.0393920732724868 assets 0.1074024953028 0.0455888894097068 0.0679547841547936 0.0393920732724868 0.00558935368498762 0.00695608162021198risk
return
efficient frontier 0.0393009031542144 0.0400009719507754 0.0420004092763677 0.0440008854773404 0.0460009361463829 0.0480000297433731 0.0499993298720953 0.051999671072642 0.0539998241891924 0.0559998996183066 0.0580023802328983 0.0599999622335149 0.0620008538762859 0.0640006608589746 0.0660032344379133 0.0680004118389888 0.0700003303661607 0.0730003746835499 0.0760032079409236 0.07900021566683 0.0820001665043373 0.0850001298625881 0.0880001022205146 0.0910000811344401 0.0940000648868538 0.097000052249355 0.10000004233583 0.103000034497334 0.106000014275721 0.1074024953028 0.00928544596087443 0.0113680222097479 0.0137661542428896 0.0153918282917927 0.0167400503810674 0.0179353913029367 0.0190127159556099 0.0199983418730154 0.020919177157052 0.021791877470304 0.0226273120610823 0.0234330086453775 0.0242147539556025 0.0249758670000241 0.0257199331999683 0.0264494137605783 0.027166282333763 0.0282214212514793 0.0292559807621554 0.0302733265478869 0.0312760498302146 0.0322662192448926 0.0332455114236848 0.0342153031530757 0.0351767379661397 0.0361307752494081 0.0370782271142095 0.0380197865164753 0.0389560446573528 0.0393920732724868 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00674421453892556 0.0101550957445178 0.01356597695011 0.0169768581557022 0.0203877393612945 0.0237986205668867 0.0272095017724789 0.0306203829780712 0.0340312641836634 0.0374421453892556 0.0408530265948479 0.0442639078004401 0.0476747890060323 0.0510856702116245 0.0544965514172168 0.057907432622809 0.0613183138284012 0.0647291950339934 0.0681400762395857 0.0715509574451779 0.0749618386507701 0.0783727198563624 0.0817836010619546 0.0851944822675468 0.088605363473139 0.0920162446787313 0.0954271258843235 0.0988380070899157 0.102248888295508 assets 0.1074024953028 0.0455888894097068 0.0679547841547936 0.0393920732724868 0.00558935368498762 0.00695608162021198
risk
return
efficient frontier 0.0393009031542144 0.0400009719507754 0.0420004092763677 0.0440008854773404 0.0460009361463829 0.0480000297433731 0.0499993298720953 0.051999671072642 0.0539998241891924 0.0559998996183066 0.0580023802328983 0.0599999622335149 0.0620008538762859 0.0640006608589746 0.0660032344379133 0.0680004118389888 0.0700003303661607 0.0730003746835499 0.0760032079409236 0.07900021566683 0.0820001665043373 0.0850001298625881 0.0880001022205146 0.0910000811344401 0.0940000648868538 0.097000052249355 0.10000004233583 0.103000034497334 0.106000014275721 0.1074024953028 0.00928544596087443 0.0113680222097479 0.0137661542428896 0.0153918282917927 0.0167400503810674 0.0179353913029367 0.0190127159556099 0.0199983418730154 0.020919177157052 0.021791877470304 0.0226273120610823 0.0234330086453775 0.0242147539556025 0.0249758670000241 0.0257199331999683 0.0264494137605783 0.027166282333763 0.0282214212514793 0.0292559807621554 0.0302733265478869 0.0312760498302146 0.0322662192448926 0.0332455114236848 0.0342153031530757 0.0351767379661397 0.0361307752494081 0.0370782271142095 0.0380197865164753 0.0389560446573528 0.0393920732724868 assets 0.1074024953028 0.0455888894097068 0.0679547841547936 0.0393920732724868 0.00558935368498762 0.00695608162021198
risk
return
efficient frontier 0.0393009031542144 0.0400009719507754 0.0420004092763677 0.0440008854773404 0.0460009361463829 0.0480000297433731 0.0499993298720953 0.051999671072642 0.0539998241891924 0.0559998996183066 0.0580023802328983 0.0599999622335149 0.0620008538762859 0.0640006608589746 0.0660032344379133 0.0680004118389888 0.0700003303661607 0.0730003746835499 0.0760032079409236 0.07900021566683 0.0820001665043373 0.0850001298625881 0.0880001022205146 0.0910000811344401 0.0940000648868538 0.097000052249355 0.10000004233583 0.103000034497334 0.106000014275721 0.1074024953028 0.00928544596087443 0.0113680222097479 0.0137661542428896 0.0153918282917927 0.0167400503810674 0.0179353913029367 0.0190127159556099 0.0199983418730154 0.020919177157052 0.021791877470304 0.0226273120610823 0.0234330086453775 0.0242147539556025 0.0249758670000241 0.0257199331999683 0.0264494137605783 0.027166282333763 0.0282214212514793 0.0292559807621554 0.0302733265478869 0.0312760498302146 0.0322662192448926 0.0332455114236848 0.0342153031530757 0.0351767379661397 0.0361307752494081 0.0370782271142095 0.0380197865164753 0.0389560446573528 0.0393920732724868 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00674421453892556 0.0101550957445178 0.01356597695011 0.0169768581557022 0.0203877393612945 0.0237986205668867 0.0272095017724789 0.0306203829780712 0.0340312641836634 0.0374421453892556 0.0408530265948479 0.0442639078004401 0.0476747890060323 0.0510856702116245 0.0544965514172168 0.057907432622809 0.0613183138284012 0.0647291950339934 0.0681400762395857 0.0715509574451779 0.0749618386507701 0.0783727198563624 0.0817836010619546 0.0851944822675468 0.088605363473139 0.0920162446787313 0.0954271258843235 0.0988380070899157 0.102248888295508 assets 0.1074024953028 0.0455888894097068 0.0679547841547936 0.0393920732724868 0.00558935368498762 0.00695608162021198
risk
return