80.2K

Verified Solution

Link Copied!

Non-Constant Growth Rate Dividend Valuation Instructions: Calculate the value of a share of XYZ common stock utilizing the concept of discounted cash flow valuation. 1. The forecast for the company's common stock dividend for 2019 through 2023 is: Dividend Forecast Given: Steps: Year 2019 2020 2021 2022 2023 4.00 4.80 5.72 6.90 8.25 2. From the years 2024 through 2028 dividend growth is expected to slow under the following schedule: Dividend Growth Year 2024 2025 2026 2027 2028 15% 13% 11% 9% 7% 3. From the year 2029 and beyond the dividend is expected to grow at a constant rate of 7% 4. You also have made the following assumptions: Required Return 10.50% 1. Populate Constant Growth Rate and Required Return in cell B4 and B5 2. Populate cells E2 through E6 with the forecasted dividend amounts 3. Populate cells F7 through F11 with the given dividend growth rates for those years. 4. Using an equation referencing the previous years dividend and the given dividend growth rate for that year, calculate the dividend amount for each cell from E7 through E11. An equation must be used in these cells. 5. Populate cell F12 by referencing the constant growth rate figure in the Inputs box of the worksheet. 6. In cell G12 calculate the Terminal Value of all future dividends using the Constant Growth Rate Dividend Model. 7. Populate cells H2 through H12 with the total cash flow expected from this share of common stock. Use cell references and equations where appropriate. 8. In cell H14 calculate the Present Value of all Future Expected Dividends using the NPV formula. You will be referencing the predicted cash flows and the RROR

Non-Constant Growth Rate Dividend Valuation Instructions: Calculate the value of a share of XYZ common stock utilizing the concept of discounted cash flow valuation. Given: 1. The forecast for the company's common stock dividend for 2019 through 2023 is: 2. From the years 2024 through 2028 dividend growth is expected to slow under the following schedule: 3. From the year 2029 and beyond the dividend is expected to grow at a constant rate of 7% 4. You also have made the following assumptions: Required Return 10.50K Steps: 1. Populate Constant Growth Rate and Required Return in cell B4 and BS 2. Populate celis E2 through E with the forecasted dividend amounts 3. Populate cells F7 through F11 with the given dividend growth rates for those years. 4. Using an equation referencing the previous years dividend and the given dividend growth rate for that year, 5. Populate cell F 12 by referencing the constant growth rate figure in the inputs box of the worksheet. 6. In cell 612 caiculate the Terminal Value of all future dividends using the Constant Growth Rate Dividend Model. 7. Populate cells H2 through H12 with the total cash flow expected from this share of common stock. Use cell referer 8. In cet H14 calculate the Present Value of all Future Expected Dividends usine the NPV formula. You will be referencing the predicted cash flows and the AROR

Answer & Explanation Solved by verified expert

See Answer