PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you! You are an analyst...

Free

50.1K

Verified Solution

Question

Finance

PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITHANY RELEVANT FORMULAS, thank you!
You are an analyst in charge of valuing common stocks. You havebeen asked to value two stocks. The first stock AB Inc. just paid adividend 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 inperpetuity.
The second stock is CD Inc. CD will pay its first dividend of $8in 5 years. The dividend will increase by 40% per year for thefollowing 3 years after its first dividend payment. Thereafter thedividend will increase by 3% per year in perpetuity.
Both stocks have a required rate of return of 25% per year forthe next 2 years, and thereafter the required rate of return willbe 10%.
Calculate AB’s expected dividend for t = 1, 2, 3, 4, and5.
What is the current price of AB?
Calculate CD’s expected dividend for t = 1, 2, 3, 4, 5, 6, 7, 8and 9.
What is the current price of CD?

Answer & Explanation Solved by verified expert

4.5 Ratings (799 Votes)

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

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITHANY RELEVANT FORMULAS, thank you!*You are an analyst in charge of valuing common stocks. You havebeen asked to value two stocks. The first stock AB Inc. just paid adividend 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 inperpetuity.The second stock is CD Inc. CD will pay its first dividend of $8in 5 years. The dividend will increase by 40% per year for thefollowing 3 years after its first dividend payment. Thereafter thedividend will increase by 3% per year in perpetuity.Both stocks have a required rate of return of 25% per year forthe next 2 years, and thereafter the required rate of return willbe 10%.Calculate AB’s expected dividend for t = 1, 2, 3, 4, and5.What is the current price of AB?Calculate CD’s expected dividend for t = 1, 2, 3, 4, 5, 6, 7, 8and 9.What is the current price of CD?

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!* You are an analyst...