Please show work in EXCEL and individual cell work.

Hamilton Landscaping's dividend growth rate is expected to be 30% in the next year, drop to 15% from Year 1 to Year 2, and drop to a constant 5% after Year 2 and all subsequent years. Hamilton has just paid a dividend of $2.50 and its stock has a required return of 11%.

a. What is Hamilton's estimated stock price today?

D₀

$2.50

r_s

11.0%

g_0,1

30%

Short-run g; for Year 1 only.

g_1,2

15%

Short-run g; for Year 2 only.

g_L

5%

Long-run g; for Year 3 and all following years.

g

30%

15%

5%

5%

Year

0

1

2

3

Dividend

PV of dividends and PV of horizon value

= D₃

= Horizon value = P₂ =

= P₀

a. What is Hamilton's estimated stock price for Year 1?

P₁

=

P₂

+

D₂

(1 + r_s)

P₁

=

+

P₁

=

b. If you bought the stock at Year 0, what's your expected dividend yield and capital gains for the upcoming year?

1. Find the expected dividend yield.

Dividend yield =

2. Find the expected capital gains yield.

Use the estimated price for Year 1, P₁, to find the expected gain.

Cap. Gain yield=