Back in January of this years I wrote a post (Mortgage Pool – Holding Period Return) accompanied by a spreadsheet, on how to calculate the holding period return and the internal rate of return, when buying and selling a pool of mortgages. This post uses the same logic, but is applied to option-free fixed income notes and bonds. I constructed this spreadsheet over the last couple of days with no Bloomberg® to test, so if you find any errors please let me know.

The bond yield function in Excel (YIELD) calculates the yield to maturity of an option-free bond given a price, and uses an internal rate of return calculation, like the XIRR formula. Internal rate of return solves, through an iterative process, for the rate that equates the present value of all cash flows to zero. In doing so, the calculation assumes that all future coupon payments will be reinvested at that solved rate. Holding period return, on the other hand, allows the investor to determine the reinvestment rate.

What this spreadsheet does, is to allow the user to buy and sell a bond, based on the yields to maturity of the bond. An internal rate of return is then calculated for the time the investment was held. Note that the Treasury day count is actual/actual, agency/corporate is 30/360, and the XIRR function is actual/365. The holding period return also assumes a day count of actual/ 365. Therefore, even if a bond is purchased and sold at the same yield to maturity, the internal rate of return and holding period of return might show slightly different returns.

The input cells (as is true in all my spreadsheets) are yellow. Start by entering a description of a security. The “Pick One” object is pointing to a drop down box. You have a choice of U.S. Treasury (notes or bonds), or U.S. Agency/Corporate. This sheet does not cover long or short first coupons. After the description of the security there are two blue objects surrounding the inputs for purchasing and selling the security. The security is purchased and sold based on a yield to maturity, on two different dates:

This gives the spreadsheet enough information to automatic construct a cash flow for purchasing and selling this security, suitable for calculating an internal rate of return (XIRR). The date of the first cash flow is the purchase settlement date. The first cash flow is the combined price and accrued interest on the purchase date, entered as a negative. From then on the coupon payments and dates are listed until the sale date. Then the last cash flow is the sale date and principal plus accrued interest to that date. The internal rate of return is an annual return (APY), so we have to convert it to a semiannual rate in order to compare it with bond yields.

The holding period of return is a simple formula set out below. All that is needed is the starting value (SV), the ending value (EV), the years held (YH) and a reinvestment rate. The first three are solved for you, so you need only enter the reinvestment rate.

In order to calculate the ending value I set up a separate cash flow that is also automatic.

The holding period return is put into one formula that calculates the future value of the cash flow, invested at the reinvestment rate. Divides that by the starting value. The quotient is then annualized.

I also include a series of reinvestment rates, form zero to 10% to show the holding period returns.

Download “HPR“

Pingback: Bond Swap – Horizon Analysis – Calculator | Excel@CFO