NEED A PERFECT PAPER? PLACE YOUR FIRST ORDER AND SAVE 15% USING COUPON:

finance Excel

finance Excel

Click here to Order a Custom answer to this Question from our writers. It’s fast and plagiarism-free.

finance Excel finance Excel 

Data

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

Place your order now for a similar assignment and have exceptional work written by one of our experts, guaranteeing you an A result.

Need an Essay Written?

This sample is available to anyone. If you want a unique paper order it from one of our professional writers.

Get help with your academic paper right away

Quality & Timely Delivery

Free Editing & Plagiarism Check

Security, Privacy & Confidentiality

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%