Question

Please show me how to do this by using excel and its functions

Mrs. Dawn Chorus, founder and president of United Bird Seed, is wondering whether the company should make its first public sale of common stock and, if so, at what price.

The company’s financial plan suggests rapid growth over the next three years but only moderate growth afterwards. Forecasted dividends per share are as follows:

Year                1                      2                      3                      4                      5

                       $1.00            $1.20             $1.44           5% growth thereafter

Investors demand a return of 10%.

Calculate the price of United Bird Seed’s stock (today).

Answer 1

		D1	D2	D3	D4
	Year	1	2	3	4	5
		$          1.00	$                1.20	$                1.44	1.512
					5% growth thereafter
	As we know from constant growth formula, P3 = D4/(Ke-g)
					P3 = 1.512/(0.1-0.05)
					P3 = 30.24
	Now the present value of all cash flows will be current price
Year		Cash flows	x DF at 10% =	Present value
1	D1	1	0.90909	$                0.91
2	D2	1.2	0.82645	$                0.99
3	D3	1.44	0.75131	$                1.08
3	P3	30.24	0.75131	$              22.72
		Sum total = Current price		$              25.70	Ans