Question

	Find below monthly levels of the S&P 500 Index and monthly Microsoft stock prices for the last five years. Any dividends paid are acoounted for in the closing monthly indices and prices. Follow the instructions below to make estimations and to answer all questions.
	Date	Adj Close S&P 500 Index	Adj Close MSFT Stock Price	Monthly Return S&P 500	Monthly Return MSFT
	4/1/13	1597.57	29.09
	5/1/13	1630.74	30.67
	6/1/13	1606.28	30.56
	7/1/13	1685.73	28.17
	8/1/13	1632.97	29.56
	9/1/13	1681.55	29.66
	10/1/13	1756.54	31.55
	11/1/13	1805.81	33.98
	12/1/13	1848.36	33.59
	1/1/14	1782.59	33.98
	2/1/14	1859.45	34.40
	3/1/14	1872.34	37.08
	4/1/14	1883.95	36.55
	5/1/14	1923.57	37.04
	6/1/14	1960.23	37.99
	7/1/14	1930.67	39.32
	8/1/14	2003.37	41.39
	9/1/14	1972.29	42.50
	10/1/14	2018.05	43.04
	11/1/14	2067.56	43.83
	12/1/14	2058.90	42.85
	1/1/15	1994.99	37.27
	2/1/15	2104.50	40.45
	3/1/15	2067.89	37.77
	4/1/15	2085.51	45.19
	5/1/15	2107.39	43.53
	6/1/15	2063.11	41.28
	7/1/15	2103.84	43.67
	8/1/15	1972.18	40.69
	9/1/15	1920.03	41.66
	10/1/15	2079.36	49.55
	11/1/15	2080.41	51.16
	12/1/15	2043.94	52.57
	1/1/16	1940.24	52.20
	2/1/16	1932.23	48.21
	3/1/16	2059.74	52.71
	4/1/16	2065.30	47.60
	5/1/16	2096.95	50.58
	6/1/16	2098.86	49.18
	7/1/16	2173.60	54.47
	8/1/16	2170.95	55.22
	9/1/16	2168.27	55.70
	10/1/16	2126.15	57.95
11/1/16	2198.81	58.27
12/1/16	2238.83	60.50
1/1/17	2278.87	62.94
2/1/17	2363.64	62.29
3/1/17	2362.72	64.51
4/1/17	2384.20	67.06
5/1/17	2411.80	68.41
6/1/17	2423.41	67.90
7/1/17	2470.30	71.62
8/1/17	2471.65	73.66
9/1/17	2519.36	73.77
10/1/17	2575.26	82.38
11/1/17	2647.58	83.36
12/1/17	2673.61	85.14
1/1/18	2823.81	94.57
2/1/18	2713.83	93.33
3/1/18	2640.87	91.27
4/1/18	2581.88	89.71
Average Monthly Return for the S&P 500 and MSFT
Standard Deviation of Returns for the S&P 500 and MSFT
Coefficient of Variation for the S&P 500 and MSFT
INSTRUCTIONS: Make sure you complete each task below and answer all questions to qualify for full credit. A complete report may add up to 5 points to your total points class score.
SUBMISSION DATE: To qualify for full credit submit a complet report through the Dropbox by 11:59 pm on April 23, 2018.
1. Use Excel commands to calculate the monthly return for the S&P 500 and MSFT by estimating the monthly percentage change in the S&P 500 indices and MSFT stock prices
Show each percentage change using the percentage format with two decimals. Generate a percentage change from cell 3 to 4, 4 to 5,…, 62 to 63 for both the S&P 500 and MSFT.
2. Use Excel comands to calculate at the bottom of each return series column the average monthly return, standard deviation of returns, and coefficient of variation for both the S&P 500 and MSFT.
3. Which had better average monthly performance, MSFT or the S&P 500? Which showed more total volatility? Which showed more relative variability as per the Coefficient of Variation?
4. Generate a chart (plot) of monthly returns of both the S&P 500 and MSFT on a YX graph. Make sure Y is MSFT Returns and X is S&P 500 Returns.
5. Estimate the Beta (slope of the linear regression equation based on your graph) for MSFT. You may use instead the slope Excel command to estimate Beta. How volatile is MSFT in relaton to the market?

Answer 1

Find below monthly levels of the S&P 500 Index and monthly Microsoft stock prices for the last five years. Any dividends paid are acoounted for in the closing monthly indices and prices. Follow the instructions below to make estimations and to answer all questions.
	Date	Adj Close S&P 500 Index	Adj Close MSFT Stock Price	Monthly Return S&P 500	Monthly Return MSFT
	04-01-2013	1597.57	29.09	2.08%	5.43%
	05-01-2013	1630.74	30.67	-1.50%	-0.36%
	06-01-2013	1606.28	30.56	4.95%	-7.82%
	07-01-2013	1685.73	28.17	-3.13%	4.93%
	08-01-2013	1632.97	29.56	2.97%	0.34%
	09-01-2013	1681.55	29.66	4.46%	6.37%
	10-01-2013	1756.54	31.55	2.80%	7.70%
	11-01-2013	1805.81	33.98	2.36%	-1.15%
	12-01-2013	1848.36	33.59	-3.56%	1.16%
	01-01-2014	1782.59	33.98	4.31%	1.24%
	02-01-2014	1859.45	34.4	0.69%	7.79%
	03-01-2014	1872.34	37.08	0.62%	-1.43%
	04-01-2014	1883.95	36.55	2.10%	1.34%
	05-01-2014	1923.57	37.04	1.91%	2.56%
	06-01-2014	1960.23	37.99	-1.51%	3.50%
	07-01-2014	1930.67	39.32	3.77%	5.26%
	08-01-2014	2003.37	41.39	-1.55%	2.68%
	09-01-2014	1972.29	42.5	2.32%	1.27%
	10-01-2014	2018.05	43.04	2.45%	1.84%
	11-01-2014	2067.56	43.83	-0.42%	-2.24%
	12-01-2014	2058.9	42.85	-3.10%	-13.02%
	01-01-2015	1994.99	37.27	5.49%	8.53%
	02-01-2015	2104.5	40.45	-1.74%	-6.63%
	03-01-2015	2067.89	37.77	0.85%	19.65%
	04-01-2015	2085.51	45.19	1.05%	-3.67%
	05-01-2015	2107.39	43.53	-2.10%	-5.17%
	06-01-2015	2063.11	41.28	1.97%	5.79%
	07-01-2015	2103.84	43.67	-6.26%	-6.82%
	08-01-2015	1972.18	40.69	-2.64%	2.38%
	09-01-2015	1920.03	41.66	8.30%	18.94%
	10-01-2015	2079.36	49.55	0.05%	3.25%
	11-01-2015	2080.41	51.16	-1.75%	2.76%
	12-01-2015	2043.94	52.57	-5.07%	-0.70%
	01-01-2016	1940.24	52.2	-0.41%	-7.64%
	02-01-2016	1932.23	48.21	6.60%	9.33%
	03-01-2016	2059.74	52.71	0.27%	-9.69%
	04-01-2016	2065.3	47.6	1.53%	6.26%
	05-01-2016	2096.95	50.58	0.09%	-2.77%
	06-01-2016	2098.86	49.18	3.56%	10.76%
	07-01-2016	2173.6	54.47	-0.12%	1.38%
	08-01-2016	2170.95	55.22	-0.12%	0.87%
	09-01-2016	2168.27	55.7	-1.94%	4.04%
	10-01-2016	2126.15	57.95	3.42%	0.55%
	11-01-2016	2198.81	58.27	1.82%	3.83%
	12-01-2016	2238.83	60.5	1.79%	4.03%
	01-01-2017	2278.87	62.94	3.72%	-1.03%
	02-01-2017	2363.64	62.29	-0.04%	3.56%
	03-01-2017	2362.72	64.51	0.91%	3.95%
	04-01-2017	2384.2	67.06	1.16%	2.01%
	05-01-2017	2411.8	68.41	0.48%	-0.75%
	06-01-2017	2423.41	67.9	1.93%	5.48%
	07-01-2017	2470.3	71.62	0.05%	2.85%
	08-01-2017	2471.65	73.66	1.93%	0.15%
	09-01-2017	2519.36	73.77	2.22%	11.67%
	10-01-2017	2575.26	82.38	2.81%	1.19%
	11-01-2017	2647.58	83.36	0.98%	2.14%
	12-01-2017	2673.61	85.14	5.62%	11.08%
	01-01-2018	2823.81	94.57	-3.89%	-1.31%
	02-01-2018	2713.83	93.33	-2.69%	-2.21%
	03-01-2018	2640.87	91.27	-2.23%	-1.71%
	04-01-2018	2581.88	89.71
			Average	0.8434%	2.0621%
			Standard deviation	2.8429%	5.8692%
			CV	3.37082896	2.846218
			Slope Beta	1.02911694