In this assignment, you will choose a company of interest (FORD MOTOR CO) and evaluate its stock’s fundamental value. You will practice how to 1) Use the beta of the stock, market return, and risk-free rate to calculate the required rate of return, 2) evaluate the stock using constant growth dividend discount model. Assignments should be submitted in the format of excel files.

Data: 1) the company’s dividend history from finance.yahoo.com or the company’s own website.

2) the company’s market risk exposure measured by “**Beta (5Y Monthly)**” from the company’s summary page on finance.yahoo.com

3) choose an **estimation period** for the required return R, at least going back a 5-year period.

4) risk-free rates (annual rates) for the estimation period; the rates are available monthly at https://fred.stlouisfed.org/series/TB3MS

5) monthly market index (use S&P 500) “**Adj Close”** prices for the estimation period https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

Part I. calculate required return R

1) Calculate risk-free rate using average of the annual 3-Month Treasury Bill interest rates over the estimation period.

2) Calculate the monthly market return = (Adj close at the end of month – Adj close at the end of pervious month)/Adj close at the end of previous month

3) Calculate annualized market return = average monthly return * 12

4) Use the formula to calculate required return R = risk-free rate + beta*(annualized market return – risk-free rate).

Part II.

1) Estimate the growth rate of dividends from dividend history data. There is no specific formula for estimating g using historical data. You can use the most recent growth rate, the average growth rate for the past 5-years, or your own model.

2) Use the Constant Growth Model in the (a case of the Dividend Discount Models): Intrinsic Value = D0(1+g)/(R-g)

where D0 is the current **annual** dividend, and R is the required return we estimated in part I.

Compare your calculation of intrinsic value to the market price, what are your observations and conclusions?