Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Project: constant-growth DDM to value a stock

1. Follow the procedures in the EXCEL file “Ch8.1 Example of Dividend Growth Model.xlsx” to value a stock using the constant-growth Dividend Discount Model. Go to http://finance.yahoo.com. Pick a stock ticker, starting with the first letter of your last name. For example, if your last name is Yang, pick a stock ticker starting with “Y” such as YUM (Yum! Brands, Inc.). If you couldn’t find a “Y” stock with dividends, you are allowed to try the neighboring letters “X” or “Z”. For example, you can pick  XOM (Exxon Mobil Corporation) or ZM (Zoom Vodeo Communications, Inc.). Note that you do not follow the above rule and randomly select a stock, your final score could be zero.

2. Download dividends from FinanceYahoo! (Historical Data panel: monthly frequency, dividends only , max time period). Make sure that your sample size is large enough, e.g., 30 years from 1992 to 2022 (20 years at minimum). You need to sum up quartly/semiannual dividends to annual dividends.

3. Once you download the historical dividends, plot the time trend of dividends. Figure out a way to predict future dividend growth rate, g (either by averaging the historical growth rate, or by linearly extrapolating the most recent trend). Make sure that g is small (e.g., <5%), otherwise your DDM model cannot be used. Note that R>g is the assumption for the constant-growth DDM, where R is the required return and g is dividend growth rate. Predict the dividend (D₁) next period in 2023 by linear extrapolation.

4. From FinanceYahoo!, download monthly stock prices and S&P 500 market prices (ticker: ^GSPC).

5. 

6. Go to https://fred.stlouisfed.org/series/TB3MS to download a 3-month T-bill rate as your risk-free rate,  . Note that the T-bill rate is in percents, and you need to divide it by 100 to get the raw return.

7. If you plan to calculate annual returns, you can use December 01 adjusted close prices to calculate annual returns for your stock, . Similarly, you can calculate the annual return of S&P 500, . Note that you need to use the column of “Adj Close” (Closing prices adjusted for stock splits). Extract year-end adjusted close prices (e.g. Dec  01 prices) to calculate annual return.  For example,

2018 annual return  = 201812price/201712price – 1,

2017 annul return = 201712price/201612price – 1.

8. Calculate the excess return of your stock over T-bill rate,  ; Calculate the excess return of S&P 500 over T-bill rate, ; Based on the CAPM (Capital Asset Pricing Model), you can run a linear regression of the stock excess return on the market excess return to estimate the beta,  .

 Where Y=, X=. You are running an OLS linear regression of Y on X. The file “Multivariate Regression Example.docx” gives an example of multivariate regression of Y on X1 and X2. You can follow its procedures but adjust to univariate regression of Y on X.

Note that the linear regression module is in the Data Panel

9. After you obtain beta, you can estimate the required rate of return for your stock as follows,

where you can estimate expected values of T-bill and SP500 returns based on their historical averages,

10. Use the constant-growth DDM to estimate model stock price:

If you get a negative PV, it is wrong. This constant-growth DDM has an assumption of R>g, otherwise the PV should go to positive infinity. Go back to Step 1 to pick another stock with lower dividend growth rate.

11. Compare the model price with the current market price. Answer the following two questions:

a. Is this stock undervalued or overvalued?

b. Should you buy or short sell the stock?

Different from the EXCEL file “Ch8.1 Example of Dividend Growth Model.xlsx”, you need to write down appropriate equations in the Excel, clearly explain relevant finance concepts, and answer the above two questions.

Print out the Excel file in 1-2 pages and hand it in the class. Don’t email your Excel file to me. Submit your Excel file through Assignment in Canvas, in case that I need to double check some numbers.