IFM7 Chapter 17 - Excel by rMQDu4g

VIEWS: 178 PAGES: 3

									             A               B                C                 D                 E                F            G
 1
 2
 3                                                   Chapter 19. Ch 19-06 Build a Model
 4                                            Note: Fill in the shaded cells with the appropriate formula
 5
 6   Mullet Technologies is considering whether or not to refund a $75 million, 12 percent coupon, 30 year bond issue that was sold
 7   5 years ago. It is amortizing $5 million of flotation costs on the 12 percent bonds over the issue's 30-year life. Mullet's
 8   investment bankers have indicated that the company could sell a new 25-year issue at an interest rate of 10 percent in today's
 9   market. Neither they nor Mullet's management anticipate that interest rates will fall below 10 percent any time soon, but
10   there is a chance that interest rates will increase.
11
12   A call premium of 12 percent would be required to retire the old bonds, and flotation costs on the new issue would amount to
13   $5 million. Mullet's marginal federal-plus-state tax rate is 40 percent. The new bonds would be issued 1 month before the old
14   bonds are called, with the proceeds being invested in short-term government securities returning 6 percent annually during the
15   interim period.
16
17   Current bond issue information
18   Par value                      $        75,000,000
19   coupon rate                                   12%
20   original maturity                                30
21   remaining maturity                              25
22   original flotation costs       $         5,000,000
23   Call premium                                  12%
24   Tax rate                                      40%
25
26
27   New issue information
28   Coupon rate                              10.0000%
29   maturity                                        25
30   flotation costs                  $       5,000,000
31
32   Time between issues (months)                     1
33   rate on surplus funds (annual)                 6%
34
35   a. Perform a complete bond refunding analysis. What is the bond refunding's NPV?
36
37   Initial investment outlay to refund old issue:
38   Call premium on old issue =
39   After-tax call premium =
40   New flotation cost =
41   Old flotation costs already expensed =
42   Remaining flotation costs to expense =
43   Tax savings from old flotation costs =                                You get to expense the remaining flotation costs
44   Additional interest on old issue after tax =                          This is interest paid on the old bond issue between when the new b
45   Interest earned on investment in T-bonds after tax =                  This is interest earned on the proceeds from the new bonds before
46   Total investment outlay =
47
48   Annual Flotation Cost Tax Effects:
49   Annual tax savings on new flotation =
50   Tax savings lost on old flotation =
51   Total amortization tax effects =
52
             A                 B             C                     D              E             F   G
53   Annual interest savings due to refunding:
54   Annual after tax interest on old bond =
55   Annual after tax interest on new bond =
56   Net after tax interest savings =
57
58   Annual cash flows =
59
60   NPV of refunding decision =
61
62   c. At what interest rate on the new debt is the NPV of the refunding no longer positive?
63
64   Use Goal Seek to set cell D60 to zero by changing cell C28.
65
66                         "Break-even" interest rate =
                      H           I      J       K              L           M
             1                  7/3/2003
             2
             3
             4
             5
ar bond issue that was sold
             6
year life. Mullet's
             7
 e of 10 percent in today's
             8
 nt any time9soon, but
            10
            11
 w issue would amount to
            12
 ed 1 month before the old
            13
            14
 ercent annually during the
            15
            16
            17
            18
            19
            20
            21
            22
            23
            24
            25
            26
            27
            28
            29
            30
            31
            32
            33
            34
            35
            36
            37
            38
            39
            40
            41
            42
            43
ning flotation costs
            44
d bond issue between when the new bonds are issued and the old bonds are retired
            45
 proceeds from the new bonds before they are used to pay off the old bonds.
            46
            47
            48
            49
            50
            51
            52

								
To top