Modified duration is the adjusted version of the Macaulay duration and takes into account how changes in interest rates affect the duration of the bond. Use Microsoft Excel to calculate changes of bond Length with these parameters: settlement date, maturity date, coupon rate, yield to maturity and frequency.
Modified duration determines the change in the value of fixed interest rate against the change in yield to maturity. The formula used to calculate the modified Duration of a bond the Macaulay duration of a bond divided by 1 plus the yield to maturity of the bonds, divided by the number of coupon periods per year.
In Excel, the formula used to calculate the modified Duration of the bond built in function MDURATION. This function returns the modified Macaulay Duration for securities, if the nominal or maturity value is $100.
For example, suppose you want to calculate the modified Macaulay duration of a bond with a settlement date of January 1, 2015, maturing on 1 January 2025, the annual coupon rate 5%, annual yield to maturity of 7% and the coupon is paid quarterly.
To find modified duration, do the following in Excel:
Used formula for calculating the percentage change of the bond price to changes in yield to maturity, multiplied by the negative value of the modified duration multiplied by 100%. Therefore, if interest rates increase by 1%, bond Price will fall 7.59% (0.01 * (-7.59) * 100%).