The management of a publicly traded firm is interested in determining the firms 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.

See data in Excel file provided with this assignment. Using Excel, determine the excess rate of return on the firms 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 firms 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 firms 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