How to calculate a modified bond duration Excel


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:

  • First, right-click the mouse on the columns A and B.
  • Next, left click the mouse on the width of the column and change the value to 32 for each column, and then click OK. Enter “Description of the notes” in cell A1 and select cell A1, and press Ctrl and B together to make the name bold. Then, enter the “data of bond” in cell B1 and select cell B1 and press Ctrl and B together to make the name bold.
  • Enter “date of maturity” in cell A2 and “1 January 2015” in cell B2. Next, enter the “bonds maturity date” in cell A3 and “January 1, 2025” in cell B3. Then, enter “annual coupon rate” in cell A4 and “5%” in Q4. In cell A5, enter “Annual yield to maturity” and in cell B5 enter “7%.” Since the coupon is paid quarterly, the frequency would be 4. Enter “coupons payment Frequency” in cell A6 and “4” in cell B6.
  • Next, enter a “Basis” in cell A7 and “3” in cell B8. In Excel, based is not required and the value of the selected calculation of the modified duration on the basis of actual calendar days in the accrual period and assumes that there are 365 days in a year.
  • Now you can solve the modified Macaulay duration of the bond. Enter “modified Duration” in cell A8, the formula is “=MDURATION (B2, B3, B4, B5, B6, B7)” in cell B8. The result is the modified duration is 7.59.
  • 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%).

    Investing stocks online advice #investingstocksonline