Question

The attached Excel sheet contains data on annual returns on IBM and 3M stocks over the period 1990-2002, as well as annual returns on S&P 500 Index and one-year U.S. Treasury bills for this period. You are asked to evaluate performance of IBM and 3M stocks using the CAPM’s Security Market Line with S&P 500 Index as proxy for the market return.

(i) Compute the annual excess returns of IBM, 3M, and the S&P 500.

(ii) Use Excel to compute regression coefficients of IBM excess returns on S&P 500 excess returns. More precisely, use Excel functions SLOPE and INTERCEPT to compute the slope and the intercept of the regression. The slope is the estimate of IBM’s beta. If the intercept is positive, IBM stock has “overper- formed”. Otherwise, it has “underperformed”. Which way is it? Use RSQ

function to find the regression’s R2.

(iii) Do the same calculations for 3M stock as for IBM in part (ii). How has 3M

stock performed over the time period 1990-2002?

Date	S&P 500	IBM	3M	1-year T-bill
Dec-90	-3.1	17.46	11.3	7.89
Dec-91	30.47	-23.87	14.04	5.86
Dec-92	7.62	-56.85	8.81	3.89
Dec-93	10.08	11.4	10.85	3.43
Dec-94	1.32	26.33	1.49	5.32
Dec-95	37.58	21.75	25.03	5.94
Dec-96	22.96	50.59	25.04	5.52
Dec-97	33.36	32.28	1.2	5.63
Dec-98	28.58	56.67	-11.7	5.05
Dec-99	21.04	15.71	34.43	5.08
Dec-00	-9.1	-23.83	20.79	6.11
Dec-01	-11.89	35.28	-1.92	3.49
Dec-02	-22.1	-44.11	4.22	2
					Annual returns on S&P 500 Index, IBM stock, 3M stock, and 1-year US T-bill rates. S&P 500 is a proxy for market return; T-bill rates are for risk-free rates in respective years.

Answer 1

Solution:

a. The annual excess return of IBM, 3M and S& P 500 is the respective return less 1-year T-bill.

Date	S&P 500	IBM	3M	1-year T-bill	R S&P 500	R (IBM)	R (3M)
Dec-90	-3.1	17.46	11.3	7.89	-10.99	9.57	3.41
Dec-91	30.47	-23.87	14.04	5.86	24.61	-29.73	8.18
Dec-92	7.62	-56.85	8.81	3.89	3.73	-60.74	4.92
Dec-93	10.08	11.4	10.85	3.43	6.65	7.97	7.42
Dec-94	1.32	26.33	1.49	5.32	-4	21.01	-3.83
Dec-95	37.58	21.75	25.03	5.94	31.64	15.81	19.09
Dec-96	22.96	50.59	25.04	5.52	17.44	45.07	19.52
Dec-97	33.36	32.28	1.2	5.63	27.73	26.65	-4.43
Dec-98	28.58	56.67	-11.7	5.05	23.53	51.62	-16.75
Dec-99	21.04	15.71	34.43	5.08	15.96	10.63	29.35
Dec-00	-9.1	-23.83	20.79	6.11	-15.21	-29.94	14.68
1-Dec	-11.89	35.28	-1.92	3.49	-15.38	31.79	-5.41
2-Dec	-22.1	-44.11	4.22	2	-24.1	-46.11	2.22

b.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.387397
R Square	0.150077
Adjusted R Square	0.072811
Standard Error	33.76013
Observations	13
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2213.782	2213.782	1.942346	0.190931
Residual	11	12537.21	1139.747
Total	12	14750.99
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-0.42404	9.915533	-0.04276	0.966655	-22.248	21.3999
X Variable 1	0.724329	0.519724	1.393681	0.190931	-0.41958	1.868233

Slope = 0.724329, Intercept = -0.42404

Since the slope is 0.724329 which is positive, therefore it has over-performed.

R-square is 0.150077.

iii.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.163332
R Square	0.026677
Adjusted R Square	-0.06181
Standard Error	12.8178
Observations	13
ANOVA
	df	SS	MS	F	Significance F
Regression	1	49.5344	49.5344	0.301495	0.593917
Residual	11	1807.255	164.2959
Total	12	1856.789
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	5.348285	3.764656	1.420657	0.183137	-2.93767	13.63424
X Variable 1	0.108348	0.197325	0.549086	0.593917	-0.32596	0.542657

Since the slope is 0.108348 is positive, the stock has overperformed.