Question

1.Measuring Systematic Risk: Beta Coefficients

The management of a publicly traded firm is interested in determining the firm’s cost of equity capital using the security market line (SML) version of the capital asset pricing model (CAPM). Management has measured the weekly returns for the market (S&P 500), its own stock, and the risk-free rate. The returns were annualized. The annualized percentage returns for each of the last 20 weeks are provided.

1a.       See data in Excel file provided with this assignment. Using Excel, determine the excess rate of return on the firm’s stock (firm return less risk-free return) and the excess rate of return on the market (market return less the risk-free return). Put the two new variables (the excess return on the firm and the excess return on the market) that you have created into separate columns. (5 points)

1b.       Determine the alpha and beta coefficients for this stock by running a simple linear regression. Use the file from part (1a) and regress the excess rate of return for the firm against the excess rate of return for the market. The “excess rate of return for the firm” data is the Input Y Range (dependent variable) and the “excess rate of return for the market” is the Input X Range (In Excel, the Data Analysis menu is under Tools (older version of Excel) or Data (newer version)). If you include the row with the variable name in your Input Y Range and your Input X Range, check the box LABELS, and Excel will automatically name your variables in the Excel output. Hint: the alpha coefficient estimate is the estimated intercept coefficient. The beta coefficient is the estimated coefficient for the independent or X variable, the excess rate of return for the market. (10 points)

1c.       Assuming that the market return for the coming year is expected to be 12 percent and that the risk-free rate is expected to be 8 percent, use market model (your regression model) to estimate the expected rate of return to the firm’s shareholders for the coming year. (Hint: You will first need to calculate the expected excess rate of return for the market. To calculate the expected excess rate of return to the firm’s shareholders, you will then plug into the regression model estimated in part (1b). You will also use the coefficient estimates estimated in part (1b).) (10 points)

	Rate of Return
Week	Market	Firm	Risk-Free
1.00	18.50	17.87	6.20
2.00	12.40	8.57	6.70
3.00	3.30	2.66	6.50
4.00	-10.30	-8.33	6.50
5.00	29.00	38.77	6.60
6.00	15.10	22.51	6.70
7.00	20.40	31.60	6.80
8.00	15.40	39.34	6.70
9.00	9.20	28.38	6.70
10.00	3.00	3.73	6.60
11.00	21.90	30.38	7.10
12.00	8.80	10.53	7.30
13.00	0.80	-7.28	7.20
14.00	12.80	18.09	7.30
15.00	-7.60	-17.66	7.40
16.00	16.70	21.28	7.20
17.00	18.30	16.77	7.10
18.00	10.30	-2.69	7.10
19.00	-1.50	-15.87	7.00
20.00	16.40	14.90	7.00

Answer 1

a) Excess Rate of Return

	Rate of Return			Excess Rate of Return
Week	Market	Firm	Risk Free	Market (X)	Firm (Y)
	(A)	(B)	(C)	(A-C)	(B-C)
1	18.50	17.87	6.20	12.30	11.67
2	12.40	8.57	6.70	5.70	1.87
3	3.30	2.66	6.50	-3.20	-3.84
4	-10.30	-8.33	6.50	-16.80	-14.83
5	29.00	38.77	6.60	22.40	32.17
6	15.10	22.51	6.70	8.40	15.81
7	20.40	31.60	6.80	13.60	24.80
8	15.40	39.34	6.70	8.70	32.64
9	9.20	28.38	6.70	2.50	21.68
10	3.00	3.73	6.60	-3.60	-2.87
11	21.90	30.38	7.10	14.80	23.28
12	8.80	10.53	7.30	1.50	3.23
13	0.80	-7.28	7.20	-6.40	-14.48
14	12.80	18.09	7.30	5.50	10.79
15	-7.60	-17.66	7.40	-15.00	-25.06
16	16.70	21.28	7.20	9.50	14.08
17	18.30	16.77	7.10	11.20	9.67
18	10.30	-2.69	7.10	3.20	-9.79
19	-1.50	-15.87	7.00	-8.50	-22.87
20	16.40	14.90	7.00	9.40	7.90

b) Regression Analysis of Excess Rate of Return

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	0.246353	2.09829	0.117407	0.907838	-4.16199	4.654696
X Variable 1	1.475039	0.198603	7.427059	6.95E-07	1.057789	1.892289

      Alpha = 0.2463

      Beta = 1.4750

Regression Equation = 0.2463 + 1.4750(X)

c) Expected Rate of Return of Firm for Coming Year

Expected Excess rate of Return for Market (X) = 12 – 8 = 4

Expected Rate of return for Firm = 0.2463 + 1.4750 (4) = 6.1463 or 6.15%

PLEASE, Rate the solution if it's helpful to you ..