• General
• Personal Finance
• Reviews & Ratings
• Wealth Management
• Popular Courses
• Courses by Topic

# How to Calculate Macaulay Duration in Excel

The modified duration of a bond is an adjusted version of the Macaulay duration and both methods are 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%].

Article Sources
Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
1. Federal Reserve Bank of St. Louis. "Investment Improvement: Adding Duration to the Toolbox."

2. Microsoft Office Support. "MDURATION Function."

Take the Next Step to Invest
×
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.
Service
Name
Description