MDURATION Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate modified bond duration with MDURATION in Google Sheets. Audit a two-year coupon model and use duration for a clearly labeled small-yield-change estimate.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =MDURATION(settlement,maturity,rate,yield,frequency,basis) calculates modified duration for a periodic-interest security. It measures sensitivity to yield changes; it is not a coupon amount or simply the number of years until maturity.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google documentation was reviewed October 17, 2026. Duration, discounted cash flows, and sensitivity estimates below were independently calculated, not obtained from a native Sheets run.
Syntax and arguments
=MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
| Argument | Meaning |
|---|---|
settlement |
Security delivery date |
maturity |
Final redemption date |
rate |
Annual coupon interest rate |
yield |
Expected annual yield |
frequency |
Coupon payments per year: 1, 2, or 4 |
day_count_convention |
Optional basis, default 0 |
Google names bases 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360, and recommends actual date values. It explicitly relates modified duration to Macaulay duration by dividing the latter by 1+yield/frequency. Google MDURATION reference.
The coupon rate and yield have different roles. A bond may pay a 6% coupon while being valued at a 5% yield. Supply rates as percentages or decimal equivalents, not the integer 6 for six percent.
Example: two complete annual coupon periods
Assume a regular hypothetical security has two remaining annual payments, one year and two years after settlement. The coupon is 6 per 100 face value; the last payment is 106 including redemption.
| Cell | Label | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,1,15) |
| B3 | Maturity | =DATE(2028,1,15) |
| B4 | Coupon rate | 6% |
| B5 | Yield | 5% |
| B6 | Frequency | 1 |
| B7 | Basis | 0 |
- Enter
=MDURATION(B2,B3,B4,B5,B6,B7)in B9. - Display B9 as a number with six decimals, not a percentage.
- The independently calculated expected modified duration is approximately 1.851334.
- Keep this full-period example separate from a settlement partway through a coupon period, which requires additional fractional-period modeling.
Audit with discounted cash flows
Create D1:G3 with columns Time in years, Cash flow, Present value, and Time-weighted present value:
| Time | Cash flow | Present value at 5% | Time-weighted value |
|---|---|---|---|
| 1 | 6 | 5.714285714 | 5.714285714 |
| 2 | 106 | 96.145124717 | 192.290249433 |
Enter 1 and 2 in D2:D3 and 6 and 106 in E2:E3. In F2 use =E2/(1+$B$5)^D2; in G2 use =D2*F2. Fill both down.
The present values sum to approximately 101.859410431. The independent Macaulay duration is the weighted-time sum divided by that present value, approximately 1.943900267 years. Divide again by 1+B5/B6 to obtain approximately 1.851333588.
This is a mathematical audit of the stated regular cash-flow model and Google’s documented relationship. It is not a claim about internal source code or undocumented date-boundary implementation.
Advanced application: estimate a small yield change
A first-order modified-duration estimate uses:
Approximate proportional price change = -modified_duration * yield_change
For a rise from 5% to 5.5%, the yield change is 0.005, or half a percentage point. With the modeled duration, the approximate proportional price change is -0.925667%.
An independent repricing of the two stated annual cash flows at 5.5% gives approximately 100.923159857. Relative to 101.859410431, the exact model change is about -0.919160%. The difference is expected because the duration estimate is linear while the discounted-price relationship is curved.
This comparison is an educational sensitivity calculation, not a price forecast. It holds cash flows fixed and does not model credit changes, embedded options, fees, or a changing yield curve. Larger yield changes require more care than simply multiplying by duration.
Errors and interpretation traps
Result formatted as percent: modified duration is a sensitivity measure, not an annual return. Keep the input yield percentage-formatted and the duration result numeric.
Wrong rate supplied: distinguish the contractual coupon rate from the valuation yield. Changing one is not equivalent to changing the other.
Frequency entered as months: semiannual means 2 payments per year, not 6. The documented choices are 1, 2, and 4.
A shortcut gives years to maturity: two years to maturity is not the same as the expected 1.851334 modified duration for this coupon-bearing model.
Fractional periods or irregular coupons: the example assumes complete regular annual periods. The reviewed help does not provide a full fractional-period algorithm or irregular-coupon contract. Verify those cases before relying on a valuation model.
Errors and unusual inputs: use meaningful dates, documented basis and frequency values, and clearly entered rates. Exact native error codes, negative-yield limits, and coercion behavior were not established from the source.
Support and references
Google currently documents MDURATION. Its introduction, retirement, and last working version remain unknown from the reviewed evidence. The guide does not assume identical behavior in another spreadsheet engine.
Reviewed October 17, 2026: Google MDURATION and Google DATE.