DURATION Function in Google Sheets: Formula, Examples & Troubleshooting

This guide follows Google’s detailed notes identifying DURATION as Macaulay duration: the present-value-weighted time, in years, of a regular coupon security’s receipts. It is distinct from the final maturity date and from modified price sensitivity.

Google’s overview is contradictory. Its lead describes a compounding-growth-period calculation, while the coupon-security syntax and detailed DURATION/MDURATION notes identify Macaulay duration. This guide follows those detailed notes; it does not claim that all Google descriptions agree. Google DURATION reference, Google MDURATION relationship.

Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Every numerical result below is independently calculated for complete regular coupon periods. No native Sheets output or accessible vendor example is claimed. This is a formula audit, not an investment recommendation.

=DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])

Google lists 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. Supply actual date values, such as DATE-created cells. The numerical audits here use explicit basis 0 only. Google argument reference.

Coupon rate and yield have separate jobs: the coupon determines the receipts; the yield discounts them. Enter four percent as 4% or 0.04, not 4.

A hypothetical security has three complete annual periods remaining, pays a 4% coupon, and is valued at a 6% yield. Set up:

The planned formula in B9 is:

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

The independently expected result is 2.882655161 years, rather than the three years to final maturity. The model starts on a regular coupon boundary; no fractional first period is inferred.

Use a 100-face-value model, with receipts of 4, 4, and 104. Enter years 1, 2, and 3 in D2:D4 and those receipts in E2:E4. In F2 enter =E2/(1+$B$5)^D2 and fill through F4. In G2 enter =D2*F2 and fill through G4.

In H2 enter =SUM(G2:G4)/SUM(F2:F4). The result is 2.882655161. Use unrounded cell values even though the table displays nine decimal places.

To see the weights directly, enter =F2/SUM($F$2:$F$4) in I2 and fill through I4. The weights are approximately 3.986716%, 3.761053%, and 92.252232%. They sum to 100%. The final receipt dominates because it includes the principal, but the earlier coupons pull the weighted time below maturity.

This is not the arithmetic mean of 1, 2, and 3, nor the time needed to recover the purchase price through undiscounted receipts. Each time receives a weight based on its discounted cash flow.

Now model two complete years, a 5% annual coupon, a 4% annual yield, and two payments per year:

=DURATION(DATE(2026,4,15),DATE(2028,4,15),0.05,0.04,2,0)

There are four receipts: 2.5, 2.5, 2.5, and 102.5. Discount each by (1+0.04/2)^k, where k runs from 1 through 4. The receipt times in years are k/2: 0.5, 1, 1.5, and 2.

The last two rows describe the same weighted time in different units. Using k rather than k/2 in the numerator produces half-year periods, so divide by two before reporting years. Do not report 3.857565844 as the duration in years.

For one complete year, an 8% coupon, a 5% yield, and quarterly payments:

=DURATION(DATE(2026,4,15),DATE(2027,4,15),0.08,0.05,4,0)

The independent receipts are 2, 2, 2, and 102 at 0.25, 0.5, 0.75, and 1 year. Discount at 0.05/4 per quarter. The weighted-time result is 0.971444045 years.

Google’s detailed relationship is MDURATION = DURATION/(1+yield/frequency). Put the unrounded quarterly modeled result in B12 and calculate =B12/(1+0.05/4). This gives 0.959450909 for modified duration. That is a different measure; use the MDURATION guide for price-sensitivity interpretation. Google MDURATION notes.

• A factor-of-two or factor-of-four difference: check whether the weighted time is still in coupon periods rather than years.
• An implausibly large discount: check percent-versus-integer input and divide annual yield by frequency only inside the cash-flow audit. The DURATION argument itself is annual yield.
• A different result after changing dates: this audit does not cover fractional settlement periods, irregular coupons, month-end adjustments, or leap-year basis behavior.
• A formula error: inspect the argument order, date storage, and documented frequency values. Exact native error tokens and coercion rules were not established.
• A growth-horizon question: PDURATION has the distinct three-argument rate/present-value/future-value task; do not substitute that signature here.

All modeled coupons and yields are positive. The five documented bases are listed for contract completeness, not as a claim that every basis, negative yield, date boundary, or validation branch was tested.

The conflicting overview remains disclosed rather than silently corrected by borrowing another product’s semantics. Introduction, last working version, retirement, and cross-product parity are unestablished. Current documentation is not proof of a particular native engine build.

Related: DATE, SUM, and PRICE.

First-party references reviewed October 18, 2026: DURATION, MDURATION, and the Google function catalogue, whose DURATION description repeats the overview conflict while listing the coupon-security signature.