MDURATION

What it does

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) returns the modified duration of a coupon bond with an assumed par value of 100.

The definition is one division applied to Macaulay duration:

MDURATION = DURATION / (1 + yld/frequency)

and that division is what converts a time into a sensitivity. Where Macaulay duration answers "on average, how far away is my money", modified duration answers "if the yield moves, how much does the price move":

dP/dy = −MDURATION × P            equivalently        ΔP/P ≈ −MDURATION × Δy

So a modified duration of 7.2 means a one-percentage-point rise in yield costs roughly 7.2% of the price. It is a first-order statement: the price/yield curve is convex, so the linear estimate overstates losses on a rise and understates gains on a fall, and the error grows with the square of Δy. Excel has no convexity function to supply the second-order correction.

Units are years, inherited from DURATION; the quantity is often read as "per unit change in yield", which is dimensionally the same thing because yield is per year.

Microsoft's page names the result "Macauley modified duration" — with that spelling of Macaulay. It is worth knowing so that searches match.

Limiting cases follow DURATION's directly, damped by the divisor:

• Zero coupon: MDURATION is the time to maturity divided by 1 + yld/frequency, so it is strictly less than the time to maturity for any positive yield.
• yld = 0: the divisor is 1 and modified duration equals Macaulay duration exactly.
• yld → −frequency: the divisor approaches zero and the quantity diverges — but that region is excluded from the admissible domain long before it is reached, because yld < 0 is an error.

Arguments

Identical to DURATION, and the same three traps apply: coupon and yld are annual rates even at frequency = 2 or 4; frequency admits only 1, 2 and 4; dates and basis are truncated to integers.

The day-count basis values are the standard five: 0 or omitted = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

Because the extra division uses yld/frequency, the frequency argument enters the answer twice — once through the coupon schedule and once through the divisor. Getting it wrong therefore moves the result further than it moves DURATION.

Try it

Quirks

Result and edge cases

Returns Number — a modified duration in years.

• settlement ≥ maturity is a domain error.
• yld = 0 makes MDURATION and DURATION identical; that equality is a useful self-test.
• One remaining coupon: inherits DURATION's single-term branch, then divides.
• Every day-count and schedule subtlety of DURATION is present here unchanged. Modified duration adds no new calendar behaviour whatsoever; it adds one division. If an MDURATION answer looks wrong, check DURATION first — the fault is almost certainly upstream of the division.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

As documented on Microsoft's MDURATION page:

The yld < 0 restriction deserves the same note it gets on the DURATION page: a negative-yield bond has no modified duration in Excel, even though the sensitivity is perfectly well defined and is precisely the quantity a desk holding negative-yielding paper needs. This is a documented domain restriction, not a mathematical one.

How to implement

Numerical notes

The additional arithmetic is one division by 1 + yld/frequency, a quantity of order 1 for any admissible input. It introduces one rounding and no instability: there is no cancellation, no overflow, and the divisor cannot approach zero inside the admissible domain because yld < 0 is rejected.

That makes MDURATION a clean amplifier for whatever DURATION does. All the genuine numerical difficulty — the backwards coupon schedule with its end-of-month rule, the 30/360 accrual variants and the order in which their start-date and end-date adjustments apply, the two different pow stagings for integer and fractional discount exponents — belongs to DURATION and is discussed on that page. MDURATION inherits every one of them and can add at most one ULP of its own.

One consequence worth stating: because the two functions differ by an exactly specified division, a disagreement between an implementation's MDURATION and DURATION/(1 + yld/f) computed on the sheet is diagnostic. If the identity holds, any error is in the schedule or the discounting; if it fails, the implementation has computed modified duration by some other route.

Rust code

4 mduration_fn "MDURATION" (5, 6) [V "settlement", V "maturity", V "coupon", V "yld", V "frequency", V "basis"] |s, a, sv| {
    let (settlement, maturity) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?);
    bond_out(s, bond::mduration_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, num(s, a, 4)?, Some(or(s, a, 5, 0.0)?)))
}

pub fn mduration_kernel(
    settlement: f64,
    maturity: f64,
    coupon: f64,
    yld: f64,
    frequency: f64,
    basis_: Option<f64>,
) -> Result<f64, BondCoreEvalError> {
    let f = freq(frequency)? as f64;
    Ok(
        duration_kernel(settlement, maturity, coupon, yld, frequency, basis_)?
            / (1.0 + rate(yld)? / f),
    )
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• DURATION — Macaulay duration, the numerator. The relation MDURATION = DURATION/(1 + yld/frequency) is exact in the definition and is the natural metamorphic test for both functions at once.
• PRICE — the price whose sensitivity MDURATION measures. Together they give the standard first-order estimate ΔP ≈ −MDURATION × P × Δy, and comparing that estimate against a direct re-pricing at the shifted yield is the honest way to see how far first order gets you.
• YIELD — the usual source of yld.
• COUPNUM / COUPDAYBS / COUPDAYS — the schedule instruments for localizing a disagreement, exactly as for DURATION.
• Confused with: Macaulay duration itself. The two differ by a factor that, for a semi-annual bond at a 6% yield, is about 3% — small enough to look like a rounding difference and large enough to matter in a hedge ratio.
• Not available: convexity, the second-order term. Excel provides no function for it.

Named variants

No named variants are recorded yet.