Make-whole Call Provisions in Excel

In recent years, bond issuers have changed from the traditional call schedule to a “make-whole” type of call. Generally, this is good for investors as it makes it less likely that high interest bonds will be called. If it is called, then they are “made whole” because they are paid the present value of the remaining cash flows. In a traditional call, investors would receive only the face value and, perhaps, a small call premium. In most cases, investors will be better off with a make-whole call.

For issuing firms the make-whole call provision provides some financial flexibility. For example, it may be that the covenants (restrictions on the issuer) are somewhat onerous, and the issuer may wish to get out from under them, or there may be a change of control. The make-whole call allows refunding without a call premium being paid or waiting until the next call date specified in the bond’s indenture.

A make-whole call provision means that the bond can be called at any time (on short notice – generally 30 or so days), and that the issuer will pay the present value of the remaining cash flows to investors. Importantly, this will be different than the current price of the bond because the discount rate is different than the recent yield to maturity. Specifically, the discount rate that is used to determine the present value is spelled out in the indenture and will be equal to the rate on a Treasury security plus some spread that is also given in the indenture. The spread is fixed at issuance (usually 15 to 50 basis points, depending on credit rating and term to maturity), though the “Comparable Treasury Issue” can change. The Treasury security that is used will be selected by an independent investment banking firm at the time of the call.

In late 2010, PPG Industries issued a bond that matures on 15 November 2040. The bond pays interest semiannually with a coupon rate of 5.50% per year (on 15 May and 15 November). You can see the details of this issue here. The indenture states that “optional redemption” will be on a make-whole basis at a spread of 25 bps over the comparable Treasury.

On 28 October 2014 the bond traded at a price of 117.95 (\$1,179.50), though we will pretend that the trade took place on 15 November 2014 so that we do not need to worry about valuing the bond between coupon payment dates.

On 13 November 2014, a comparable Treasury bond (the 4.25%’s of 2040) was trading at 122.7969. We will again assume that this quote was from 15 November 2014. The yield on this security was 2.98%. Therefore, the yield that will be used to calculate the present value of the remaining payments is 2.98% + 25bps = 3.23%.

With these minor simplifications, we will now determine the call price of the PPG 5.5’s of 2040. To do so, we need to calculate the present value of the remaining cash flows at the appropriate discount rate. There are 51 remaining interest payments of \$27.50 each, plus one payment of \$1,027.50 (return of principal + last interest payment). The image below shows the timeline for the cash flows of this bond:

In Excel, there are several ways to do this calculation and we will illustrate the formulas. The image below shows the start of the spreadsheet:

Using the NPV Function

First, we can create a table of the remaining cash flows and then use the NPV function to find the present value. In my spreadsheet, the remaining cash flows are in B21:B72 (I am assuming that the bond is called immediately after the 15 November coupon payment). So, the formula is:

=NPV(B13/B9, B21:B72)

Where the call yield is in B13 and B9 is the payment frequency (2 for semiannual). This gives us a call price of \$1,396.54, which is \$217.04 above the current price of the bond. Obviously, PPG is unlikely to call the bond under these circumstances.

Using the Price Function

We can also use the Price function since it simply calculates the present value of a bond’s cash flows. The only unusual thing is that we use the Treasury yield plus the spread. The formula is:

=PRICE(B10, B11, B6, B13, B5/10, B9, 0)*10

And that, of course, gives exactly the same result as the NPV function (\$1,396.54).

Using the PV Function

Finally, we can use Excel’s PV function to find the call price of the bond. Ordinarily, we wouldn’t do this because the PV function doesn’t provide correct values between coupon payment dates, but we are assuming that we are on a coupon date. The formula is:

=PV(B13/B9, ROUND((B11-B10)/365, 0)*B9, -B6*B5/B9, -B5)

Note that I have used the Round function to calculate the number of years to maturity, and as before I’ve used the Treasury yield plus the spread for the Rate argument. Naturally, the answer is the same as it should be since we are merely calculating the present value of the remaining cash flows.

The final spreadsheet showing the formulas appears below:

If you enjoyed this tutorial, you may want to view some of my other Excel tutorials.