Efficient portfolios and Efficient frontier
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. |
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
risk
return
efficient frontier 3.9300903154214445E-2 4.0000971950775441E-2 4.2000409276367681E-2 4.4000885477340441E-2 4.6000936146382926E-2 4.800002974337314E-2 4.9999329872095273E-2 5.1999671072641973E-2 5.3999824189192372E-2 5.599989961830662E-2 5.8002380232898255E-2 5.999996223351492E-2 6. 2000853876285907E-2 6.4000660858974587E-2 6.6003234437913269E-2 6.8000411838988833E-2 7.000033036616074E-2 7.3000374683549904E-2 7.6003207940923634E-2 7.9000215666830034E-2 8.2000166504337252E-2 8.5000129862588078E-2 8.8000102220514576E-2 9.100008113444015E-2 9.4000064886853751E-2 9.700005224935504E-2 0.10000004233583047 0.10300003449733379 0.10600001427572135 0.10740249530280017 9.2854459608744284E-3 1.1368022209747919E-2 1.3766154242889555E-2 1.539182829179271E-2 1.6740050381067396E-2 1.7935391302936667E-2 1.9012715955609882E-2 1.9998341873015434E-2 2.0919177157052014E-2 2.1791877470303977E-2 2.2627312061082348E-2 2.3433008645377486E-2 2.4214753955602495E-2 2.4975867000024111E-2 2.571993319996831E-2 2.6449413760578279E-2 2.716628233376301E-2 2.8221421251479341E-2 2.9255980762155409E-2 3.027332654788693E-2 3.1276049830214585E-2 3.2266219244892563E-2 3.3245511423684829E-2 3.4215303153075714E-2 3.5176737966139709E-2 3.6130775249408137E-2 3.707822711420948E-2 3.8019786516475279E-2 3.8956044657352841E-2 3.9392073272486811E-2 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 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.28000000000000003 0.28999999999999998 3.3333333333333335E-3 6.7442145389255623E-3 1.0155095744517791E-2 1.3565976950110021E-2 1.6976858155702247E-2 2.0387739361294479E-2 2.3798620566886707E-2 2.7209501772478939E-2 3.0620382978071164E-2 3.4031264183663389E-2 3.7442145389255624E-2 4.0853026594847852E-2 4.426390780044008E-2 4.7674789006032309E-2 5.1085670211624544E-2 5.4496551417216765E-2 5.7907432622808994E-2 6.1318313828401229E-2 6.472919503399345E-2 6.8140076239585678E-2 7.1550957445177907E-2 7.4961838650770135E-2 7.8372719856362363E-2 8.1783601061954592E-2 8.519448226754682E-2 8.8605363473139048E-2 9.2016244678731277E-2 9.5427125884323519E-2 9.8838 007089915747E-2 0.10224888829550796 assets 0.10740249530280019 4.5588889409706755E-2 6.7954784154793599E-2 3.9392073272486818E-2 5.5893536849876187E-3 6.9560816202119766E-3
risk
return