DURATION in Excel: Calculate Macaulay Bond Duration

=DURATION(settlement, maturity, coupon, yld, frequency, [basis])

Both rates are annual. For semiannual payments, pass annual rates and frequency 2; do not halve the rates yourself. Entering 6 rather than 6% means 600%. There is no face-value or redemption argument: Microsoft specifies an assumed par value of 100.

Use the convention specified for the security rather than choosing the one that gives a preferred result. US and European 30/360 are distinct conventions. The examples here use basis 0 and settlement on a regular coupon date; they do not establish month-end adjustment behavior for other schedules.

Use DATE or genuine Excel date cells rather than locale-ambiguous text such as 1/2/2027. Microsoft states that settlement, maturity, frequency and basis are truncated to integers. Time-of-day fractions therefore do not request intraday precision. Supply whole-number frequency and basis codes deliberately. Settlement must be strictly earlier than maturity.

Enter this input block:

In B9, enter:

=DURATION(B2,B3,B4,B5,B6,B7)

Expected result: 2.8333926664 years. Format B9 as a Number, not a date or percentage. Omitting B7 from the formula is expected to give the same result because omitted basis defaults to 0.

Reconstruct the answer from cash flows

Settlement is exactly on a coupon date. Future payments are 6 after one year, 6 after two years, and 106 after three years. The last payment includes principal.

For each row, divide the cash flow by 1.06 raised to its year number. Multiply each present value by its payment time, add those weighted amounts, then divide by the total present value:

=(1*6/1.06+2*6/1.06^2+3*106/1.06^3)/(6/1.06+6/1.06^2+106/1.06^3)

The independent result is again 2.8333926664. Earlier coupon payments explain why duration is less than the three-year maturity. This calculation uses full precision, not the rounded table values.

=DURATION(DATE(2027,1,15),DATE(2029,1,15),4%,6%,2,0)

Expected result: 1.9406649079 years. There are four future payments: 2, 2, 2 and 102. Discount them at 3% per half-year. Their present values total approximately 96.2829015972. Weight those values by 0.5, 1, 1.5 and 2 years, then divide by the present-value total. Using period numbers 1–4 without dividing by frequency would incorrectly report half-year periods as years.

With an 8% annual coupon and yield, quarterly payments and one year remaining:

=DURATION(DATE(2027,1,15),DATE(2028,1,15),8%,8%,4,0)

Expected result: 0.9709708182 years. The four payments are 2, 2, 2 and 102, discounted at 2% per quarter, with times 0.25, 0.5, 0.75 and 1 year. This is a useful control that distinguishes annual rates from periodic rates.

For the original three-year dates with zero coupon:

=DURATION(DATE(2027,1,15),DATE(2030,1,15),0,6%,2,0)

Expected result: 3 years. Only principal at maturity has positive value, so its time receives all the weight. This illustrates why duration is a weighted time rather than simply another name for maturity: they coincide in this particular zero-coupon case.

For sensitivity work, modified duration divides the Macaulay measure by 1 + annual yield/frequency. For the annual example, 2.8333926664/1.06 is approximately 2.6730119495. Use the dedicated MDURATION function for that measure. PRICE addresses the separate question of price per 100; do not assume that duration itself is a price or total settlement amount.

Microsoft’s DURATION reference documents these conditions:

Change one input at a time when diagnosing a failure. These are documented expected errors, not native test observations; no precedence is asserted when several arguments are invalid. Zero rates are not excluded by the documented negative-rate rule. Required coupon, yield and frequency arguments have no documented defaults, and blank-cell coercion is not used in these examples.

If the output looks like a date, apply a Number format: the returned measure is a number of years. If it looks plausible but differs from another calculation, compare annual-rate units, coupon frequency, settlement date and basis before increasing displayed precision. A duration calculation for a regular coupon schedule is not a general model for callable securities, irregular cash flows or an arbitrary redemption value.

These formulas use English function names and commas. Localized Excel may use translated names or semicolon separators.

The current Microsoft DURATION reference, checked 19 October 2026, lists Excel for Microsoft 365 on Windows and Mac, Excel 2024 and 2021 on Windows and Mac, and Excel 2019 and 2016. These are documentation applicability labels, not a statement of vendor lifecycle support. The first release containing this function is not established here. The current English page does not list Excel for the web; that omission alone does not establish that web Excel lacks the function.

The examples in this guide are independently derived expectations based on the documented contract, not recorded Excel runs. No Excel version was available for native testing, and no screenshot is presented as execution evidence. Financial examples use regular coupons with settlement exactly on a coupon date; between-coupon and month-end conventions require their own checks.