Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

please solve it by excel Excel Master It! Problem In practice, the use of the dividend discount model is refined from the method we presented

please solve it by excel
image text in transcribed
image text in transcribed
Excel Master It! Problem In practice, the use of the dividend discount model is refined from the method we presented in the textbook. Many analysts will estimate the dividend for the next five years and then estimate a perpetual growth rate at some point in the fature, typically 10 years. Rather than have the dividend growth fall dramatically from the fast growth period to the perpetual growth period, linear interpolation is applied. That is, the dividend growth is projected to fall by an equal amount each year. For example, if the high-growth period is 15 percent for the next five years and the dividends are expected to fall to a 4 pereent perpetual growth fate five years later. the dividend growth fate would decline by 2 percent each year. Assume the required return is 11 percent. The Value Line Inwesment Sumey provides information for investons. Below, you will find information for AbbVie from the 2020 edition of Value Line: a. Asume that a perpetual growth tate of 5 percent begins II years from now and use linear interpolation between the high growth rate and perpetual growth rate. Construct a table that shows the dividend grown rate and dividend each year. What is the stock price at Year io? What is the stock price today? b. How sensitive is the current stock price to changes in the perpetual frowth rate? Graph the current stock price against the perpetual growth rate in 11 years to find out. Instead of applying the constant dividend growth model to find the stock price in the fature, analysts often will combine the dividend discount method with mutiples valuation, often with the PE ratio. Remenber that the PE ratio is the price per share divided by the earaings per share. So, if we know what the PE ratio is, we can solve for the stock price. Suppose we also hawe the following information about AbbVie: Payout ratio PE ratio at constant growh rate 15 c. Use the PE ratio to calculate the stock price when AbbVie reaches a perpetual growth rate in dividends. Now find the value of the stock today by finding the present value of the dividends during the supernormal growth rate and the price you calculated using the PE ratio. d. How sensitive is the current stock price to changes in PE ratio when the stock reaches the perpetual growth rate? Graph the current stock price against the PE ratio in 11 years to find out. Excel Master It! Problem In practice, the use of the dividend discount model is refined from the method we presented in the textbook. Many analysts will estimate the dividend for the next five years and then estimate a perpetual growth rate at some point in the fature, typically 10 years. Rather than have the dividend growth fall dramatically from the fast growth period to the perpetual growth period, linear interpolation is applied. That is, the dividend growth is projected to fall by an equal amount each year. For example, if the high-growth period is 15 percent for the next five years and the dividends are expected to fall to a 4 pereent perpetual growth fate five years later. the dividend growth fate would decline by 2 percent each year. Assume the required return is 11 percent. The Value Line Inwesment Sumey provides information for investons. Below, you will find information for AbbVie from the 2020 edition of Value Line: a. Asume that a perpetual growth tate of 5 percent begins II years from now and use linear interpolation between the high growth rate and perpetual growth rate. Construct a table that shows the dividend grown rate and dividend each year. What is the stock price at Year io? What is the stock price today? b. How sensitive is the current stock price to changes in the perpetual frowth rate? Graph the current stock price against the perpetual growth rate in 11 years to find out. Instead of applying the constant dividend growth model to find the stock price in the fature, analysts often will combine the dividend discount method with mutiples valuation, often with the PE ratio. Remenber that the PE ratio is the price per share divided by the earaings per share. So, if we know what the PE ratio is, we can solve for the stock price. Suppose we also hawe the following information about AbbVie: Payout ratio PE ratio at constant growh rate 15 c. Use the PE ratio to calculate the stock price when AbbVie reaches a perpetual growth rate in dividends. Now find the value of the stock today by finding the present value of the dividends during the supernormal growth rate and the price you calculated using the PE ratio. d. How sensitive is the current stock price to changes in PE ratio when the stock reaches the perpetual growth rate? Graph the current stock price against the PE ratio in 11 years to find out

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Technical Analysis Of Stock Trends

Authors: Robert D. Edwards, John Magee

1st Edition

1607962233, 978-1607962236

More Books

Students also viewed these Finance questions