MDURATION in Excel: Calculate Modified Bond Duration

=MDURATION(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 the following in B9 and format it as a Number:

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

Expected result: 2.6730119495 years. Omitting the final basis argument is expected to produce the same result, because the documented default is 0.

Independently verify the result

The three future cash flows are 6, 6 and 106 at years 1, 2 and 3. Their discounted values at 6% total 100. Macaulay duration is their present-value-weighted payment time:

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

This gives 2.8333926664 years. Modified duration is Macaulay duration divided by 1 + yld/frequency, which here is 1.06. The result is 2.6730119495. The full computation is independent arithmetic; the displayed decimal is an expected Excel result rather than an observed run.

As a worksheet cross-check, the following should agree with B9, allowing for display rounding:

=DURATION(B2,B3,B4,B5,B6,B7)/(1+B5/B6)

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

Expected result: 1.8841406873 years. The four cash flows are 2, 2, 2 and 102 at half-year intervals, discounted at 3% per period. Their present-value total is approximately 96.2829015972, and their Macaulay duration is 1.9406649079 years. Divide by 1.03, not 1.06, to obtain modified duration.

The formula still receives 4% and 6% as annual rates. Passing 2% and 3% with frequency 2 would describe a different bond and yield, not an equivalent form of these inputs.

For the annual example on its coupon-date settlement, price per 100 is 100. The present-value model gives this first-order relationship:

Approximate proportional price change = -modified duration × change in annual yield

For a rise from 6% to 6.25%, the yield change is 0.0025, or 25 basis points. With modified duration 2.6730119495, the estimated proportional price change is approximately -0.0066825299, or -0.66825299%. The corresponding price estimate is approximately 99.3317470126 per 100.

You can calculate that estimate using the output in B9:

=100*(1-B9*(6.25%-6%))

An independent full repricing of these same cash flows is:

=6/1.0625+6/1.0625^2+106/1.0625^3

It gives approximately 99.3348259719 per 100. The small difference illustrates the curvature omitted by a first-order duration estimate. Do not present the approximation as an exact quote. Larger changes, changes in cash flows, embedded options and changes in credit conditions require a more complete model. This is an educational sensitivity calculation, not a recommendation to trade.

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

Expected result: 0.9519321747 years, from Macaulay duration 0.9709708182 divided by 1+8%/4, or 1.02.

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

Expected result: 2.9126213592 years, from the three-year zero-coupon Macaulay duration divided by 1.03. Unlike its Macaulay duration, this zero-coupon bond’s modified duration is not exactly its remaining maturity when yield is positive.

Microsoft’s MDURATION 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 MDURATION 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.