Question

Q6: Calculation of ABC stock's beta. Below are 16 monthly ·percentage returns of ABC stock and of the index.

Calculation of Beta	Column1	Column2
Month	ABC stock return	Index return
1	-13.38	2.52
2	16.79	5.45
3	-1.67	0.76
4	-3.46	2.36
5	10.22	8.56
6	7.13	8.67
7	6.71	10.8
8	7.84	3.33
9	2.15	-5.07
10	7.95	7.1
11	-8.05	-11.57
12	7.68	4.65
13	4.75	14.59
14	7.55	2.66
15	-2.36	3.81
16	4.98	7.99

a. Calculate the sample variances and sample standard deviations of the stock and of the index.
b. Calculate the sample covariance of ABC stock returns and the .index returns.
c. What is the sensitivity of the ABC stock to the index return, or in other words what is the beta of ABC stock?
d. You are considering a risky investment project whose risk profile is similar to the risk of ABC stock If the risk free rate is 2% and risk premium is 6%, what is the discount rate to use for this investment project?
e. Calculate the residual return of ABC stock by using the following equation: e(i)=ABC return(i)-beta*index return(i), i=1,2, ... 16. You should use the beta value in the answer to the question (c). What is the covariance· of the residual return of ABC stock and the index return?
f. Calculate the R"2 of the regression. What is the coefficient of correlation between ABC stock and the index?

Answer 1

	Month	ABC stock return	Index return	Residual return
	1	-13.38%	2.52%	-14.98%
	2	16.79%	5.45%	13.33%
	3	-1.67%	0.76%	-2.15%
	4	-3.46%	2.36%	-4.96%
	5	10.22%	8.56%	4.78%
	6	7.13%	8.67%	1.62%
	7	6.71%	10.80%	-0.15%
	8	7.84%	3.33%	5.73%
	9	2.15%	-5.07%	5.37%
	10	7.95%	7.10%	3.44%
	11	-8.05%	-11.57%	-0.70%
	12	7.68%	4.65%	4.73%
	13	4.75%	14.59%	-4.51%
	14	7.55%	2.66%	5.86%
	15	-2.36%	3.81%	-4.78%
	16	4.98%	7.99%	-0.09%
a.	Sample Variance	0.57%	0.38%
	Standard deviation	7.53%	6.17%
b.	Covariance (Stock, Index)	0.23%
	Correlation (Stock, Index)	0.5201
c.	Beta	=Correl(stock,index)*Stock stdev / mkt. stdev
	Beta	0.6350
d.	Discount rate of risky project	=risk free rate + Beta * risk premium
		=2% + 0.6350*6%
	Discount rate of risky project	5.81%
e.	Covariance of residual return of ABC stock and index return	-1.83637E-18

Excel formula: