Bond Duration

Duration is a measure of interest rate risk of a debt security. It measures price sensitivity of a fixed income instrument with reference to a movement in interest rates. A higher duration means higher interest rate risk and vice versa. Duration doesn’t simply equal the term of the fixed income security except in case of a zero-coupon bond where it equals the term of the bond. In all other cases, where there are periodic payments in addition to the final balloon payment, duration is lower than the term of the fixed income instrument.

Value of debt securities fall when the market interest rates rise simply because bond investors will prefer to invest in new debt securities offering higher stated interest rates unless the price of existing debt securities drop and vice versa. The risk resulting from sensitivity of bond prices to market interest rate is called interest rate risk. Owing to the magnifying effect of time value of money, it can be established that interest rate risk is higher for debt securities with longer term, lower coupon rates and lower yield to maturity.

There are several types of duration measure, namely Macaulay duration, modified duration, effective duration and key-rate duration.

Duration Calculations in Excel

Excel has two functions to calculate duration: DURATION, which calculates Macaulay duration and MDURATION which calculates Macaulay duration.

Both duration measures DURATION and MDURATION have same arguments:

DURATION(settlement, maturity, coupon, yld, frequency, [basis])

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Settlement is the date on which duration is being calculated, maturity refers to the maturity date, coupon stands for the annual coupon rate, yld is the security’s annual yield, frequency stands for the number of coupon payments per year while [basis] is an optional argument related to day-counting method


On 14 November 2017, you added the three bonds to your company’s investment portfolios: (a) a $1,000 zero-coupon bond yielding 5.1% to maturity which is 31 December 2020, (b) a $100 face-value 6% semi-annual bond maturing on 30 June 2023 and yielding 4.8% and (c) a $1,000 face value 5.5% semi-annual bond maturing on 30 June 2023 and yielding 5.8%.

As required by your risk management manual, you must calculate Macaulay duration for each bond and report it to your company’s risk manager.

Bond duration calculation in Excel

As mentioned above, duration of a zero-coupon bond equals it outstanding term, while in other cases, it is less than the term of the debt instrument. Bond B is less risky than Bond C even though they have equal terms because it has higher coupon rate.

The risk manager has replied you back requesting you to calculate modified duration because Macaulay duration doesn’t consider the price sensitivity that results from changes in yield to maturity of the debt securities.

Macaulay duration calculation in Excel

Using modified duration, our assessment of interest rate risk inherent in Bond C has dropped due to its relatively higher yield to maturity to almost cancel out Bond B’s advantage arising from comparatively higher coupon rate.

Written by Obaidullah Jan