Question

answer using excel please

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 $6.00. 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 $10 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. a) Calculate AB's expected dividend for t= 1, 2, 3, 4, and 5. b) Calculate CD's expected dividend for t - 1, 2, 3, 4, 5, 6, 7, 8 and 9. Assume that the required rate of return for both stocks is 15%. Both stocks have a required rate of return of 45% per year for the next 2 years, 30% per year for the following 2 years, and thereafter the required rate of return will be 10%. c) What is the current price of AB? d) What is the current price of CD? Both stocks have a required rate of return of 45% per year for the next 2 years, 30% per year for the following 2 years, and thereafter the required rate of return will be 10%. e) What is the current price of AB? 1) What is the current price of CD? Note you cannot use the NPV function to immediately value the stocks at time 0, as the required rate of return changes during the forecast period. Note: All of Calculations should be rounded to the nearest penny. That is 2 decimal places.

Answer 1

1. Dividend of AB

Particulars		Amount
Last year Dividend		6.00
growth rate	year 1	0.60
	year 2	0.40
	year 3	0.30
	year 4	0.10
4+ growth indefinitely		0.04
	working	Dividend
year 1	Div = 6*(1+0.60)	9.60
year 2	Div = 9.60*(1+0.40)	13.44
year 3	Div = 13.44+(1+0.30)	17.47
year 4	Div = 17.47 +(1+0.10)	19.22
year 5	Div = 19.22*(1+0.04)

*Dividend = last years dividend* (1+ growth rate)

2. Dividend of CD

particulars		Amt
Last year Dividend	year 5	10.00
growth rate	year 6	0.40
	year 7	0.40
	year 8	0.40
	year 9	0.03
8+ growth indefinitely		0.03
	working
year 6	Div = 6*(1+0.60)	14.00
year 7	Div = 9.60*(1+0.40)	15.40
year 8	Div = 13.44+(1+0.30)	21.56
year 9	Div = 17.47 +(1+0.10)	22.21

In case od CD, the first dividend in paid after 5 years

So the dividend paid is as follow:

year	Amt ($)
year 1	-
year 2	-
year 3	-
year 4	-
year 5	10.00
year 6	14.00
year 7	15.40
year 8	21.56
year 9	22.21

3. Assuming the question is repeated twice by mistake, and the first case is to find the current price with required rate of return as 15%

r=0.15

after 4 years , one will receive dividend at a constant growth of 4%

so we need to find the value of stock at the end of the yaer4

which is = this year dividend* (1+ growth)/(discount rate – growth)

next years dividend = This years dividend * (1+growth)

value of stock at the end of year 4

= 19.99/(0.15-0.04)

181.71

So now we need to find the present value of all the dividend and value of stock,

Year	Dividend	working	Discount factor = 1/(1+r)^n	Discounted cash Flow= Cash flow * discount value
year 1	9.60	1/ (1+0.15)^1	0.87	8.35
year 2	13.44	1/ (1+0.15)^2	0.76	10.16
year 3	17.47	1/ (1+0.15)^3	0.66	11.49
year 4	19.22	1/ (1+0.15)^4	0.57	10.99
year 4	181.71	1/ (1+0.15)^4	0.57	103.89
			Total	144.88

So the current price of AB is $ 144.88 , if taking IRR as 15%

4. Price of CD

value of stock at the end of year 8

= 22.21/(0.15-0.03)

185.06

So the current price,

Year	Dividend	working	Discount factor = 1/(1+r)^n	Discounted cash Flow= Cash flow * discount value
year 1	-	1/ (1+0.15)^1	0.87	-
year 2	-	1/ (1+0.15)^2	0.76	-
year 3	-	1/ (1+0.15)^3	0.66	-
year 4	-	1/ (1+0.15)^4	0.57	-
year 5	10.00	1/ (1+0.15)^5	0.50	4.97
year 6	14.00	1/ (1+0.15)^6	0.43	6.05
year 7	15.40	1/ (1+0.15)^7	0.38	5.79
year 8	21.56	1/ (1+0.15)^8	0.33	7.05
year 8	185.06	1/ (1+0.15)^8	0.33	60.50
			Total	84.36

Current price of CD= $84.36

E,f similarly the current price can be determined when the r will change ,as follow, for CD

Working	Discount factor = 1/(1+r)^n
1/ (1+0.45)^1	0.69
1/ (1+0.45)^2	0.48
1/ (1+0.30)^3	0.46
1/ (1+0.30)^4	0.35
1/ (1+0.10)^5	0.62
1/ (1+0.10)^6	0.56
1/ (1+0.10)^7	0.51
1/ (1+0.10)^8	0.47
1/ (1+0.10)^8	0.47