Question

To please be done in excel: XYZ Ltd is a typical young company. It has not paid any dividends to date but the company’s earnings per share (EPS) have been growing at an average annual growth rate of 20% p.a. for the last five years. The company has just announced that it will declare the following dividends in the next 3 years: $10, $12 and $13.50 per share starting in exactly one year’s time. The required rate of return for Alpha is 22%. a. Show that the fair price for the share is $469.77 if the long run average growth rate in dividends is assumed to be 20%.

Answer 1

Hello student! As attaching an excel file is not possible, so I'm going to attach the screenshot of my spreadsheet and explain how I calculated each cell and which command I used.

• Information in Blue is already provided in the question.

• Calculation of Dividend of Year 4 - as Long-term growth rate is given as 20% so Dividend for year 4 is calculated as Dividend 3 x (1 + g).

In cell B12 enter

=B9*(1 + B4)

• Price at the end of Year 3 - Using Gordon Growth Model.

In cell C14 enter

=B12/B5

• Current Fair Share Price. - We have to discount back each year dividend and Year 3 price at the required rate.

For this use NPV command in excel.

=NPV(rate, Value 1, Value 2,......)

In cell B17 enter

=NPV(B3,B7,B8,B9+C14)

B3 - Rate

B7 - Dividend 1

B8 - Dividend 2

Notice the last Value, which is B9 + C14 i.e. Dividend 3 + Price at the end of year 3.