Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Construct a spreadsheet to estimate the optimal capital structure for a hypothetical company. The attached spreadsheet contains all the information you will need. However, to

Construct a spreadsheet to estimate the optimal capital structure for a hypothetical company. The attached spreadsheet contains all the information you will need. However, to work out the solution, you will need to complete the spreadsheet by entering appropriate formulas. Note that in most instances, proper linking will mean that you only need to enter the appropriate formula once and then can copy it to other relevant cells in a particular column.

Note that you are given the pre-tax cost of debt at different levels of financial leverage, but must calculate the after-tax cost of debt. Also, you will need to estimate the beta at various levels of financial leverage using the Hamada Model. From there you will need to use the CAPM to calculate the cost of equity at each level of financial leverage. Once you have done that, the value of the firm is just the discounted present value of the expected Free Cash Flows cash flow at the implied WACC. Note that we are assuming no growth in the FCFs, so the discounting is done with the regular perpetuity model.

image text in transcribed

L20 X G G 1 J M o Q H FCF in mil. K Tax Rate N Mil. Shares 481 0.28 10 Value of Firm S&P500 10 yr. T-Note I'm r Pf 10.00% 3.47% re B B 1.0500 WACC A B B C D E F 1 FIN401 Project 3 - Estimating Optimal Capital Structure 2 Fall 2021 S&P 3 Pre-tax After-tax Bond 4 W. W. rd ro Rating 5 0.000 1.000 2.65% 1.91% AAA 6 0.065 0.935 2.96% AA+ 7 0.130 0.870 3.25% AA 8 0.195 0.805 3.56% AA- 9 0.260 0.740 3.75% A+ 10 0.325 0.675 3.89% 11 0.390 0.610 4.01% A- 12 0.455 0.545 4.65% BBB+ 13 0.520 0.480 5.25% BBB 14 0.585 0.415 6.23% BBB- 15 0.650 0.350 7.22% CCC+ 16 0.715 0.285 7.84% CCC 17 0.780 0.220 9.12% CCC 18 0.845 0.155 10.56% CC 19 0.910 0.090 12.62% 20 0.975 0.025 18.45% D 21 22 23 24 25 Version 1 + Ready EO E + 100% L20 X G G 1 J M o Q H FCF in mil. K Tax Rate N Mil. Shares 481 0.28 10 Value of Firm S&P500 10 yr. T-Note I'm r Pf 10.00% 3.47% re B B 1.0500 WACC A B B C D E F 1 FIN401 Project 3 - Estimating Optimal Capital Structure 2 Fall 2021 S&P 3 Pre-tax After-tax Bond 4 W. W. rd ro Rating 5 0.000 1.000 2.65% 1.91% AAA 6 0.065 0.935 2.96% AA+ 7 0.130 0.870 3.25% AA 8 0.195 0.805 3.56% AA- 9 0.260 0.740 3.75% A+ 10 0.325 0.675 3.89% 11 0.390 0.610 4.01% A- 12 0.455 0.545 4.65% BBB+ 13 0.520 0.480 5.25% BBB 14 0.585 0.415 6.23% BBB- 15 0.650 0.350 7.22% CCC+ 16 0.715 0.285 7.84% CCC 17 0.780 0.220 9.12% CCC 18 0.845 0.155 10.56% CC 19 0.910 0.090 12.62% 20 0.975 0.025 18.45% D 21 22 23 24 25 Version 1 + Ready EO E + 100%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Handbook On Corporate Governance In Financial Institutions

Authors: Christine A. Mallin

1st Edition

1784711780, 978-1784711788

More Books

Students also viewed these Finance questions

Question

What is Accounting?

Answered: 1 week ago

Question

Define organisation chart

Answered: 1 week ago

Question

What are the advantages of planning ?

Answered: 1 week ago