Question

SOLUTION IS NEEDED IN EXCEL FORMAT ONLY PLEASE. (EXCEL FORMULA ONLY)

Example 5: Nonconstant Growth

Suppose a stock will pay the following dividends and has the following required return:

Year 1:	$               1.00
Year 2:	$               2.00
Year 3:	$               2.50
Required return:	10.0%

After the third year, the dividends will grow at:

5.0%

What is the price of the stock? First, we need to find the price of the stock when it begins a constant growth rate, which is in Year 3. The price of the stock in Year 3 will be:

The price today is the present value of the future dividends, plus the present value of the future price, so:

Price today:

Answer 1

P3 = [D3(1 + g)] / [r - g]

= [$2.50 x 1.05] / [0.10 - 0.05] = $2.625 / 0.05 = $52.50

P0 = [D1 / (1 + r)] + [D2 / (1 + r)²] + [(D3 + P3) / (1 + r)³]

= [$1 / 1.10] + [$2 / 1.10²] + [($2.50 + $52.50) / 1.10³]

= $0.91 + $1.65 + $41.32 = $43.88