Problem Set 4: (Each problem is equally-weighted)

Problem 1:

The excel upload file contains monthly stock prices for stock MCD and stock KO

Calculate monthly returns for each stock.

Calculate the average monthly return and standard deviation for each stock and covariance between the two stocks.

Find the minimum variance portfolio weights, where the portfolio consists of MCD stock and KO stock (assume that the portfolio weights cannot be negative or above 100%). Also find the average return and standard deviation for the minimum variance portfolio. Use sample statistics throughout.

Problem 2:

The excel upload file contains monthly returns in excess of the risk-free rate for the S&P 500 (a proxy for the market portfolio) and Schlumberger Limited stock (SLB). The annualized risk-free rate is 2% and market risk premium (market return minus risk-free rate) is 5%. Using these data, do the following:

1.) Run a regression and provide the associated estimate of SLB's market beta. Additionally, provide a 95% confidence interval (range) for the beta parameter.

2.) Now that you have estimated beta (and already have the risk-free rate and market risk premium), use the CAPM to compute an estimate of SLB's expected (annual) stock return.

3.) Report the alpha for Schlumberger. Is the alpha statistically significant? Why? (Use a significance level of 5%, the significance level we used in class)

DATA FOR QUESTION 1

Dates	MCD Prices	KO Prices
5/1/2008	43.431137	18.457529
6/1/2008	41.161476	16.755545
7/1/2008	44.060226	16.821192
8/1/2008	45.68882	17.00737
9/1/2008	45.744053	17.271938
10/1/2008	42.949001	14.597276
11/1/2008	43.556938	15.528234
12/1/2008	46.516739	15.255392
1/1/2009	43.397675	14.396081
2/1/2009	39.081852	13.765911
3/1/2009	41.196571	14.810578
4/1/2009	40.230267	14.817561
5/1/2009	44.533356	16.920586
6/1/2009	43.40097	16.517876
7/1/2009	41.910065	17.45022
8/1/2009	42.808254	17.075586
9/1/2009	43.823372	18.801704
10/1/2009	45.005917	18.972742
11/1/2009	48.568909	20.357176
12/1/2009	48.359615	20.576345
1/1/2010	48.351868	19.583632
2/1/2010	49.451637	19.031313
3/1/2010	52.113655	19.854368
4/1/2010	55.136429	19.613283
5/1/2010	52.230835	18.861036
6/1/2010	51.882023	18.391348
7/1/2010	54.922325	20.567492
8/1/2010	57.545177	20.854862
9/1/2010	59.126778	21.84013
10/1/2010	61.713718	23.234507
11/1/2010	62.134281	23.935482
12/1/2010	61.389038	25.267467
1/1/2011	58.917801	24.145662
2/1/2011	60.525314	24.556738
3/1/2011	61.350777	25.48645
4/1/2011	63.140755	26.295313
5/1/2011	65.745064	26.04195
6/1/2011	68.493454	26.229052
7/1/2011	70.248039	26.896336
8/1/2011	73.440369	27.861305
9/1/2011	71.819061	26.718376
10/1/2011	75.932579	27.39003
11/1/2011	78.116104	26.953045
12/1/2011	82.666878	28.462063
1/1/2012	81.612244	27.469534
2/1/2012	81.801727	28.417318
3/1/2012	81.397202	30.105429
4/1/2012	80.85788	31.503147
5/1/2012	74.128731	30.84684
6/1/2012	74.031746	32.275028
7/1/2012	74.725815	33.810997
8/1/2012	74.834511	31.300282
9/1/2012	77.328201	31.743841
10/1/2012	73.156265	31.327673
11/1/2012	73.358536	31.951187
12/1/2012	75.012215	30.753624
1/1/2013	81.032913	31.593519
2/1/2013	81.551666	32.849121
3/1/2013	85.458481	34.308315
4/1/2013	87.558731	36.171722
5/1/2013	82.783905	34.17215
6/1/2013	85.531921	34.274685
7/1/2013	84.737053	34.485779
8/1/2013	81.52314	32.850964
9/1/2013	83.792976	32.592846
10/1/2013	84.062965	34.294571
11/1/2013	84.803268	34.831913
12/1/2013	85.210014	36.053406
1/1/2014	82.69841	33.007496
2/1/2014	83.559021	33.339138
3/1/2014	86.821579	33.740608
4/1/2014	89.788567	35.881626
5/1/2014	89.83284	35.98719
6/1/2014	89.940903	37.262714
7/1/2014	84.423393	34.822067
8/1/2014	83.673424	36.975735
9/1/2014	85.377228	37.80883
10/1/2014	84.404678	37.387943
11/1/2014	87.178238	40.021534
12/1/2014	85.123581	37.952011
1/1/2015	83.97892	37.008152
2/1/2015	89.847633	38.922829
3/1/2015	89.29055	36.450829
4/1/2015	88.474991	36.762352
5/1/2015	87.906822	37.124897
6/1/2015	87.875854	35.556873
7/1/2015	92.303398	37.540829
8/1/2015	89.964836	35.932461
9/1/2015	91.876404	36.663544
10/1/2015	104.669914	39.036716
11/1/2015	106.450928	39.285595
12/1/2015	111.025902	39.902683
1/1/2016	116.326286	39.865524
2/1/2016	110.133118	40.060585
3/1/2016	119.006653	43.088581
4/1/2016	119.773636	41.936249
5/1/2016	115.578865	41.749027
6/1/2016	113.950188	42.432377
7/1/2016	112.221893	41.154232
8/1/2016	110.323723	40.965584
9/1/2016	110.892723	39.918575
10/1/2016	108.210762	40.320786
11/1/2016	114.651306	38.371307
12/1/2016	117.916306	40.278061
1/1/2017	118.739746	39.865688
2/1/2017	123.661011	40.239693
3/1/2017	126.483925	40.700012
4/1/2017	136.555008	41.746147
5/1/2017	147.250671	43.990669
6/1/2017	149.465912	43.390842
7/1/2017	152.347229	44.7136
8/1/2017	157.090286	44.430725
9/1/2017	154.772339	43.903996
10/1/2017	164.877792	45.207031
11/1/2017	169.876175	45.000561
12/1/2017	171.037949	45.478539
1/1/2018	170.064117	47.173576
2/1/2018	156.748367	42.841816
3/1/2018	156.380005	43.049976
4/1/2018	161.729996	44.509998

DATA FOR QUESTION 2

Month	S&P 500 monthly excess returns	SLB monthly excess returns
1	-2.58%	-11.09%
2	-1.46%	10.25%
3	0.12%	-9.18%
4	8.03%	9.75%
5	5.25%	14.71%
6	0.99%	-1.86%
7	1.70%	-5.47%
8	1.97%	9.66%
9	-1.17%	-2.32%
10	5.14%	-3.09%
11	1.01%	-0.17%
12	4.83%	15.67%
13	1.89%	11.08%
14	1.27%	5.50%
15	-1.42%	-1.08%
16	-1.99%	-8.78%
17	1.61%	-2.12%
18	1.73%	10.43%
19	-3.38%	1.17%
20	0.11%	-3.85%
21	0.87%	8.41%
22	1.13%	-6.84%
23	4.18%	3.99%
24	2.79%	2.12%
25	-2.47%	1.41%
26	1.86%	10.42%
27	-2.07%	-7.05%
28	-2.13%	-3.21%
29	2.94%	-0.30%
30	-0.10%	10.54%
31	3.49%	9.50%
32	-1.23%	2.66%
33	0.52%	-2.21%
34	-2.70%	6.99%
35	3.98%	5.01%
36	-0.52%	1.35%
37	2.03%	26.79%
38	0.19%	-10.44%
39	1.27%	9.21%
40	0.87%	8.46%
41	-3.45%	-5.69%
42	-0.14%	-0.92%
43	0.03%	2.23%
44	1.75%	-9.08%
45	2.26%	0.97%
46	2.69%	1.27%
47	1.56%	7.79%
48	0.93%	-8.30%
49	1.07%	0.12%
50	-2.40%	-1.24%
51	0.74%	9.15%
52	3.93%	6.22%
53	2.94%	4.92%
54	-1.86%	8.53%
55	-3.58%	10.50%
56	0.92%	1.70%
57	3.47%	8.12%
58	1.02%	-8.69%
59	-4.22%	-3.56%
60	-1.38%	5.06%
61	-6.47%	-26.82%
62	-2.80%	13.74%
63	-1.00%	0.54%
64	4.55%	14.36%
65	1.35%	0.43%
66	-8.88%	6.10%
67	-1.04%	-5.71%
68	1.39%	-7.47%
69	-9.99%	-18.87%
70	-18.11%	-41.39%
71	-7.23%	-1.79%
72	0.97%	-17.71%
73	-8.58%	-3.67%
74	-11.39%	-6.50%
75	8.00%	6.49%
76	9.45%	18.73%
77	5.67%	15.52%
78	-0.09%	-5.25%
79	7.19%	-1.15%
80	3.61%	5.28%
81	3.47%	5.86%
82	-1.95%	4.27%
83	5.98%	3.00%
84	1.88%	1.86%
85	-3.71%	-2.54%
86	3.07%	-3.46%
87	5.90%	3.77%
88	1.52%	11.80%
89	-8.29%	-23.71%
90	-5.33%	-1.46%
91	6.60%	7.51%
92	-4.62%	-10.85%
93	8.57%	14.42%
94	3.73%	12.60%
95	-0.01%	10.39%
96	6.46%	7.65%
97	2.29%	6.36%
98	3.40%	5.12%
99	0.01%	-0.17%
100	2.84%	-3.84%
101	-1.13%	-4.30%
102	-1.70%	0.78%
103	-2.02%	4.49%
104	-5.66%	-14.24%
105	-7.20%	-26.85%
106	10.36%	20.70%
107	-0.41%	2.87%
108	1.04%	-9.79%
109	4.53%	9.57%
110	4.24%	3.54%
111	3.16%	-10.43%
112	-0.68%	5.85%
113	-6.20%	-15.49%
114	3.97%	2.58%
115	1.17%	9.33%
116	2.46%	1.92%
117	2.50%	-0.08%
118	-1.84%	-3.96%
119	0.56%	3.36%
120	0.89%	-3.31%