Excel Project (Efficient portfolios and Efficient frontier)
zhenbaozhuData
Date | APPLE | WMT | MSFT | Apple | WMT | MSFT | Asset A | Asset B | Asset C | |||||
12/31/05 | 5.869116 | 40.936508 | 22.005415 | Apple | WMT | MSFT | ||||||||
1/31/06 | 6.252933 | 41.426868 | 22.388046 | 6.54% | 1.20% | 1.74% | ||||||||
2/28/06 | 7.181309 | 42.599094 | 22.979153 | 14.85% | 2.83% | 2.64% | ||||||||
3/31/06 | 7.978589 | 41.329037 | 23.959829 | 11.10% | -2.98% | 4.27% | Table of Covariance | |||||||
4/30/06 | 7.849935 | 38.955948 | 20.241114 | -1.61% | -5.74% | -15.52% | Asset A | Asset B | Asset C | Things in dark grey are input parameters | ||||
5/31/06 | 7.686444 | 37.252583 | 18.133703 | -2.08% | -4.37% | -10.41% | Asset A | 0.61% | 0.08% | 0.22% | Things in light grey are computed intermediate things | |||
6/30/06 | 7.861242 | 39.670475 | 20.340328 | 2.27% | 6.49% | 12.17% | Asset B | 0.08% | 0.23% | 0.06% | Things in yellow are labels | |||
7/31/06 | 7.428838 | 38.856762 | 18.49622 | -5.50% | -2.05% | -9.07% | Asset C | 0.22% | 0.06% | 0.38% | Things in red are "Solver" inputs or outputs | |||
8/31/06 | 8.67105 | 41.718204 | 19.403025 | 16.72% | 7.36% | 4.90% | ||||||||
9/30/06 | 9.197577 | 42.224873 | 21.130194 | 6.07% | 1.21% | 8.90% | Table of expected returns and risk | |||||||
10/31/06 | 9.508361 | 42.162521 | 20.013079 | 3.38% | -0.15% | -5.29% | E(return) | Standard Deviation | ||||||
11/30/06 | 9.857034 | 42.037804 | 22.248495 | 3.67% | -0.30% | 11.17% | Asset A | 2.62% | 7.81% | |||||
12/31/06 | 10.369199 | 43.947617 | 22.105003 | 5.20% | 4.54% | -0.64% | Asset B | 1.09% | 4.81% | |||||
1/31/07 | 10.79366 | 40.741882 | 21.188278 | 4.09% | -7.29% | -4.15% | Asset C | 1.97% | 6.16% | |||||
2/28/07 | 10.650034 | 40.796741 | 20.359304 | -1.33% | 0.13% | -3.91% | ||||||||
3/31/07 | 10.699543 | 43.395325 | 20.78429 | 0.46% | 6.37% | 2.09% | ||||||||
4/30/07 | 10.629257 | 43.584763 | 20.054588 | -0.66% | 0.44% | -3.51% | 0.61% | |||||||
5/31/07 | 10.25766 | 42.220543 | 20.985094 | -3.50% | -3.13% | 4.64% | ||||||||
6/30/07 | 11.932584 | 41.878899 | 22.115059 | 16.33% | -0.81% | 5.38% | ||||||||
7/31/07 | 11.75993 | 42.260265 | 21.469366 | -1.45% | 0.91% | -2.92% | ||||||||
8/31/07 | 11.652668 | 41.533646 | 20.215986 | -0.91% | -1.72% | -5.84% | ||||||||
9/30/07 | 12.369576 | 45.390919 | 21.629238 | 6.15% | 9.29% | 6.99% | ||||||||
10/31/07 | 11.679561 | 47.135494 | 20.776413 | -5.58% | 3.84% | -3.94% | ||||||||
11/30/07 | 12.376299 | 47.823711 | 21.243826 | 5.97% | 1.46% | 2.25% | ||||||||
12/31/07 | 13.949464 | 49.410961 | 24.16526 | 12.71% | 3.32% | 13.75% | ||||||||
1/31/08 | 16.576292 | 47.574959 | 25.973772 | 18.83% | -3.72% | 7.48% | ||||||||
2/29/08 | 18.321507 | 49.282116 | 26.573099 | 10.53% | 3.59% | 2.31% | ||||||||
3/31/08 | 17.845701 | 47.755589 | 26.375406 | -2.60% | -3.10% | -0.74% | ||||||||
4/30/08 | 17.654713 | 53.357197 | 24.044287 | -1.07% | 11.73% | -8.84% | ||||||||
5/31/08 | 17.846313 | 56.902275 | 25.362825 | 1.09% | 6.64% | 5.48% | ||||||||
6/30/08 | 18.664078 | 60.746368 | 24.43421 | 4.58% | 6.76% | -3.66% | ||||||||
7/31/08 | 20.328909 | 59.252789 | 25.553526 | 8.92% | -2.46% | 4.58% | ||||||||
8/31/08 | 20.473276 | 60.557922 | 24.83812 | 0.71% | 2.20% | -2.80% | ||||||||
9/30/08 | 18.270342 | 61.559025 | 23.819889 | -10.76% | 1.65% | -4.10% | ||||||||
10/31/08 | 17.962212 | 59.097317 | 22.217438 | -1.69% | -4.00% | -6.73% | ||||||||
11/30/08 | 16.40687 | 55.987366 | 22.475725 | -8.66% | -5.26% | 1.16% | ||||||||
12/31/08 | 14.042816 | 57.717258 | 23.098423 | -14.41% | 3.09% | 2.77% | ||||||||
1/31/09 | 13.608422 | 58.402107 | 23.392935 | -3.09% | 1.19% | 1.28% | ||||||||
2/28/09 | 13.726799 | 61.743847 | 24.273848 | 0.87% | 5.72% | 3.77% | ||||||||
3/31/09 | 13.73052 | 64.542175 | 28.083342 | 0.03% | 4.53% | 15.69% | ||||||||
4/30/09 | 13.94604 | 62.15049 | 29.610525 | 1.57% | -3.71% | 5.44% | ||||||||
5/31/09 | 12.377708 | 62.230877 | 29.510588 | -11.25% | 0.13% | -0.34% | ||||||||
6/30/09 | 14.125751 | 65.113098 | 27.203735 | 14.12% | 4.63% | -7.82% | ||||||||
7/31/09 | 15.208604 | 60.969387 | 28.536581 | 7.67% | -6.36% | 4.90% | ||||||||
8/31/09 | 14.980044 | 62.163292 | 28.634418 | -1.50% | 1.96% | 0.34% | ||||||||
9/30/09 | 16.423851 | 64.508293 | 30.467091 | 9.64% | 3.77% | 6.40% | ||||||||
10/31/09 | 17.472368 | 68.088806 | 32.807419 | 6.38% | 5.55% | 7.68% | ||||||||
11/30/09 | 17.730829 | 66.138863 | 32.432014 | 1.48% | -2.86% | -1.14% | ||||||||
12/31/09 | 15.821273 | 63.133839 | 32.804802 | -10.77% | -4.54% | 1.15% | ||||||||
1/31/10 | 16.631613 | 63.150757 | 33.212269 | 5.12% | 0.03% | 1.24% | ||||||||
2/28/10 | 17.065008 | 64.613281 | 35.802132 | 2.61% | 2.32% | 7.80% | ||||||||
3/31/10 | 18.761208 | 67.8209 | 35.286789 | 9.94% | 4.96% | -1.44% | ||||||||
4/30/10 | 20.125475 | 65.319435 | 35.758446 | 7.27% | -3.69% | 1.34% | ||||||||
5/31/10 | 20.797703 | 64.268425 | 36.679207 | 3.34% | -1.61% | 2.57% | ||||||||
6/30/10 | 21.395247 | 62.992832 | 37.963425 | 2.87% | -1.98% | 3.50% | ||||||||
7/31/10 | 22.939465 | 64.636559 | 39.960102 | 7.22% | 2.61% | 5.26% | ||||||||
8/31/10 | 22.659969 | 65.898293 | 41.03281 | -1.22% | 1.95% | 2.68% | ||||||||
9/30/10 | 24.29059 | 65.725937 | 41.555016 | 7.20% | -0.26% | 1.27% | ||||||||
10/31/10 | 26.748892 | 75.437912 | 42.316208 | 10.12% | 14.78% | 1.83% | ||||||||
11/30/10 | 24.933533 | 74.007401 | 41.371773 | -6.79% | -1.90% | -2.23% | ||||||||
12/31/10 | 26.465057 | 73.640938 | 35.983208 | 6.14% | -0.50% | -13.02% | ||||||||
1/31/11 | 29.017591 | 72.731041 | 39.05603 | 9.64% | -1.24% | 8.54% | ||||||||
2/28/11 | 28.218189 | 71.275192 | 36.472504 | -2.75% | -2.00% | -6.61% | ||||||||
3/31/11 | 28.381468 | 68.041862 | 43.630669 | 0.58% | -4.54% | 19.63% | ||||||||
4/30/11 | 29.544855 | 64.746552 | 42.033985 | 4.10% | -4.84% | -3.66% | ||||||||
5/31/11 | 28.563786 | 62.225079 | 39.860466 | -3.32% | -3.89% | -5.17% | ||||||||
6/30/11 | 27.623274 | 63.146225 | 42.162708 | -3.29% | 1.48% | 5.78% | ||||||||
7/31/11 | 25.678486 | 56.78598 | 39.291664 | -7.04% | -10.07% | -6.81% | ||||||||
8/31/11 | 25.231974 | 57.270893 | 40.223278 | -1.74% | 0.85% | 2.37% | ||||||||
9/30/11 | 27.336546 | 50.558086 | 47.838978 | 8.34% | -11.72% | 18.93% | ||||||||
10/31/11 | 27.062035 | 51.971306 | 49.393021 | -1.00% | 2.80% | 3.25% | ||||||||
11/30/11 | 24.182106 | 54.144138 | 50.759815 | -10.64% | 4.18% | 2.77% | ||||||||
12/31/11 | 22.362589 | 59.104412 | 50.402992 | -7.52% | 9.16% | -0.70% | ||||||||
1/31/12 | 22.213264 | 59.086597 | 46.551182 | -0.67% | -0.03% | -7.64% | ||||||||
2/29/12 | 25.174894 | 61.001522 | 50.893883 | 13.33% | 3.24% | 9.33% | ||||||||
3/31/12 | 21.652395 | 59.999561 | 45.954697 | -13.99% | -1.64% | -9.70% | ||||||||
4/30/12 | 23.066017 | 63.507816 | 48.83897 | 6.53% | 5.85% | 6.28% | ||||||||
5/31/12 | 22.216469 | 65.997383 | 47.482441 | -3.68% | 3.92% | -2.78% | ||||||||
6/30/12 | 24.217342 | 65.952194 | 52.595364 | 9.01% | -0.07% | 10.77% | ||||||||
7/31/12 | 24.656557 | 64.569351 | 53.319153 | 1.81% | -2.10% | 1.38% | ||||||||
8/31/12 | 26.413992 | 65.63018 | 53.7822 | 7.13% | 1.64% | 0.87% | ||||||||
9/30/12 | 26.528477 | 63.719135 | 55.948418 | 0.43% | -2.91% | 4.03% | ||||||||
10/31/12 | 25.822859 | 64.092247 | 56.265884 | -2.66% | 0.59% | 0.57% | ||||||||
11/30/12 | 27.200134 | 62.900127 | 58.413239 | 5.33% | -1.86% | 3.82% | ||||||||
12/31/12 | 28.498844 | 61.168968 | 60.772713 | 4.77% | -2.75% | 4.04% | ||||||||
1/31/13 | 32.171871 | 65.009232 | 60.142895 | 12.89% | 6.28% | -1.04% | ||||||||
2/28/13 | 33.884594 | 66.063232 | 62.285477 | 5.32% | 1.62% | 3.56% | ||||||||
3/31/13 | 33.882225 | 69.411102 | 64.744362 | -0.01% | 5.07% | 3.95% | ||||||||
4/30/13 | 36.030972 | 72.56868 | 66.049454 | 6.34% | 4.55% | 2.02% | ||||||||
5/31/13 | 34.109711 | 70.34034 | 65.5625 | -5.33% | -3.07% | -0.74% | ||||||||
6/30/13 | 35.225227 | 74.346237 | 69.148331 | 3.27% | 5.70% | 5.47% | ||||||||
7/31/13 | 38.841778 | 72.561714 | 71.117195 | 10.27% | -2.40% | 2.85% | ||||||||
8/31/13 | 36.645142 | 73.083603 | 71.228363 | -5.66% | 0.72% | 0.16% | ||||||||
9/30/13 | 40.192673 | 81.66021 | 79.537865 | 9.68% | 11.74% | 11.67% | ||||||||
10/31/13 | 40.860806 | 90.938293 | 80.484512 | 1.66% | 11.36% | 1.19% | ||||||||
11/30/13 | 40.382496 | 92.359932 | 82.205322 | -1.17% | 1.56% | 2.14% | ||||||||
12/31/13 | 39.952976 | 100.752655 | 91.306129 | -1.06% | 9.09% | 11.07% | ||||||||
1/31/14 | 42.503876 | 85.07267 | 90.114471 | 6.38% | -15.56% | -1.31% | ||||||||
2/28/14 | 40.199734 | 84.089714 | 88.123955 | -5.42% | -1.16% | -2.21% | ||||||||
3/31/14 | 39.595936 | 84.10614 | 90.296387 | -1.50% | 0.02% | 2.47% | ||||||||
4/30/14 | 44.773647 | 78.477531 | 95.433014 | 13.08% | -6.69% | 5.69% | ||||||||
5/31/14 | 44.52298 | 81.947502 | 95.623627 | -0.56% | 4.42% | 0.20% | ||||||||
6/30/14 | 45.768883 | 85.372734 | 102.867409 | 2.80% | 4.18% | 7.58% | ||||||||
7/31/14 | 54.749969 | 91.716125 | 108.928123 | 19.62% | 7.43% | 5.89% | ||||||||
8/31/14 | 54.485802 | 90.372299 | 111.33313 | -0.48% | -1.47% | 2.21% | ||||||||
9/30/14 | 52.825207 | 96.502312 | 103.973877 | -3.05% | 6.78% | -6.61% | ||||||||
10/31/14 | 43.10302 | 93.97139 | 107.945564 | -18.40% | -2.62% | 3.82% | ||||||||
11/30/14 | 38.205803 | 89.640907 | 99.300156 | -11.36% | -4.61% | -8.01% | ||||||||
12/31/14 | 40.313007 | 92.723198 | 102.096245 | 5.52% | 3.44% | 2.82% | ||||||||
1/31/15 | 41.938221 | 95.780754 | 109.526405 | 4.03% | 3.30% | 7.28% | ||||||||
2/28/15 | 46.20462 | 94.368088 | 115.796768 | 10.17% | -1.47% | 5.72% | ||||||||
3/31/15 | 48.812218 | 100.041351 | 128.2267 | 5.64% | 6.01% | 10.73% | ||||||||
4/30/15 | 42.585117 | 98.679451 | 121.432449 | -12.76% | -1.36% | -5.30% | ||||||||
5/31/15 | 48.328697 | 108.054138 | 132.012497 | 13.49% | 9.50% | 8.71% | ||||||||
6/30/15 | 52.020744 | 107.946556 | 134.288925 | 7.64% | -0.10% | 1.72% | ||||||||
7/31/15 | 50.970757 | 111.74102 | 135.855804 | -2.02% | 3.52% | 1.17% | ||||||||
8/31/15 | 54.897453 | 116.634895 | 137.465027 | 7.70% | 4.38% | 1.18% | ||||||||
9/30/15 | 60.973743 | 117.047661 | 141.756165 | 11.07% | 0.35% | 3.12% | ||||||||
10/31/15 | 65.505836 | 117.037827 | 149.67601 | 7.43% | -0.01% | 5.59% | ||||||||
11/30/15 | 72.192863 | 116.79213 | 156.455429 | 10.21% | -0.21% | 4.53% | ||||||||
12/31/15 | 76.091995 | 113.02179 | 168.886551 | 5.40% | -3.23% | 7.95% | ||||||||
1/31/16 | 67.204628 | 106.299118 | 160.73143 | -11.68% | -5.95% | -4.83% | ||||||||
2/29/16 | 62.664707 | 112.162949 | 156.892731 | -6.76% | 5.52% | -2.39% | ||||||||
3/31/16 | 72.401154 | 120.522179 | 178.281311 | 15.54% | 7.45% | 13.63% | ||||||||
4/30/16 | 78.349983 | 123.010948 | 182.300385 | 8.22% | 2.06% | 2.25% | ||||||||
5/31/16 | 90.879066 | 119.289581 | 203.019226 | 15.99% | -3.03% | 11.37% | ||||||||
6/30/16 | 105.886086 | 128.870193 | 204.51561 | 16.51% | 8.03% | 0.74% | ||||||||
7/31/16 | 128.585907 | 138.281509 | 224.98613 | 21.44% | 7.30% | 10.01% | ||||||||
8/31/16 | 115.610542 | 139.910004 | 210.330002 | -10.09% | 1.18% | -6.51% | ||||||||
9/30/16 | 108.672516 | 138.75 | 202.470001 | -6.00% | -0.83% | -3.74% | ||||||||
10/31/16 | 118.485588 | 145.770004 | 223.720001 | 9.03% | 5.06% | 10.50% | ||||||||
11/5/16 | 118.690002 | 145.770004 | 223.720001 | 0.17% | 0.00% | 0.00% |
EF
Return | Risk | Wa | Wb | Wc | Total weight | Efficient frontier for risky portfolio | ||||||||
Table of expected returns and risk | 0.4000% | 7.0479% | -53.7010% | 138.5369% | 15.1642% | 100.0000% | ||||||||
E(return) | Standard Deviation | 0.6000% | 6.2095% | -42.2030% | 124.3108% | 17.8923% | 100.0000% | |||||||
Asset A | 2.62% | 7.81% | 0.8000% | 5.4522% | -30.7223% | 110.1060% | 20.6163% | 100.0000% | ||||||
Asset B | 1.09% | 4.81% | 1.0000% | 4.8123% | -19.2357% | 95.8941% | 23.3416% | 100.0000% | ||||||
Asset C | 1.97% | 6.16% | 1.2000% | 4.3425% | -7.7435% | 81.6752% | 26.0683% | 100.0000% | ||||||
1.4000% | 4.1021% | 3.7430% | 67.4633% | 28.7937% | 100.0000% | |||||||||
Table of covariances | 1.6000% | 4.1310% | 15.2295% | 53.2514% | 31.5191% | 100.0000% | ||||||||
Asset A | Asset B | Asset C | 1.8000% | 4.4238% | 26.7103% | 39.0466% | 34.2431% | 100.0000% | ||||||
Asset A | 0.61% | 0.08% | 0.22% | 2.0000% | 4.9340% | 38.1968% | 24.8348% | 36.9684% | 100.0000% | |||||
Asset B | 0.08% | 0.23% | 0.06% | 2.2000% | 5.6024% | 49.6833% | 10.6229% | 39.6938% | 100.0000% | |||||
Asset C | 0.22% | 0.06% | 0.38% | 2.4000% | 6.3794% | 61.1698% | -3.5890% | 42.4191% | 100.0000% | |||||
2.6000% | 7.2302% | 72.6563% | -17.8009% | 45.1445% | 100.0000% | |||||||||
Proporition of wealth invested in | 2.8000% | 8.1316% | 84.1429% | -32.0127% | 47.8699% | 100.0000% | ||||||||
Asset A | 290.90% | Wa | 3.0000% | 9.0686% | 95.6294% | -46.2246% | 50.5952% | 100.0000% | ||||||
Asset B | -287.83% | Wb | 3.2000% | 10.0311% | 107.1159% | -60.4365% | 53.3206% | 100.0000% | ||||||
Asset C | 96.93% | Wc | 3.4000% | 11.0126% | 118.6024% | -74.6484% | 56.0460% | 100.0000% | ||||||
Total | 1.00 | (this is a CONSTRAINT for "Solver", this cell B23 must be forced to equal one) | 3.6000% | 12.0082% | 130.0889% | -88.8602% | 58.7713% | 100.0000% | ||||||
(it is the constraint that the total proportions of our wealth must equal one) | 3.8000% | 13.0149% | 141.5754% | -103.0721% | 61.4967% | 100.0000% | ||||||||
This bit is used to find the risk of the portfolio | 4.0000% | 14.0301% | 153.0619% | -117.2840% | 64.2220% | 100.0000% | ||||||||
Asset A | Asset B | Asset C | 4.2000% | 15.0523% | 164.5484% | -131.4958% | 66.9474% | 100.0000% | ||||||
Proportion | 290.90% | -287.83% | 96.93% | These numbers are a table of, for instance | 4.4000% | 16.0800% | 176.0349% | -145.7077% | 69.6728% | 100.0000% | ||||
Asset A | 290.90% | 5.1604% | -0.6713% | 0.6270% | proportion of A * proportion of B * | 4.6000% | 17.1122% | 187.5215% | -159.9196% | 72.3981% | 100.0000% | |||
Asset B | -287.83% | -0.6713% | 1.9170% | -0.1564% | covariance of A and B | 4.8000% | 18.1488% | 199.0137% | -174.1386% | 75.1249% | 100.0000% | |||
Asset C | 96.93% | 0.6270% | -0.1564% | 0.3567% | 5.0000% | 19.1875% | 210.4945% | -188.3433% | 77.8489% | 100.0000% | ||||
5.2000% | 20.2299% | 221.9867% | -202.5623% | 80.5756% | 100.0000% | |||||||||
5.4000% | 21.2736% | 233.4675% | -216.7671% | 83.2996% | 100.0000% | |||||||||
5.6000% | 22.3197% | 244.9540% | -230.9790% | 86.0249% | 100.0000% | |||||||||
Expected return on the portfolio of all 3 assets | 5.8000% | 23.3675% | 256.4405% | -245.1908% | 88.7503% | 100.0000% | ||||||||
6.40% | (if you are minimizing risk for a given return you should tell "Solver" that | 6.0000% | 24.4169% | 267.9270% | -259.4027% | 91.4757% | 100.0000% | |||||||
this cell, B35, is fixed--at whatever level of return you want. If you are maximizing | 6.2000% | 25.4675% | 279.4136% | -273.6146% | 94.2010% | 100.0000% | ||||||||
return for a fixed risk, you should tell "Solver" to maximize this cell | 6.4000% | 26.5193% | 290.9001% | -287.8265% | 96.9264% | 100.0000% | ||||||||
Risk associated with the portfolio of all 3 assets | ||||||||||||||
26.52% | (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. | ||||||||||||||
This table is to develop the Efficient Frontier that is shown on Graph (See the blue bullet shape curve) |
Solve List
MVP
This Table is to find the Minimum Variance Portfolio (MVP) and it is captured as the red dot on Graph with return being equal to Cell B31 and risk Cell B36 | |||||
Table of expected returns and risk | |||||
E(return) | Standard Deviation | ||||
Asset A | 2.62% | 7.81% | |||
Asset B | 1.09% | 4.81% | |||
Asset C | 1.97% | 6.16% | |||
Table of covariances | |||||
Asset A | Asset B | Asset C | |||
Asset A | 0.61% | 0.08% | 0.22% | ||
Asset B | 0.08% | 0.23% | 0.06% | ||
Asset C | 0.22% | 0.06% | 0.38% | ||
Proporition of wealth invested in | |||||
Asset A | 0.08 | Wa | |||
Asset B | 0.62 | Wb | |||
Asset C | 0.30 | 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.28% | 61.85% | 29.87% | These numbers are a table of, for instance | |
Asset A | 8.28% | 0.0042% | 0.0041% | 0.0055% | proportion of A * proportion of B * |
Asset B | 61.85% | 0.0041% | 0.0885% | 0.0104% | covariance of A and B |
Asset C | 29.87% | 0.0055% | 0.0104% | 0.0339% | |
Expected return on the portfolio of all 3 assets | |||||
1.48% | (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 | |||||
4.08% | (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. |
Sharpe Ratio for CML
This table is to develop the Capital Market Line on Graph (See the red straight line) | ||||||||||||||||
Table of expected returns and risk on the risky assets | Capital market line | |||||||||||||||
E(return) | Risk | risk | return | market price of risk (Slope of CML) | 0.3378039691 | |||||||||||
Asset A | 2.62% | 7.81% | 0.00% | 0.33% | risk free rate | 0.33% | ||||||||||
Asset B | 1.09% | 4.81% | 1.00% | 0.67% | ||||||||||||
Asset C | 1.97% | 6.16% | 2.00% | 1.01% | ||||||||||||
3.00% | 1.35% | |||||||||||||||
Riskfree rate | 0.33% | The return on the risk free asset | 4.00% | 1.68% | ||||||||||||
5.00% | 2.02% | |||||||||||||||
6.00% | 2.36% | |||||||||||||||
Table of covariances for the risky assets | 7.00% | 2.70% | ||||||||||||||
Asset A | Asset B | Asset C | Construct covariance matrix using formulae | 8.00% | 3.04% | |||||||||||
Asset A | 0.61% | 0.08% | 0.22% | such as covar(A,B)=risk(A)*risk(B)*correl(A,B) | 9.00% | 3.37% | ||||||||||
Asset B | 0.08% | 0.23% | 0.06% | 10.00% | 3.71% | |||||||||||
Asset C | 0.22% | 0.06% | 0.38% | 11.00% | 4.05% | |||||||||||
12.00% | 4.39% | |||||||||||||||
Proporition of wealth invested in purely risky assets | 13.00% | 4.72% | ||||||||||||||
Asset A | 0.3771 | Wa | Normally these values are set by "Solver" but it may be necessary to | 14.00% | 5.06% | |||||||||||
Asset B | 0.2543 | Wb | reset them to "sensible" values and then re-run "Solver" if "Solver" | 15.00% | 5.40% | |||||||||||
Asset C | 0.3685 | Wc | fails to find a solution (eg, B23=1/3, B24=1/3, B25=1/3 ) | 16.00% | 5.74% | |||||||||||
Total | 1.0000 | (this is a CONSTRAINT for "Solver", this cell B26 must be forced to equal one) | 17.00% | 6.08% | ||||||||||||
(it is the constraint that the sum of the proportions of our wealth must equal one) | 18.00% | 6.41% | ||||||||||||||
This bit is used to find the risk of the portfolio of purely risky assets | 19.00% | 6.75% | ||||||||||||||
Asset A | Asset B | Asset C | 20.00% | 7.09% | ||||||||||||
Proportion | 37.71% | 25.43% | 36.85% | These numbers are a table of, for instance | 21.00% | 7.43% | ||||||||||
Asset A | 37.71% | 0.0867% | 0.0077% | 0.0309% | proportion of A * proportion of B * | 22.00% | 7.77% | |||||||||
Asset B | 25.43% | 0.0077% | 0.0150% | 0.0053% | covariance of A and B | 23.00% | 8.10% | |||||||||
Asset C | 36.85% | 0.0309% | 0.0053% | 0.0516% | 24.00% | 8.44% | ||||||||||
25.00% | 8.78% | |||||||||||||||
26.00% | 9.12% | |||||||||||||||
27.00% | 9.45% | |||||||||||||||
Expected return on the portfolio of all 3 risky assets | 28.00% | 9.79% | ||||||||||||||
1.99% | 29.00% | 10.13% | ||||||||||||||
Risk associated with the portfolio of all 3 risky assets | ||||||||||||||||
4.91% | ||||||||||||||||
Market Price of Risk ( = Slope of the Capital Market Line) | ||||||||||||||||
0.3378039691 | ||||||||||||||||
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. |
Graph
risk
return
efficient frontier 7.0479357901693143E-2 6.2094721368613007E-2 5.4521703149258999E-2 4.8122944988587379E-2 4.3424645534851224E-2 4.1020629367338385E-2 4.1310080547350782E-2 4.423810560293337E-2 4.9339683255376084E-2 5.6023541725036423E-2 6.379426806822508E-2 7.2302272278067639E-2 8.1316462306538537E-2 9.0686018959910103E-2 0.10031141288413428 0.11012558105223758 0.12008224754992818 0.13014871216596469 0.14030134321361157 0.15052270614062097 0.16079969448825279 0.1711222865625712 0.1814878894068678 0.19187481065012935 0.20229894685339544 0.21273552377230204 0.22319697785107537 0.2336754529178155 0.24416875762084478 0.25467505890278119 0.26519281208854578 4.0000000000000001E-3 6.0000000000000001E-3 8.0000000000000002E-3 0.01 1.2E-2 1.4E-2 1.6E-2 1.7999999999999999E-2 0.02 2.1999999999999999E-2 2.4E-2 2.5999999999999999E-2 2.8000000000000001E-2 0.03 3.2000000000000001E-2 3.4000000000000002E-2 3.5999999999999997E-2 3.7999999999999999E-2 0.04 4.2000000000000003E-2 4.3999999999999997E- 2 4.5999999999999999E-2 4.8000000000000001E-2 0.05 5.1999999999999998E-2 5.3999999999999999E-2 5.6000000000000001E-2 5.8000000000000003E-2 0.06 6.2E-2 6.4000000000000001E-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.7113730245712145E-3 1.0089412715809097E-2 1.3467452407046977E-2 1.684549209828486E-2 2.0223531789522742E-2 2.3601571480760621E-2 2.6979611171998506E-2 3.0357650863236385E-2 3.3735690554474264E-2 3.711373024571215E-2 4.0491769936950028E-2 4.3869809628187907E-2 4.7247849319425793E-2 5.0625889010663679E-2 5.400392870190155E-2 5.7381968393139436E-2 6.0760008084377322E-2 6.4138047775615187E-2 6.7516087466853072E-2 7.0894127158090958E-2 7.427216684932883E-2 7.7650206540566716E-2 8.1028246231804602E-2 8.4406285923042473E-2 8.7784325614280359E-2 9.1162365305518245E-2 9.4540404996756131E-2 9.7918444687994016E-2 0.10129648437923187 assets 7.8090251963799789E-2 4.8103940318018228E-2 6.1622220592084359E-2 2.6201851326850191E-2 1.0885197647799849E-2 1.9715245030342825E-2 efficient frontier 7.0479357901693143E-2 6.2094721368613007E-2 5.4521703149258999E-2 4.8122944988587379E-2 4.3424645534851224E-2 4.1020629367338385E-2 4.131008054735078 2E-2 4.423810560293337E-2 4.9339683255376084E-2 5.6023541725036423E-2 6.379426806822508E-2 7.2302272278067639E-2 8.1316462306538537E-2 9.0686018959910103E-2 0.10031141288413428 0.11012558105223758 0.12008224754992818 0.13014871216596469 0.14030134321361157 0.15052270614062097 0.16079969448825279 0.1711222865625712 0.1814878894068678 0.19187481065012935 0.20229894685339544 0.21273552377230204 0.22319697785107537 0.2336754529178155 0.24416875762084478 0.25467505890278119 0.26519281208854578 4.0000000000000001E-3 6.0000000000000001E-3 8.0000000000000002E-3 0.01 1.2E-2 1.4E-2 1.6E-2 1.7999999999999999E-2 0.02 2.1999999999999999E-2 2.4E-2 2.5999999999999999E-2 2.8000000000000001E-2 0.03 3.2000000000000001E-2 3.4000000000000002E-2 3.5999999999999997E-2 3.7999999999999999E-2 0.04 4.2000000000000003E-2 4.3999999999999997E-2 4.5999999999999999E-2 4.8000000000000001E-2 0.05 5.1999999999999998E-2 5.3999999999999999E-2 5.6000000000000001E-2 5.8000000000000003E-2 0.06 6.2E-2 6.4000000000000001E-2 Stocks 7.8090251963799789E-2 4.8103940318018228E-2 6.1622220592084359E-2 2.6201851326850191E-2 1.0885197647799849E-2 1.9715245030342825E-2 MVP 4.0804421324903514E-2 1.4790888261786719E-2
risk
return