Question

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!*

You are an analyst in charge of valuing common stocks. You have been asked to value two stocks. The first stock AB Inc. just paid a dividend of $4.50. The dividend is expected to increase by 60%, 40%, 30% and 10% per year respectively in the next four years. Thereafter the dividend will increase by 4% per year in perpetuity.

The second stock is CD Inc. CD will pay its first dividend of $8 in 5 years. The dividend will increase by 40% per year for the following 3 years after its first dividend payment. Thereafter the dividend will increase by 3% per year in perpetuity.

Both stocks have a required rate of return of 25% per year for the next 2 years, and thereafter the required rate of return will be 10%.

1. Calculate AB’s expected dividend for t = 1, 2, 3, 4, and 5.
2. What is the current price of AB?
3. Calculate CD’s expected dividend for t = 1, 2, 3, 4, 5, 6, 7, 8 and 9.

What is the current price of CD?

Answer 1

year	expected dividend = current dividend*(1+growth rate)	expected dividend = current dividend*(1+growth rate)
1	4.5*1.6	7.2
2	7.2*1.4	10.08
3	10.08*1.3	13.104
4	13.104*1.1	14.4144
5	14.4144*1.04	14.990976
terminal value= expected dividend in year 5/(required return-growth rate)	14.9909/(10%-4%)	249.85
Year	cash flow	present value of cash flow/(1+r)^n r= 25%	present value of cash flow/(1+r)^n r= 25% for first 2 years and 10% 2 years onwards
1	7.2	7.2/1.25	5.76
2	10.08	10.08/1.25^2	6.4512
3	13.104	13.104/1.10^1	11.91272727
4	14.4144	14.4144/1.10^2	11.91272727
4	71.39	249.85/1.10^3	187.716003
value of stock = sum of present value of cash flow			223.75
year	expected dividend = current dividend*(1+growth rate)	expected dividend = current dividend*(1+growth rate)
5	8	8
6	8*1.4	11.2
7	11.2*1.4	15.68
8	15.68*1.4	21.952
9	21.952*1.03	22.61056
terminal value= expected dividend in year 5/(required return-growth rate)	22.61056/(10%-3%)	323.008
Year	cash flow	present value of cash flow/(1+r)^n r= 25%	present value of cash flow/(1+r)^n r= 10%
5	8	8/1.1^5	4.967370584
6	11.2	11.2/1.1^6	6.322108017
7	15.68	15.68/1.10^7	8.046319294
8	21.952	21.952/1.10^8	10.24077001
8	323.008	323.008/1.10^8	150.6856159
value of stock = sum of present value of cash flow			180.26