A few posts back we looked at Holding Period Return On Option-Free Fixed Income Securities. I thought we could take that one step further, and show horizon analysis for a bond swap. In order to help explain the calculator, I have divided the analysis into three sections and placed a rounded triangle around each one. The blue triangle is the present value or initial inputs. The example shows a swap from a U.S. Treasury (actual/365 day count) into a U.S. government agency (30/360 day count). The two dark yellow cells are drop down boxes with a choice of security types. As is true with all of my workbooks, light yellow cells are input cells.

After entering settlement date, enter the amount of par value you will sell, the maturity date, coupon, frequency of payments, and the yield to maturity the bonds will be sold at. For the bonds to be purchased, you have three options for par value. You can reinvest all the proceeds from the sold bonds ($/$), buy the same par value as sold (Par/Par), or enter a different amount altogether (Other). Note that in any case, the bonds will be rounded down to the nearest $1,000 in par value automatically. Continue on entering the description of the purchased bonds. Note that this example swap will increase the yield to maturity by 30 basis points. After prices and accrued interest is automatically calculated, the row called “Takeout/(Payup)” will be either the excess funds taken out of the swap if positive, or the additional funds needed to make the amount of proceeds equal to the amount invested in the new bonds (negative). Also note that the price calculated, given the yield to maturity, is rounded to three places which is the current convention.

At some point in the future, the swap will either be unwound, or marked to market. It is referred to as the horizon date. This is where the orange triangle comes in. After the horizon date is entered, you need the reinvestment assumption during the duration of this swap and the yields to maturity, at the horizon date, for the two bonds.

Then you get a recap of the swap that includes for the components of the swap. If you are familiar with the bond market, I think the recap is self explanatory. If not leave a comment.

Download “Swap”

This is terrific. Do you have a sheet that calculates amortized book value with dates in between coupons and maturity date? Effective interest method versus SL.

Kent,

There are simple examples with Excel on YouTube, but I have not created one.

Don

Good work. But, it appears COUPDAYBS gives the wrong amount of days to calculate interest for. It counts the number of days from last coupon up to and including settlement day. You’re not supposed to count the settlement date, when calculating accrued interest. We need to know how many days starting from last coupon, up to but not including settlement date.

Hi Jack,

Thank you for your comment. You did not give a specific calculation, so let’s look at the default example I used. Cell G25 shows the accrued interest for a Treasury note or bond and uses COUPDAYBS. Treasuries use an actual/actual basis, so given a maturity date of 08/15/2028 and a settlement of 06/3/15, we can assume that the last coupon payment on the bond was 02/15/15 and the next payment is 08/15/15.

The number of actual days in this semiannual period is 181 (08/15/2015 – 02/15/2015). The number of days from the last payment is 108 (06/03/15-02/15/2015). The coupon is 5.5%. The accrued interest would be:

=0.055/2/181*108*25000000 or $410,220.99, which is the value in cell G25.

Please send me your calculation of accrued interest.

Thanks, Don

Thanks for your response Don. What happened is I tested it on a bond I sold, at work, recently that has a maturity date of 8/20/2018 . The days of accrued interest that my firm’s more-automated software (that links directly to market data) was showing wasn’t matching up with your spreadsheet. Looking further into it, I found out that bond actually pays interest on the 19th of the coupon months, even though it matures on the 20th! So, the spreadsheet was coming up one day short . So, I guess the moral is you can’t always assume that the coupon dates can be calculated correctly from the maturity date.

By the way, the version of your spreadsheet available here doesn’t have the “Buy Par Bond?” section on the upper right in it. Maybe there is a later version.

Thanks again

Jack,

The picks for type of swap are $/$ (purchase equal dollars),Par/Par (purchase equal par value), or “Other” (you enter your own Par value to purchase). If you don’t see these, download a fresh copy. I’m not sure where you are seeing “Buy Par Bond”.

Thanks

Don

Hey Don I had a question on this swap spreadsheet to make sure I am using it correctly. Could I give you a quick call to ask you a couple questions? I promise I won’t take up more than a couple minutes of your time Sir. Please send me an email if that is ok. Thanks

Ryan R. Coombs

Vice President – Research & Strategies

Don – I have a question on this swap spreadsheet and was wondering if I could give you a quick call? Please shoot me an email if that would be ok. Thanks!

Ryan,

I sent my phone number by email to you. Please respond to my email with your phone number, so I recognized your call.

Don, nice simple bond calculator. Any chance you have worked out a ‘break-even’ model for the swap. for example, if i own a bond purchased to yield 2% and i am thinking of selling to replace with bond yielding 3%, how long would it take me to recoup my ‘realized loss’ (3% mkt yield vs. 2% book yield)? assume bond originally bought at $100 and reinvest only proceeds from the sale…

Hi Rob,

I wrote this program years ago, when I was managing accounts that were only concerned with total rate of return. I never really was comfortable with the concept of breaking even to book loss however, regardless of the type of account. What if you picked up yield on the swap, but the bond you sold outperformed the bond you purchased (spread widened)? The reality might be that at the time you calculated that you broke even, you might actually be at a loss relative to the bond you sold. I realize that sometimes those to whom one reports can more easily grasp the concept of “making up for a loss” than holding period return, so you might not have a choice.

To answer your question, I understand what you are looking for, however I don’t have a spreadsheet specifically for that purpose.

Don