You can download both at Yahoofinance.com. When you pull up either one, click on Historical Data and set the parameters to monthly and go back as far as you can/want. (Set the dates)

TOTAL RISK

Convert your stock price and index data to return series. Ignore dividends for this project. You will be calculating monthly returns. Calculate the mean, standard deviation and coefficient of variation using the functions in EXCEL. This is historical data so the EXCEL functions are appropriate.

MARKET RISK

1. Calculate your companys beta by regressing your stocks returns on the index returns using the LINEST function in EXCEL or the Data Analysis package.
2. Look up your companys beta. It is available on all of the Finance web sites. Be sure and document where you picked up your beta.

ESTIMATING THE COST OF EQUITY

1. CAPM

Estimate the parameters of the Capital Asset Pricing Model. Find a current estimate of the risk-free rate (use the 10 year T-bond rate). Estimate the market risk premium. There is a good source for the market risk premium at Ken Frenchs web site. He is a finance professor at Dartmouth and he maintains a database for all to use. His web address is in the text on p. 295 in the margin. The data is listed under Data Library, Benchmarks Annual returns. Calculate the long run average market risk premium from the data given. Combine your estimates of beta, the risk-free rate and the market risk premium to estimate the cost of equity for your company using the CAPM. You determine, document and justify the time frame you chose for estimating the Market Risk Premium

2.DISCOUNTED CASH FLOW MODEL

Estimate the expected return on equity using the DCF model: r_S = D_1/P₀ + g.

For your estimate of the growth rate use the analysts forecast of earnings growth that can be found at Yahoofinance.com. Alternatively you can also calculate growth with the formula:

g= b(ROE) where b is the earnings retention ratio and ROE is the Return on Equity.

3. BOND YIELD PLUS RISK PREMIUM

Estimate r_S using the bond yield plus risk premium approach. You should be able to find the companys cost of debt in the footnotes to the financial statements. The current equity risk premium is 6%.[1]

4.Calculate the WACC. Use one of your equity estimates from above and choose a r_d from the footnotes in the financial statements.

ANALYSIS

You are to write a summary analysis of your estimations. Document everything you do tell me where you got all of your data and any assumptions you needed to make in coming up with your estimates. Answer the following questions in your analysis:

-What time period does your data cover? If you downloaded the data to compute the returns why did you choose the time period you did?

- How does your computed beta compare with the published beta? Why do you think they differ? Which do you think is a better estimate of the market risk of your stock?

- How does the beta compare to the total risk measures you calculated standard deviation and CV? What is each statistic measuring? Does your company appear to have a lot of company specific risk or a little? Is it possible for a company to have lots of company specific risk and little market risk? Explain.

How do your three estimates of r_S compare? Which do you feel is the best estimate of the cost of equity? Do you think your firms recent performance is a good indicator of expected future returns?

Explain your WACC calculation. Justify and explain how you decided on an estimate of r_d.

Graph your stocks price performance against the market index.

Turn in a copy of your EXCEL spreadsheet with all the data and calculations and the graph. Also include a typed analysis addressing each of the questions above