The DURATION function in Excel calculates the Macaulay duration of a bond, which measures its price sensitivity to interest rate changes. You provide settlement and maturity dates, a coupon rate, a yield, and payment frequency, and Excel returns the duration in years. This guide walks through the syntax, a real example, common pitfalls, and fixes.
What You’ll Need
- Excel (2010 or later; the function is also in Google Sheets)
- The bond’s settlement date (when you buy it)
- The bond’s maturity date (when it pays back principal)
- Annual coupon rate (as a decimal or percentage)
- Annual yield to maturity (also as a decimal or percentage)
- Payment frequency per year (1, 2, or 4)
- Optional: day count basis (defaults to US 30/360)
Step-by-Step
- Open your worksheet and enter the bond details in separate cells. For example: A1 = settlement date, A2 = maturity date, A3 = coupon rate, A4 = yield, A5 = frequency.
- Format the dates as actual Excel dates (not text). Use
=DATE(2025,1,15)or type them and set the cell format to Date. - Click an empty cell where you want the duration to appear.
- Type the formula:
=DURATION(A1, A2, A3, A4, A5). If you have a basis value, add it as a sixth argument:=DURATION(A1, A2, A3, A4, A5, 0). - Press Enter. Excel returns the Macaulay duration in years (e.g., 7.52).
- Format the result with 2–4 decimal places via the Home tab → Number Format.
- Verify the output: duration should be less than the years to maturity for a coupon bond, and equal to maturity for a zero-coupon bond.
Common Mistakes
- Entering dates as text — Excel can’t compute with text dates; use DATE() or valid date cells.
- Using percentage signs inconsistently — If you type 5% for the coupon, Excel treats it as 0.05. If you type 5, it treats it as 500%. Always use the percent format or decimal.
- Reversing settlement and maturity — Settlement must be before maturity, or you get a #NUM! error.
- Forgetting the frequency argument — It’s required; omitting it returns #VALUE!.
- Using the wrong basis — The default (0) is US 30/360. If your bond uses actual/actual, you need basis 1.
Tips
- Use cell references instead of typing numbers directly into the formula — easier to update and audit.
- For modified duration (which is more directly related to price change), divide the result by (1 + yield/frequency).
- Use the
MDURATIONfunction if you specifically need modified duration. - If you have multiple bonds, set up a table with columns for each input and drag the formula down.
- Remember that duration assumes a flat yield curve and small parallel shifts — it’s an approximation.
Troubleshooting
#VALUE! error — Usually means one of the arguments is not numeric or a date is text. Check that settlement and maturity are real dates (select the cell, look at the formula bar — it should show a date serial number).
#NUM! error — The most common cause is settlement ≥ maturity. Also check that coupon and yield are positive numbers. If frequency is not 1, 2, or 4, you’ll get this error.
Result seems too high or low — Verify that coupon and yield are in the same format (both decimals or both percentages). A 5% coupon should be 0.05 or 5%, not 5.
Result not updating — If you changed inputs but the result stays the same, check that calculation is set to Automatic (Formulas tab → Calculation Options).
Frequently Asked Questions
What is the difference between DURATION and MDURATION? DURATION returns Macaulay duration (weighted average time to receive cash flows). MDURATION returns modified duration, which is Macaulay duration divided by (1 + yield/frequency) — it approximates the percentage price change for a 1% yield change.
Can I use DURATION for a zero-coupon bond? Yes. Set the coupon rate to 0%. The duration will equal the years to maturity (approximately).
What does the basis argument do? It sets the day count convention. 0 = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360. The default is 0.
Does DURATION work in Google Sheets? Yes, the same syntax works in Google Sheets.
Why is my duration longer than the maturity? That shouldn’t happen for a standard bond. Double-check your dates and that coupon is not negative. If you have a very high yield and negative coupon, it might, but that’s unusual.
Can I use DURATION for bonds with irregular first or last periods? The function assumes regular periods. For odd periods, you’d need a more complex calculation or a financial add-in.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Hogwarts Legacy Microsoft Visual C Runtime Error Fix
- Set Up UPI Payments Through WhatsApp
- Pair Oppo Enco Earbuds with Android, iPhone & Windows
- Fix Administrator Account Disabled Error in Windows 11
- How to Play Kodi on Smart TV from PC
- Fix DISM Error 1726: Remote Procedure Call Failed in Windows 10