The modified duration of a bond is an adjusted version of the Macaulay duration and is used to calculate the changes in a bond's duration and price for each percentage change in the yield to maturity.

Key Takeaways

  • The formula for modified duration tells you the change in the value of a bond in relation to a change in its yield to maturity.
  • In Excel, the formula is built into the MDURATION function.
  • Follow this step-by-step example to complete the formula.

You can use Microsoft Excel to calculate a bond's modified duration given these parameters: settlement date, maturity date, coupon rate, yield to maturity, and frequency.

What Modified Duration Tells You

Modified duration determines the change in the value of a fixed income security in relation to a change in the yield to maturity. The formula used to calculate a bond's modified duration is the Macaulay duration of the bond divided by 1 plus the bond's yield to maturity divided by the number of coupon periods per year.

In Excel, the formula used to calculate a bond's modified duration is built into the MDURATION function. This function returns the modified Macaulay duration for a security, assuming the par value is $100.

Example of Modified Duration Calculation in Excel

For instance, say you want to calculate the modified Macaulay duration of a 10-year bond with a settlement date on Jan. 1, 2020, a maturity date on Jan. 1, 2030, an annual coupon rate of 5%, and an annual yield to maturity of 7%. The coupon is paid quarterly.

To find the modified duration take the following steps in Excel:

  1. First, right-click on columns A and B.
  2. Next, left-click on Column Width and change the value to 32 for each of the columns, then click OK. Enter "Bond Description" into cell A1, then select cell A1 and press the CTRL and B keys together to boldface the title. Then, enter "Bond Data" into cell B1, then select cell B1 and press the CTRL and B keys together to boldface the title.
  3. Enter "Bond's Settlement Date" into cell A2 and "January 1, 2020" into cell B2. Next, enter "Bond's Maturity Date" into cell A3 and "January 1, 2030" into cell B3. Then, enter "Annual Coupon Rate" into cell A4 and "5%" into B4. In cell A5, enter "Annual Yield to Maturity" and in cell B5, enter "7%." Since the coupon is paid quarterly, the frequency is 4. Enter "Coupon Payment Frequency" into cell A6 and "4" into cell B6.
  4. Next, enter "Basis" into cell A7 and "3" into cell B8. In Excel, the basis is optional and the value chosen calculates the modified duration using actual calendar days for the accrual period and assumes that there are 365 days in a year.
  5. Now you can solve the modified Macaulay duration of the bond. Enter "Modified Duration" into cell A8 and the formula "=MDURATION (B2, B3, B4, B5, B6, B7)" into cell B8. The resulting modified duration is 7.59.

The formula used to calculate the percentage change in the price of the bond is the change in yield to maturity multiplied by the negative value of the modified duration multiplied by 100%. Therefore, if interest rates increase by 1%, the price of the bond is expected to drop 7.59% = [0.01 * (-7.59) * 100%].