This is an assignment about the stock project. Be sure to read every question in the word document carefully and do not leave out any word

profileMichelle_Michy
2019120203491220191129130231project_stock..xlsm
Home>Business & Finance homework help>Management homework help>This is an assignment about the stock project. Be sure to read every question in the word document carefully and do not leave out any word

Stock Project Stock Prices

IBM MSFT S&P500
Date Adj Close Returns Adj Close Returns Adj Close Returns
1/1/13 83.1630095 11.8642395 1498.109985
2/1/13 82.245674 -1.12% 12.0155115 1.26% 1514.680054 1.09%
3/1/13 87.7201765 6.24% 12.467986 3.63% 1569.189941 3.47%
4/1/13 83.2951125 -5.31% 14.4246875 13.56% 1597.569946 1.78%
5/1/13 85.548767 2.63% 15.2091095 5.16% 1630.73999 2.03%
6/1/13 78.9628675 -8.34% 15.157777 -0.34% 1606.280029 -1.52%
7/1/13 80.5866625 2.01% 13.972888 -8.48% 1685.72998 4.71%
8/1/13 75.310341 -7.01% 14.65749 4.67% 1632.969971 -3.23%
9/1/13 76.8952105 2.06% 14.7077415 0.34% 1681.550049 2.89%
10/1/13 74.416176 -3.33% 15.6490755 6.02% 1756.540039 4.27%
11/1/13 74.611336 0.26% 16.8511525 7.13% 1805.810059 2.73%
12/1/13 78.305908 4.72% 16.6583385 -1.16% 1848.359985 2.30%
1/1/14 73.7596055 -6.16% 16.8498115 1.14% 1782.589966 -3.69%
2/1/14 77.3039625 4.58% 17.059103 1.23% 1859.449951 4.13%
3/1/14 80.8004225 4.33% 18.3893545 7.23% 1872.339966 0.69%
4/1/14 82.4711 2.03% 18.1246565 -1.46% 1883.949951 0.62%
5/1/14 77.3877565 -6.57% 18.3669185 1.32% 1923.569946 2.06%
6/1/14 76.5337145 -1.12% 18.839857 2.51% 1960.22998 1.87%
7/1/14 80.9246675 5.43% 19.4994795 3.38% 1930.670044 -1.53%
8/1/14 81.1906585 0.33% 20.525049 5.00% 2003.369995 3.63%
9/1/14 80.6218035 -0.71% 21.0760405 2.61% 1972.290039 -1.58%
10/1/14 69.821541 -15.47% 21.344263 1.26% 2018.050049 2.27%
11/1/14 68.8744585 -1.38% 21.735237 1.80% 2067.560059 2.39%
12/1/14 68.6060715 -0.39% 21.250147 -2.28% 2058.899902 -0.42%
1/1/15 65.557205 -4.65% 18.4823665 -14.98% 1994.98999 -3.20%
2/1/15 69.247505 5.33% 20.0606825 7.87% 2104.5 5.20%
3/1/15 69.113167 -0.19% 18.7336905 -7.08% 2067.889893 -1.77%
4/1/15 73.7594755 6.30% 22.410393 16.41% 2085.51001 0.84%
5/1/15 73.053261 -0.97% 21.5902765 -3.80% 2107.389893 1.04%
6/1/15 71.11705 -2.72% 20.4738675 -5.45% 2063.110107 -2.15%
7/1/15 70.8241045 -0.41% 21.656395 5.46% 2103.840088 1.94%
8/1/15 64.659401 -9.53% 20.181717 -7.31% 1972.180054 -6.68%
9/1/15 63.9089165 -1.17% 20.660227 2.32% 1920.030029 -2.72%
10/1/15 61.753193 -3.49% 24.571949 15.92% 2079.360107 7.66%
11/1/15 61.4622345 -0.47% 25.370165 3.15% 2080.409912 0.05%
12/1/15 61.2378005 -0.37% 26.072201 2.69% 2043.939941 -1.78%
1/1/16 55.5287475 -10.28% 25.8889235 -0.71% 1940.23999 -5.34%
2/1/16 58.305401 4.76% 23.9104805 -8.27% 1932.22998 -0.41%
3/1/16 68.0802155 14.36% 26.1410675 8.53% 2059.73999 6.19%
4/1/16 65.603363 -3.78% 23.6041105 -10.75% 2065.300049 0.27%
5/1/16 69.1096345 5.07% 25.085577 5.91% 2096.949951 1.51%
6/1/16 69.54805 0.63% 24.388815 -2.86% 2098.860107 0.09%
7/1/16 73.598694 5.50% 27.015009 9.72% 2173.600098 3.44%
8/1/16 72.801399 -1.10% 27.386776 1.36% 2170.949951 -0.12%
9/1/16 73.4162825 0.84% 27.624613 0.86% 2168.27002 -0.12%
10/1/16 71.0314635 -3.36% 28.7372705 3.87% 2126.149902 -1.98%
11/1/16 74.9738235 5.26% 28.9003315 0.56% 2198.810059 3.30%
12/1/16 77.412178 3.15% 30.003294 3.68% 2238.830078 1.79%
1/1/17 81.3902895 4.89% 31.215212 3.88% 2278.870117 1.76%
2/1/17 83.86203 2.95% 30.891712 -1.05% 2363.639893 3.59%
3/1/17 81.855217 -2.45% 31.99222 3.44% 2362.719971 -0.04%
4/1/17 75.3449705 -8.64% 33.2551995 3.80% 2384.199951 0.90%
5/1/17 71.744362 -5.02% 33.9255485 1.98% 2411.800049 1.14%
6/1/17 73.014786 1.74% 33.675434 -0.74% 2423.409912 0.48%
7/1/17 68.6670225 -6.33% 35.51725 5.19% 2470.300049 1.90%
8/1/17 67.888611 -1.15% 36.528538 2.77% 2471.649902 0.05%
9/1/17 69.5891955 2.44% 36.585636 0.16% 2519.360107 1.89%
10/1/17 73.896553 5.83% 40.853718 10.45% 2575.26001 2.17%
11/1/17 71.5270235 -3.31% 41.3399545 1.18% 2584.840088 0.37%
12/1/17 74.325119 3.76% 42.2238195 2.09% 2673.610107 3.32%
Exercise one
Average return -0.32% 1.96% 0.94%
Standard deviation 0.05 0.06 0.03
Variance 0.26% 0.33% 0.07%
Covariance 0.07%
Corelation 0.23
Exercise two
Average return -0.32% 1.96%
Standard deviation 5.14% 0.06
Portfolio 0% 100%
Average return 1.96%
Standard deviation 5.77%
Standard deviation Expected return
Portfolio 10% 90% 0-100% 5.77% 1.96%
Average return 1.73% 10-90% 5.71% 1.73%
Standard deviation 5.71% 20-80% 5.64% 1.51%
30-70% 5.58% 1.28%
Portfolio 20.00% 80.0% 40-60% 5.52% 1.05%
Average return 1.51% 50-50% 5.46% 0.82%
Standard deviation 5.64% 60-40% 5.39% 0.59%
70-30% 5.33% 0.36%
Portfolio 30.00% 70.0% 80-20% 5.27% 0.14%
Average return 1.28% 90-10% 5.21% -0.09%
Standard deviation 5.58% 100-0% 5.14% -0.32%
Portfolio 40.00% 60.0%
Average return 1.05%
Standard deviation 5.52%
Portfolio 50.00% 50.0%
Average return 0.82%
Standard deviation 5.46%
Portfolio 60.00% 40.0%
Average return 0.59%
Standard deviation 5.39%
Portfolio 70.00% 30.0%
Average return 0.36%
Standard deviation 5.33%
Portfolio 80.00% 20.0% Standard deviation Average return Risk free rate
Average return 0.14% 5.77% 1.96% 0.0015
Standard deviation 5.27% 5.71% 1.73% 0.0015
5.64% 1.51% 0.0015
Portfolio 90.00% 10.0% 5.58% 1.28% 0.0015
Average return -0.09% 5.52% 1.05% 0.0015
Standard deviation 5.21% 5.46% 0.82% 0.0015
5.39% 0.59% 0.0015
Portfolio 100.00% 0.0% 5.33% 0.36% 0.0015
Average return -0.32% 5.27% 0.14% 0.0015
Standard deviation 5.14% 5.21% -0.09% 0.0015
Exercise three
Part 1
Minimum variance portfolio
Portfolio 80% 20%
Average return 0.14%
Standard deviation 5.27%
Sharpe ratio -0.15
Part 11
Risk free rate 0.15%
Optimal portfolio
CAL
Returns Std deviation
0.14% 5.27%
1.96% 5.77%
CAL 3.64
Part 111
The market falls at the point 5.27% and the 0.015.

Returns against standard deviation

0.0576944585278052 0.0570683293589871 0.0564422001901691 0.055816071021351 0.0551899418525329 0.0545638126837148 0.0539376835148967 0.0533115543460786 0.0526854251772605 0.0520592960084425 0.0514331668396244 0.0196196939988989 0.017338129758511 0.015056565518123 0.012775001277735 0.010493437037347 0.00821187279695906 0.00593030855657109 0.00364874431618312 0.00136718007579514 -0.000914384164592835 -0.00319594840498081

Graph of returns against risk

0.0576944585278052 0.0570683293589871 0.0564422001901691 0.055816071021351 0.0551899418525329 0.0545638126837148 0.0539376835148967 0.0533115543460786 0.0526854251772605 0.0520592960084425 0.0196196939988989 0.017338129758511 0.015056565518123 0.012775001277735 0.010493437037347 0.00821187279695906 0.00593030855657109 0.00364874431618312 0.00136718007579514 -0.000914384164592835 0.0576944585278052 0.0570683293589871 0.0564422001901691 0.055816071021351 0.0551899418525329 0.0545638126837148 0.0539376835148967 0.0533115543460786 0.0526854251772605 0.0520592960084425 0.0015 0.0015 0.0015 0.0015 0.0015 0.0015 0.0015 0.0015 0.0015 0.0015

Sheet1

Question 3 part 1V
IBM MSFT
2013 -7.17% 31.80%
2014 -15.10% 23.73%
2015 -12.36% 15.19%
2016 21.06% 11.90%
2017 -5.29% 33.13%
Correlation of +1
IBM Returns MSFT
2013 -7.17% -9.17%
2014 -15.10% -17.10%
2015 -12.36% -14.36%
2016 21.06% 23.06%
2017 -5.29% -7.29%
Question 3 part V
Correlation of -1
IBM Returns MSFT
2013 -7.17% 7.17%
2014 -15.10% 15.10%
2015 -12.36% 12.36%
2016 21.06% -21.06%
2017 -5.29% 5.29%

Returns with a positive correlation

IBM Returns 2013.0 2014.0 2015.0 2016.0 2017.0 -0.0717483071302416 -0.150968930867226 -0.123587409892748 0.210639567312847 -0.0528958753165002 MSFT 2013.0 2014.0 2015.0 2016.0 2017.0 -0.0917483071302416 -0.170968930867226 -0.143587409892748 0.230639567312847 -0.0728958753165002

Returns with a negative correlation

IBM Returns 2013.0 2014.0 2015.0 2016.0 2017.0 -0.0717483071302416 -0.150968930867226 -0.123587409892748 0.210639567312847 -0.0528958753165002 MSFT 2013.0 2014.0 2015.0 2016.0 2017.0 0.0717483071302416 0.150968930867226 0.123587409892748 -0.210639567312847 0.0528958753165002