Calculating EXCEL Duration Between Bond Coupon Payments
Excel duration between coupon dates
How does EXCEL calculate DURATION for settlement dates between coupon dates? This was a question posed by one of our readers recently. We have already examined the calculation of Excel Duration and Price functions when settlement dates equal coupon payments dates in our earlier posts:
We had demonstrated how the duration is calculated from first principles and how the results tallied with numbers generated using EXCEL DURATION (for Macaulay Duration), PRICE & MDURATION (for Modified Duration) functions.
In this post we look at an instance where the settlement date falls between coupon payment dates. When the price of a note or bond is calculated on a date other than a coupon paying date or an issue date, the price can either be quoted as a clean price or a dirty price depending on whether accrued interest is excluded in the calculation or not. EXCEL’s quoting convention for PRICE is the clean price of the bond, i.e. the accrued interest is subtracted out from the sum of the present value of future cash flows. You can see this when you click the “Help on this function” link for EXCEL’s PRICE function where the last part of the given formula is the accrued interest (boxed in red below):
However, for the calculation of EXCEL’s DURATION, accrued interest is not factored out.
Recall the formula for Macaulay Duration:
In the numerator PV(CF1) is the present value of the cash flow due at the end of the initial period which contains the settlement date. This cash flow will consist of the coupon payment and/or the principal payment, if applicable; interest earned for the period between the period start date and the settlement date will not be deducted from the present value of this cash flow.
Likewise in the denominator, PVTCF (Present Value of Total Cash Flows) is equivalent to the dirty price of the bond and not its clean price. If we use EXCEL’s PRICE function to determine the denominator for the Macaulay duration we need to remember to add back the accrued interest.
Let us look at an example of a five year note with the following terms:
On a coupon payment date of 31-July-2016, there is no accrued interest, so clean price equals the dirty price of the note which equals 99.979185.
Macaulay Duration from EXCEL equals 1.49347. The calculation using first principles can be seen in the screen shot below.
Because accrued interest is zero, the present values of cash flows (PVCF) for calculating price and the Macaulay duration in each period are the same.
Now let us consider the settlement date of 14-November-2016 which falls between the coupon payment dates of 31-July-2016 and 31-January-2017 respectively.
In this case we can see a difference between the cash flows in the initial period which contains the settlement date i.e. 31-July-2016 and 31-January-2017. The PVCF for Price calculation in this period is reduced by the accrued interest earned between 31-July-2016 and 14-November-2016 where accrued interest = 100 x 0.875%/2 x A/E = 100 x 0.875%/2 x 106/184 = 0.252038. There is no similar deduction made in this period for PVCF for the duration calculation.
In row 24, Price in the “PVCF – for Price” column is the clean price of the note, while the figure calculated in the “PVCF – for Macaulay Duration” column is the dirty price of the note. As mentioned above, Dirty Price = EXCEL PRICE + Accrued Interest = 99.982924 + 0.252038 = 100.234962.
Macaulay duration on 14-November-2016 is 1.20543. It uses an initial period PVCF from which accrued interest has not been subtracted in the numerator and dirty price in the denominator.
The value derived from first principles matches the value obtained using EXCEL’s DURATION function.