In practice, a common way to value a share of stock when a company pays dividends is to value the dividends over the next five years or so, then find the “terminal” stock price using a benchmark PE ratio. Suppose a company just paid a dividend of $1.15. The dividends are expected to grow at 20 percent over the next five years. In five years, the estimated payout ratio is 40 percent and the benchmark PE ratio is 21. What is the target stock price in five years? What is the stock price today assuming a required return of 12 percent on this stock?
Please show me how to find the solution using Microsoft Excel. I need to know how the formula/inputs used in the calculations.