The modified duration is an adjusted version of the Macaulay duration and takes into account how interest rate fluctuations affect a bond's durations. Use Microsoft Excel to calculate a bond's modified duration given these parameters: settlement date, maturity date, coupon rate, yield to maturity, and frequency.
The 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 security, assuming the par value is $100.
For instance, assume you want to calculate the modified Macaulay duration of a bond with a settlement date on January 1, 2015, a maturity date on January 1, 2025, annual coupon rate of 5%, annual yield to maturity of 7% and the coupon is paid quarterly.
To find modified duration take the following steps in Excel:
- First, right click on columns A and B.
- Next, left click on Column Width and change the value to 32 for each of the columns, and click OK. Enter "Bond Description" into cell A1 and select cell A1 and press the CTRL and B keys together to make the title bold. Then, enter "Bond Data" into cell B1 and select cell B1 and press the CTRL and B keys together to make the title bold.
- Enter "Bond's Settlement Date" into cell A2 and "January 1, 2015" into cell B2. Next, enter "Bond's Maturity Date" into cell A3 and "January 1, 2025" 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 will be 4. Enter "Coupon Payment Frequency" into cell A6 and "4" into cell B6.
- 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 there are 365 days in a year.
- 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 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%].