Bond refunding

I need help with the below problems.

Thanks

******************

UNIT 4 PROFESSIONAL CHALLENGE- INSTRUCTIONS

Hi Everyone,

For the Unit 4 Professional Challenge assignment, please complete Chapter 20, Problem 20-5 on page 822 (or Chapter 19, Problem 19-5 on page 711-712 if you have the 12th edition text; or Problem 19-5 on page 682 if you have the 11th edition text) on Bond Refunding Analysis. I have attached the Unit 4 Professional Challenge- Blank Answer Template. Please use it to fill in your answers. The cells that need to be filled in are in the color gray. Virtually all of the cells should have formulas (or linked to other cells) in order to show your work and logic. You must use formulas for your calculations rather than just pasting in numbers. Feel free to use a financial calculator to verify your answers but you want to use the Excel formulas. Points will be deducted without showing how your calculations are derived. This blank answer template will save you time in that you will not have to format your own spreadsheet.

Be sure to read pages 810-815 on Refunding Operations. Then write a short memo (a few paragraphs) on if Mullet should refund the bond or not. Justify why or why not.

Please use the Professional Challenge-Blank Answer Template to complete the calculations. Be sure to use Excel formulas in all cells. Here are some instructions to help you out:

1. For the Investment Outlay numbers, the Call premium and Flotation costs need to be negative numbers since they are cash outflows. For the call premium on the old bond (before tax), you will multiply the existing bond issue by the call premium %. For the call premium on the old bond (after tax), you will multiply the answer derived from the before tax calculation by (1 minus tax rate).
2. For the Flotation costs on new issue (before tax), it is given data. Be sure to make the amount a negative number. The answer in cell G27 will be the same as in cell F27.
3. For the immediate tax savings on old flotation cost expense (before-tax), you want to subtract the "years since old debt" from "maturity of original debt" and then divide this by "maturity of original debt" and then multiply it by the original flotation cost. For the after-tax, you will multiply the before-tax answer by the tax rate.
4. For the Extra Interest Paid on Old Issue, the number should also be negative. You should multiply the existing bond issue amount by one month (1/12) and multiply it by the original coupon rate. For the after-tax, you will multiply the before-tax answer by (1 - tax rate).
5. For the Interest Earned on Short-term Investment, you want to take the new bond issue and multiply it by the (After tax cost of debt /12 months). For the after-tax, you will multiply the before-tax answer by (1 - tax rate).
6. For the Total After Tax Investment (cell G31), just sum up cells G26 thru G30.
7. For the Annual Tax Savings from New Issue Flotation Costs, you want to divide the New Flotation Cost by the New Bond Maturity. For the after-tax, you will multiply the before-tax answer by the tax rate.
8. For the Annual Lost Tax Savings From Old Issue Flotation Costs, be sure to divide the original Flotation costs by Maturity of the Original Debt for the Before-Tax cell (cell F35). Make sure the Maturity of the Original Debt is negative in the formula since it is a cash outflow. For the After-tax (cell G35), multiply your answer in cell F35 by the tax rate.
9. For the Net Flotation Cost Tax Savings, you just sum up the interest tax savings (or loss) from the new issue flotation costs and the old issue flotation costs.
10. For the Interest on the Old Bond, you multiply the Existing Bond Issue by the Annual Original Coupon Rate. For the after-tax, you will multiply the before-tax answer by (1 - tax rate).
11. Interest on New bond (Before Tax) is the New Bond Issue amount multiplied by the New Cost of Debt. The New Bond issue number should be negative. For After Tax, you multiply cell F40 by (1 - Tax rate).
12. Net Interest Savings should be the sum of the Interest on the Old Bond and New Bond.
13. For cell D54 (NPV of Annual Flotation Cost Savings), you will want to use the After-tax cost of new debt as your Rate and the After-tax net Flotation Cost of Savings as the Pmt input in. Also make sure the Pmt number is negative.
14. For cell I54 (NPV of Annual Interest Savings), you will also want to use the After-tax cost of new debt as your Rate and the Annual Interest Savings as the Pmt input in. Also make sure the Pmt number is negative.
15. For the Bond Refunding NPV inputs, the Initial Outlay = Total-After Tax Investment. The PV of Flotation Costs is the number you calculated in cell D54. The PV of Interest Savings is the number you calculated in cell I54.
16. To find the Bond Refund NPV, you sum up cells C60, E60, & G60.
17. In cells D54 and I54 you will use the Present Value (PV) formula in Excel (not the NPV formula). The Bond Refund NPV answer in cell C62 will be the sum of cells C60, E60, and G60. Again, you will not use the NPV formula in Excel.
-------------------------
Problem 20-5
Mullet Technologies is considering whether or not to refund a $75 million, 12% coupon, 30-year bond issue that was sold 5 years ago. It is amortizing $5 million of flotation costs on the 12% bonds over the issue's 30-year life. Mullet's investment banks have indicated that the company could sell a new 25-year issue at an interest rate of 10% in today's market. Neither they nor Mullet's management anticipate that interest rates will fall below 10% any time soon, but there is a chance that rates will increase.
A call premium of 12% would be required to retire the old bonds, and flotation costs on the new issue would amount to $5 million. Mullet's marginal federal plus-state tax rate is 40%. 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 6% annually during the interim period.
A. Perform a complete bond refunding analysis. What is the bond refunding's NPV?
B. What factors would influence Mullet's decision to refund now rather than later?
-------------------------