Pick a company of your interest that has been traded for at least 5 years with dividend payment history and use the Gordon Growth Model (page144) to conduct its stock valuation research. The research includes a BUY or SELL or HOLD recommendation. Please make sure to use both qualitative and quantitative support to make your recommendation and present detailed calculations in your report ( Please submit both a word file and an Excel file which shows the calculation details):

Gordon Growth Model: PV of stock = D1 / (K - g)

Where D1 is the expected dividend next year, g is the dividend growth rate, and K is the required rate of return by investors, which can be calculated using CAPM.

1. Determine g, the dividend growth rate of the company.

Research the dividend history of the company, and calculate the average annual growth rate of dividend. Please show the sources of data and your calculation in your report.

2. Determine D1, the expected dividend payment next year.

If only D0 is available, use D1= D0 (1 +g) to solve for D1, where D0 is the most recent annual dividend payment,

3. Determine K, the required rate of return from investing in the stock.

According to the CAPM, the required rate of return of investing in stock K = Rf + B (Rm - Rf)

Where Rf is the risk-free rate of return. (The three-month T-bill annualized return is a good proxy for Rf) . B is the annual beta (not daily or monthly beta ) of the company. Rm is the overall stock market annual return. ( S&P500 annual return can be used as a proxy for Rm).

Please search for the most current data for the variables involved in CAMP formulas, including Rf, B, and Rm. Show the data source and calculation in the report.

4. Solve for the value of the stock using the Gordon Growth Model.

5. What are the assumptions of the Gordon Growth Model? Do you think your valuation is realistic for the company, why or why not?

6. Compare your estimated values from the above method to the current stock price. Does the market currently overvalue or undervalue the stock? What is your recommended transaction in this stock? (BUY, SELL, or hold) why the recommendation?

Message me for the excel demo, it didn't let me add to the question.