Fluent Essays

You are assigned with five stocks (See the excel file titled “Companies assignment”) and you are expected to build the investment portfolios using those five stocks. Note that I showed you an example of a three-stock portfolio in the attached excel file. You are expected to apply the same concepts to the five-stock portfolios.Please collect monthly stock price for the five companies that you are assigned from FINANCE.YAHOO.COM and employ the data time range from Jan 1, 2015 to June 30, 2021. Then using the risk-free rate of 0.35\%, you are expected to develop the complete portfolio and develop the efficient frontier of these six securities (T-bill and five stocks). Following are the procedure in details: Collect monthly stock price data from Finance.YAHOO.COM. The time range for the date should be from Jan 1, 2015 to June 30, 2021. Compute monthly stock returns for each stock ( monthly rate of return = (P1 – P0) /P0; P is monthly adjusted close price). Then compute mean and standard deviation of stock returns for each company (See my excel example with the sheet entitled ‘Data’) Develop Table of Covariance of these five stocks (See my excel example with the sheet entitled ‘Data’) Develop Efficient Frontier for all risky portfolios constructed with 5 assigned stocks (See my excel example with the sheet entitled ‘EF’ and ‘Graph’) Find the Minimum-Variance risky portfolio (See my excel example with the sheet entitled ‘MVP’) Find the Efficient portfolios including a risk free asset with risk free rate of 0.35\% (See my excel example with the sheet entitled ‘Sharpe Ratio for CML’ ) Draw the capital market line (CML) (See my example with the sheet entitled ‘Sharpe Ratio for CML’ and ‘Graph’) gc_208FIN435001_fullgc_2020-11- Student Name STOCK 1 STOCK 2 STOCK 3 STOCK 4 STOCK 5 Company Name Ticker Company Name Ticker Company Name Ticker Company Name Ticker Company Name Ticker almansoori, majed Polo Ralph Lauren RL Proctor Gamble Co. PG Pepsico Inc. PEP Nike NKE McCormick MKC Alvarado, Marilin Proctor Gamble Co. PG Pepsico Inc. PEP Northern Oil and Gas, Inc NOG McCormick MKC McDonalds MCD Bautista, Julia Pepsico Inc. PEP Northern Oil and Gas, Inc NOG Nike NKE McDonalds MCD Southwest Airlines Co LUV Chai, Yitong Northern Oil and Gas, Inc NOG Nike NKE McCormick MKC Southwest Airlines Co LUV Kroger KR Chen, Xi Nike NKE McCormick MKC McDonalds MCD Kroger KR Kimberly-Clark KMB Franco, Gerardo McCormick MKC McDonalds MCD Southwest Airlines Co LUV Kimberly-Clark KMB Hormel Foods HRL Haley, Alyvia McDonalds MCD Southwest Airlines Co LUV Kroger KR Hormel Foods HRL Hasbro HAS Haro, Kristian Southwest Airlines Co LUV Kroger KR Kimberly-Clark KMB Hasbro HAS Alphabet Inc GOOGL Hernandez, Osiris Kroger KR Kimberly-Clark KMB Hormel Foods HRL Alphabet Inc GOOGL GameStop GME Huang, Wenkai Kimberly-Clark KMB Hormel Foods HRL Hasbro HAS GameStop GME General Mills GIS Hylin, Julie Hormel Foods HRL Hasbro HAS Alphabet Inc GOOGL General Mills GIS Fossil FOSL Li, Rongfeng Hasbro HAS Alphabet Inc GOOGL GameStop GME Fossil FOSL Ecolab ECL Luo, Yu Alphabet Inc GOOGL GameStop GME General Mills GIS Ecolab ECL Chevron Corp CVX Lyu, Jianfei GameStop GME General Mills GIS Fossil FOSL Chevron Corp CVX Campbell Soup CPB Mendez, Silvina General Mills GIS Fossil FOSL Ecolab ECL Campbell Soup CPB Costco Wholesale COST Monzon, Eduardo Fossil FOSL Ecolab ECL Chevron Corp CVX Costco Wholesale COST Coca-Cola Bottling COKE Nguyen, Manh Tien Ecolab ECL Chevron Corp CVX Campbell Soup CPB Coca-Cola Bottling COKE Biglari Holdings BH Ortega, Tatum Chevron Corp CVX Campbell Soup CPB Costco Wholesale COST Biglari Holdings BH Clorox CLX WANG, YIKANG Campbell Soup CPB Costco Wholesale COST Coca-Cola Bottling COKE Clorox CLX Colgate Palmolive CL Zhai, Jiangling Costco Wholesale COST Coca-Cola Bottling COKE Biglari Holdings BH Colgate Palmolive CL Caterpillar CAT Zhu, Junyun Coca-Cola Bottling COKE Biglari Holdings BH Clorox CLX Caterpillar CAT Ball Corporation BLL 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 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 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 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 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