YOU CAN SUBMIT AN EXCEL FILE FOR THIS ASSIGNMENT Calculate the monthly returns of the stocks of the following companies from January 2014 to May 2020. Yahoo Finance provides monthly returns! Use the prices given under "adjusted close" to avoid dividend and split issues. IBM Tesla 1. (50 points) What is the monthly expected return and the standard deviation of a portfolio that is composed of (x% IBM and y% Tesla), where {x,y | x%+y%=100%, x,y in {0,100}}* *i.e. do not have negative weights, only positive weights. Have at least 100 expected value and standard deviation combinations calculated and plot the "opportunity set". 2. (30 points) What is the composition (the weights of the assets) of a portfolio that is formed by investing in IBM and Tesla stocks and that has the lowest standard deviation? (That is, what is the weight of each stock in the lowest standard deviation portfolio...among all possible combinations, not only among the ones you calculated in question 1). You are expected to solve this question analytically. 3. (20 points) You have $10,000. What is the expected value and the standard deviation of a portfolio that is formed by investing $5K at the risk free rate and $5K on the portfolio you found in question 2 (assume that monthly risk free rate is 0.0025%)? Does there exist another portfolio consisting of these assets AND that has a higher expected return and lower risk, measured by standard deviation? If there is, describe that portfolio with one sentence, do not calculate it. Date Adj Close (Te! Adj Close (IBM) 12/1/13 10.02867 1/1/14 20.59% 2/1/14 16.32067 115.294 34.95% 3/1/14 13.89667 120.5088 -14.85% 4/1/14 13.85933 123.0005 13.85133 16.004 14.88667 5/1/14 6/1/14 114.1452 7/1/14 120.6941 8/1/14 17.98 121.0908 9/1/14 16.17867 120.2423 10/1/14 16.11333 104.1345 11/1/14 16.30133 102.7219 12/1/14 14.82733 102.3216 1/1/15 13.57333 97.77445 2/1/15 115.419 -0.27% -0.06% 15.54% -6.98% 20.78% -10.02% -0.40% 1.17% -9.04% -8.46% 13.556 103.2783 -0.13% 3/1/15 12.58467 103.078 -7.17% -0.19% 4/1/15 15.07 110.0076 19.75% 5/1/15 16.72 108.9544 10.95% 6/1/15 17,884 105.2558 7/1/15 17.74333 104.8222 6.96% -0.79% 8/1/15 16.604 95.69823 -6.42% 9/1/15 16.56 94.58746 -0.27% 10/1/15 13.79533 91.39694 -16.69% 11/1/15 15.35067 90.9663 12/1/15 16.00067 90.63415 11.27% 4.23% 1/1/16 12.74667 82.18453 -20.34% 2/1/16 3/1/16 4/1/16 5/1/16 12.79533 15.318 16.05067 86.29409 100.7612 97.09529 14.882 102.2847 0.38% 19.72% 4.78% -7.28% 6/1/16 14.152 101.9552 -4.91% 7/1/16 8/1/16 9/1/16 10/1/16 15.65267 107.8933 10.60% 11/1/16 12/1/16 1/1/17 2/1/17 3/1/17 4/1/17 5/1/17 14.134 106.7245 13.602 107.6259 13.182 104.1298 12.62667 109.9092 14.246 113.4838 16.79533 16.666 18.55333 20.938 22.734 105.1749 6/1/17 24.10733 107.0373 -9.70% -3.76% -3.09% -4.21% 12.82% 119.3155 17.90% 122.9391 -0.77% 119.9971 11.32% 110.4533 12.85% 8.58% 116.7883 Monthly Retu Monthly Retu Tesla Weight IBM Weight Portfolio Retu Portfolio Standard Deviation 12.094 110.0078 -5.81% 0.982165 0.017835 0.201234 #NAME? 4.81% 0.582743 0.417257 0.22371 4.52% 0.005766 0.994234 0.044113 2.07% 0.646761 0.353239 0.005566 -6.16% 0.664243 0.335757 -0.021079 -1.10% 0.836487 0.163513 0.128196 5.74% 0.684774 0.315226 -0.029723 0.33% 0.601535 0.398465 0.126304 -0.70% 0.974318 0.025682 -0.097792 -13.40% 0.714466 0.285534 -0.041136 -1.36% 0.22738 0.77262 -0.007827 -0.39% 0.790589 0.209411 -0.072303 -4.44% 0.260809 0.739191 -0.054907 5.63% 0.892911 0.107089 0.004888 0.90575 0.09425 -0.065083 6.72% 0.89545 0.10455 0.18387 -0.96% 0.954918 0.045082 0.104121 -3.39% 0.337649 0.662351 0.001022 -0.41% 0.521087 0.478913 -0.006071 -8.70% 0.124808 0.875192 -0.084193 -1.16% 0.649338 0.350662 -0.005791 -3.37% 0.292791 0.707209 -0.072736 -0.47% 0.761996 0.238004 0.084789 -0.37% 0.528882 0.471118 0.020675 -9.32% 0.335562 0.664438 -0.130186 5.00% 0.614728 0.385272 0.021612 16.76% 0.963866 0.036134 0.196089 -3.64% 0.885956 0.114044 0.038227 5.34% 0.005343 0.994657 0.052772 -0.32% 0.075794 0.924206 -0.006695 5.82% 0.69422 -1.08% 0.162427 0.84% 0.771417 -3.25% 0.321716 0.678284 -0.031967 5.55% 0.730964 0.269036 -0.015862 3.25% 0.19325 0.80675 0.051022 5.14% 0.864515 0.135485 0.161668 3.04% 0.818983 0.181017 -0.000809 -2.39% 0.385167 0.614833 -7.95% 0.5187 -4.78% 0.533667 0.30578 0.091424 0.837573 -0.024833 0.228583 -0.027105 0.028905 0.4813 0.466333 0.028389 0.023491 6.04% 7/1/17 8/1/17 9/1/17 10/1/17 11/1/17 3/1/18 17.742 4/1/18 19.59333 5/1/18 18.982 6/1/18 22.86333 7/1/18 19.876 8/1/18 20.11067 9/1/18 17.65133 110.8345 21.56467 100.6636 23.72667 99.52251 22.74 102.0155 22.102 108.3299 20.59 108.2667 12/1/17 20.75667 108.9582 1/1/18 23.62067 116.259 2/1/18 22.87067 110.6698 110.0382 103.9636 101.3458 -3.12% 101.3034 20.45% 105.0959 -13.07% 106.2199 1.18% -12.23% -10.55% 10.03% -4.16% -2.81% -6.84% 0.81% 13.80% -3.18% -22.42% 10.43% -0.57% 0.269027 -5.52% 0.730041 -2.52% 0.03269 10/1/18 22.488 84.60832 11/1/18 23.36533 91.08791 12/1/18 22.18667 84.37944 99.78259 1/1/19 20.468 2/1/19 21.32533 102.5366 3/1/19 18.65733 105.9616 4/1/19 15.91267 105.3383 5/1/19 12.344 95.36546 6/1/19 14.89733 104.7887 7/1/19 16.10733 112.6459 8/1/19 15.04067 102.9877 27.40% 3.90% -5.04% -7.75% 4.19% -12.51% -14.71% -22.43% 20.68% 8.12% -6.62% 9/1/19 16.058 10/1/19 20.99467 102.8173 11/1/19 21.996 103.3708 12/1/19 27.88867 104.2728 1/1/20 43.37133 111.8109 2/1/20 44.53267 101.2467 3/1/20 34.93333 87.1961 4/1/20 52.12533 98.69597 5/1/20 55.66667 98.17719 111.805 6.76% 30.74% 4.77% 26.79% 55.52% 2.68% -21.56% 49.21% 6.79% 1.77% 0.731933 0.268067 0.048962 -5.95% 0.509282 0.490718 -0.082936 -1.13% 0.333988 0.666012 0.025935 2.50% 0.847338 0.152662 -0.031412 6.19% 0.769846 0.230154 -0.007353 -0.06% 0.742513 0.257487 -0.050946 0.64% 0.744604 0.255396 0.007659 6.70% 0.63829 0.36171 0.112308 -4.81% 0.113348 0.886652 -0.046225 0.730973 0.269959 0.96731 -0.04% 0.717563 0.282437 3.74% 0.880355 0.119645 -0.110548 1.07% 0.390224 0.609776 0.011129 4.34% 0.775926 0.224074 -0.085153 -23.66% 0.500546 0.499454 0.018972 7.66% 0.947647 0.052353 0.04098 -7.36% 0.512073 0.487927 -0.061767 18.25% 2.25E-05 0.999978 0.18254 2.76% 0.479849 0.520151 0.034455 3.34% 0.445642 0.554358 -0.037237 -0.59% 0.594158 0.405842 -0.089793 -9.47% 0.869518 0.130482 -0.207357 9.88% 0.131006 0.868994 0.112965 7.50% 0.158328 0.841672 0.07597 -8.57% 0.896475 0.103525 -0.068243 8.56% 0.782927 0.217073 0.071541 -8.04% 0.899198 0.100802 0.268335 0.54% 0.697459 0.302541 0.034894 0.87% 0.259963 0.740037 0.076101 7.23% 0.936783 0.063217 0.524634 -9.45% 0.706075 0.293925 -0.008865 -13.88% 0.294931 0.705069 -0.161421 13.19% 0.726779 0.273221 0.393709 -0.53% 0.471766 0.528234 0.029275 0.265476 0.734524 0.731758 0.268242 -0.0645 0.061275 -0.025376 0.146605 0 0 Covariance Calculation 0.002109