Question

What is the value today of KTS stock if it grows at 12% for 5 years, then 8% for 5 years, and then grows at 5% thereafter?

Create a valuation sheet in MS-Excel similar to the format shown below: ng mga tao 99 SUPERNORMAL GROWTH DO $2.00 Cost of Equity 12.00% 10.00% 10.00% 10.00% D3 10.00% 10.00% DS 10.00% 10.00% D7 10.00% D8 10.00% D9 10.00% D101 5.00% D6 ng g10 P10 Assume KTS currently pays a $2 per share dividend and has a cost of equity of 15%.

Answer 1

We have created the valuation sheet similar to the format which has been shared to arrive at the Value of the Stock.

D0	$       2.00	P0	25.4788
Cost of Equity	15%
g1	12%	D1	2.2400
g2	12%	D2	2.5088
g3	12%	D3	2.8099
g4	12%	D4	3.1470
g5	12%	D5	3.5247
g6	8%	D6	3.8067
g7	8%	D7	4.1112
g8	8%	D8	4.4401
g9	8%	D9	4.7953
g10	8%	D10	5.1789
g	5%	P10	36.2024

We have shared two tables, one representing final outputs and another one displaying formulas (which will aid understanding the logic employed to solve the problem)

A. F G 1 $ 2.00 DO Discount rate (Cost of Equity) Discounted Cash Flows 2 РО 25.4788 Year Cost of Equity 15% D1 g1 12% 2.2400 1 0.8696 1.9478 12% 5 D2 2.5088 0.7561 1.8970 g2 12% 6 D3 2.8099 3 0.6575 1.8475 g3 7 12% D4 3.1470 0.5718 1.7993 g4 12% D5 3.5247 5 0.4972 1.7524 g5 8% D6 9 3.8067 0.4323 1.6457 g6 8% 10 D7 4.1112 7 0.3759 1.5455 g7 8% 4.4401 11 D8 0.3269 1.4515 g8 8% 12 D9 4.7953 0.2843 1.3631 g9 8% 1.2801 13 D10 5.1789 10 0.2472 g10 5% 14 P10 36.2024 10 0.2472 8.9487 15 9 LO LLI st

A D E F G H Discount rate (Cost of Equity) 2. SUM(H4:H14) Discounted Cash Flows 2 DO PO Year Cost of Equity 0.15 -1/(1+$C$3)^F4 1/(1+$C$3)^F5 -1/(1+$C$3)^F6 1/(1+$C$3)^F7 1/(1+$C$3)^F8 -1/(1+$C$3)^F9 1/(1+$C$3)^F10 -1/(1+$C$3)^F11 1/(1+SC$3)^F12 -1/(1+$C$3)^F13 1/(1+SC$3)^F14 C2*(1+C4) G4*E4 4 g1 0.12 D1 1 -E4*(1+C5) G5*E5 5 0.12 D2. 2 g2 -E5*(1+C6) G6*E6 6 0.12 D3 g3 -E6*(1+C7) =G7*E7 7 0.12 D4 4 g4 -E7*(1+C8) G8*E8 0.12 D5 5 g5 -E8*(1+C9) -E9*(1+C10) G9*E9 0.08 D6 6 g6 -G10*E10 10 0.08 D7 7 g7 -E10*(1+C11) G11*E11 11 g8 0.08 D8 8 =E11*(1+C12) =G12*E12 12 0.08 D9 g9 -E12*(1+C13) G13*E13 13 0.08 D10 10 g10 -E13*(1+C14)/C3-C14 10 =G14*E14 14 0.05 P10 gg 15 LO