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?