Schumann Shoe Manufacturer is considering whether or not to refund a $70 million, 10% coupon, 30-year bond issue that was sold 8 years ago. It is amortizing $4.5 million of flotation costs on the 10% bonds over the issue's 30-year life. Schumann's investment bankers have indicated that the company could sell a new 22-year issue at an interest rate of 8 percent in today's market. Neither they nor Schumann's management anticipate that interest rates will fall below 6 percent any time soon, but there is a chance that interest rates will increase.

A call premium of 10 percent would be required to retire the old bonds, and flotation costs on the new issue would amount to $5 million. Schumann's marginal federal-plus-state tax rate is 40 percent. The new bonds would be issued 1 month before the old bonds are called, with the proceeds being invested in short-term government securities returning 5 percent annually during the interim period

a. Perform a complete bond refunding analysis. What is the bond refunding's NPV?

b. At what interest rate on the new debt is the NPV of the refunding no longer positive?

( Can you please upload an Excel File. I need formulas. Thank You so much for the great help as always :) )

1 Build a Model Solution 2 Solution See EXCEL Spreadsheet in Dropbo 3 Chapter 18 4 Problem 5 | Schumann Shoe 8 anuracturer is considering whether or not to refund a milion, luz coupon, ju- year bond issue that was sold 8 years ago. It is amortizing $4.5 million of flotation costs on the 10 7 bonds over the issue's 30-year life. Schumann's investment bankers have indicated that the company could sell a nev 22-year issue at an interest rate of 8 percent in today's market. Neither 9 they nor Schumann's management anticipate that interest rates vill fall belov 6 percent any time 0 soon, but there is a chance that interest ratesill increase 13 on the new issue would amount to 5 million. Schumann's marginal federal-plus-state tax 14 rate is 40 percent. The ner bonds vould be issued 1 month before the old bonds are called s ith the proceeds being invested in short-term government securities returning 5 percent annually during the interim period | Current bond issue data 70,000,000 10% 30 Par value 20 Coupon rate 21 Original maturity 22 Remaining maturity 23 Original flotation costs 24 Call premium 25 Ta rate 4,500,000 10% 40% Refunding data Coupon rate Flotation costs Rate earned on proceeds of ner bonds b 8.0000% 5,000,000 5% 29 Maturity 1 Time betveen issuing new bonds and call 4 a. Perform a complete bond refunding analysis. What is the bond refunding's NPV? w Hint: F //of closely the Ch. 18 T lkit starting with line 49. Initial investment outlay to refund old issue , Call premium on old issue = 40 After-tax call premium = 41 New flotation cost= 42 Old flotation costs already expensed 43 Remaining flotation costs to expense = You get to expense the remaining flotation costs Tax savings from old flotation costs = 45 Additional interest on old issue after tax = 46 Interest earned on investment in T-bonds after tax = 47 Total investment outlay- This is interest paid on the old bond issue bet eenhen the ner bonds are issued and the old bonds are retired K 49 Annual Flotation Cost TaH Effects: 50 Annual tax savings on new flotation = 51 Tax savings lost on old flotation = 52 Total amortization tax effects- This is interest earned on the proceeds from the ner bonds before they are used to pay off the old bonds 54 Annual interest savings due to refunding 55 Annual after tax interest on old bond = 56 Annual after tax interest on new bond 7Net after ta interest savings- 59 Annual cash flows = 61 After-tax cost of new debt- 4.80% 63 NPV of refunding decision- 5 b. At hat interest rate on the nev debt is the NPV of the refunding no longer positive? 7 Use Goal Seek to set cell D63 to zero by changing cell 028 Break-even interest rate =