Problem

ABC is a young starup company.

Currently, they do not pay dividends.

The first scheduled dividend will be paid at the end of year 3, with amount of $1.50.

In the next 4 years, the dividend will grow at 20% every year.

After that, it will maintain a sustainable growth rate of 6% forever. The required rate of return is 15%.

John hired you as an excel programmer. He asks you to write only one flexible excel program that has all of the following requirements respect to the change of input data:

a) The amount of stock price must be shown at answer section.

b) Please use IF function to develop a flexible model displaying the stock price and dividend for this model.

*Please show excel formula**

Asgn 5: Stock valuation
Input Section				Output Section
First dividend starts at the end of year	3	years
First scheduled dividend	$ 1.50	per year
G1 rate of growth	20%
G1 was paid for	4	years		Stock price =	?
G2 rate of growth	6%
G2 was paid for	forever
Required rate of return	15%
Answer Section
year	stock price	Dividend Payment