HW 5 Base Case he base case is correct, but it includes only values and labels,
ID: 2789589 • Letter: H
Question
HW 5 Base Case
he base case is correct, but it includes only values and labels, no formulas. Note the rows with bullets in Column B. The values in these rows may be changed and your instructor expects your workbook to update correctly. The specific cell locations where changes may occur are:
E3:E5 E7:E8 E11:E13 E16:E19 C25:C35
Your job is to insert formulas in all cells that are formatted bold dark red. When you enter a correct formula, the resulting value or label in your output section should be the same as the value and label in the base case. Thus you have check figures as you build an interactive workbook
WACC and DCF Capital Budgeting
• Weight of new debt securities 40.0%
• Weight of new preferred equity 15.0%
• Weight of new common equity 45.0%
• Tax rate 40.0%
• Before-tax cost of new debt 7.5%
After-tax cost of new debt 4.5%
• Anticipated preferred stock price $40.00
• Preferred stock dividend per share $3.50
• Preferred flotation costs 5.0%
Cost of new preferred stock 9.2%
• Anticipated common stock price $45.00
• Current common dividend per share $3.20
• Common stock flotation costs 15.0%
• Dividend growth rate expected 10.0%
Cost of new common stock 19.2%
WACC = Required rate of return 11.8%
NCFs Year DCFs
• (220,000) 0 (220,000)
• 65,000 1 58,128
• 60,000 2 47,983
• 60,000 3 42,910
• 60,000 4 38,373
• 50,000 5 28,597
• 10,000 6 5,115
• 0 7 0
• 0 8 0
• 0 9 0
• 0 10 0
Net present value (NPV) $1,106
Internal rate of return (IRR) 12.0%
Proposal recommendation Accept
Prepared by: John Lasik
Explanation / Answer
After tax cost of debt
before tax cost of debt*(1-tax rate)
7.5*(1-.4)
4.5
cost of preferred stock
preferred dividend/net proceed
3.5/ 38
9.21%
net proceeds
market price*(1-flotation cost)
40*(1-5%)
38
cost of equity
(expected dividend/net proceeds)+growth rate
(3.52/38.25)+.1
19.20%
expected dividend
current dividend*(1+growth rate)
3.2*(1.10)
3.52
net proceeds
45*(1-.15)
38.25
growth rate
10%
Source
weight
cost of source
weight*cost of source
debt
0.4
4.5
1.8
preferred
0.15
9.21
1.3815
equity
0.45
19.2
8.64
Weighted average cost of capital
sum of weight*cost of source
11.822
Year
Net cash flow
present value of net cash flow = net cash flow/(1+r)^n r = 11.82%
0
-220000
-220000
1
65000
58129.14
2
60000
47985.75
3
60000
42913.39
4
60000
38377.2
5
50000
28600.43
6
10000
5115.441
Net present value
sum of present value of net cash flow
1121.343
IRR
using IRR function in MS Excel =irr(-220000,65000,60000,60000,60000,50000,10000)
12.03%
yes project should be accepted as it results in Positive NPV and IRR greater than cost of capital
After tax cost of debt
before tax cost of debt*(1-tax rate)
7.5*(1-.4)
4.5
cost of preferred stock
preferred dividend/net proceed
3.5/ 38
9.21%
net proceeds
market price*(1-flotation cost)
40*(1-5%)
38
cost of equity
(expected dividend/net proceeds)+growth rate
(3.52/38.25)+.1
19.20%
expected dividend
current dividend*(1+growth rate)
3.2*(1.10)
3.52
net proceeds
45*(1-.15)
38.25
growth rate
10%
Source
weight
cost of source
weight*cost of source
debt
0.4
4.5
1.8
preferred
0.15
9.21
1.3815
equity
0.45
19.2
8.64
Weighted average cost of capital
sum of weight*cost of source
11.822
Year
Net cash flow
present value of net cash flow = net cash flow/(1+r)^n r = 11.82%
0
-220000
-220000
1
65000
58129.14
2
60000
47985.75
3
60000
42913.39
4
60000
38377.2
5
50000
28600.43
6
10000
5115.441
Net present value
sum of present value of net cash flow
1121.343
IRR
using IRR function in MS Excel =irr(-220000,65000,60000,60000,60000,50000,10000)
12.03%
yes project should be accepted as it results in Positive NPV and IRR greater than cost of capital
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.