How to Calculate Macaulay Duration in Excel

By
Nick Lioudis
Full Bio
Nick Lioudis is a writer, multimedia professional, consultant, and content manager for Bread. He has also spent 10+ years as a journalist.
Learn about our editorial policies
Updated August 20, 2022
Reviewed by
JeFreda R. Brown
Dr. Brown
Reviewed by JeFreda R. Brown
Full Bio
Dr. JeFreda R. Brown is a financial consultant, Certified Financial Education Instructor, and researcher who has assisted thousands of clients over a more than two-decade career. She is the CEO of Xaris Financial Enterprises and a course facilitator for Cornell University.
Learn about our Financial Review Board
Fact checked by
Vikki Velasquez
Vikkie Velasquez
Fact checked by Vikki Velasquez
Full Bio
Vikki Velasquez is a researcher and writer who has managed, coordinated, and directed various community and nonprofit organizations. She has conducted in-depth research on social and economic issues and has also revised and edited educational materials for the Greater Richmond area.
Learn about our editorial policies

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."

Open a New Bank Account
×
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.
Sponsor
Name
Description