Question

35. Nonconstant Growth Storico Co. just paid a dividend of $3.35 per share. The company will increase its dividend by 16 percent next year and will then reduce its dividend growth rate by 4 percentage points per year until it reaches the industry average of 4 percent dividend growth, after which the company will keep a constant growth rate forever. If the required return on the company’s stock is 10.5 percent, what will a share of stock sell for today?

I am trying to set up a table on MS Excel to use as a calculator for this problem. I would prefer each individual calculation within the equation explained to me with the proper cells. Thank you!

Answer 1

Share price today = present value of all future dividend payouts. Note that here D0 = 3.35.

Now growth from D0 to D1 = 16%. Growth from D1 to D2 = 16-4 = 12%. Growth from D2 to D3 = 12%-4% = 8%. Growth from D3 to D4 = 8% - 4% = 4%. This will now remain constant and growth will continue at 4% forever now.

Now price = present value of D1 to D4 + present value of P4 (i.e. price at the end of year 4).

Present value of D1 to D4 = 13.84396 (calculation shown in excel below)

P4 = D5/r-g = 5.0840669/(10.5%-4%) = 78.2164132

Present value of P4 = 78.2164132/PVIF for year 4 = 78.2164132*0.67073 = 52.4624761

Total price of share today = 13.84396 + 52.462761

= $66.3064399. This can be rounded to $66.31

Thus price today = $66.31

EXCEL CALCULATOR:

A		B		C	D = 1/C^A	E = B*D
Year		Amount in $	year on year growth %	1+r	PVIF = 1/(1+r)^n	PV = PVIF*amount
-	D0	3.3500000		1.105	1.00000
1	D1	3.8860000	16%		0.90498	3.5167421
2	D2	4.3523200	12% (16-4)		0.81898	3.5644807
3	D3	4.7005056	8%		0.74116	3.4838363
4	D4	4.8885258	4%		0.67073	3.2789048
5	D5	5.0840669	4%
					Total (D1 to D4)	13.8439638
	P4	78.2164132				52.4624761
					Total Present value of D1 to D4 and P4	66.30643989

Image of excel:

Image of excel in which formulas can be seen: