MDURATION in Collabora Office: Estimate Small Yield-Change Sensitivity

=MDURATION(Settlement; Maturity; Coupon; Yield; Frequency [; Basis])

The day-count choices are 0 = US NASD 30/360; 1 = actual days/actual year; 2 = actual days/360; 3 = actual days/365; 4 = European 30/360. Basis 0 and 4 have different month-end adjustments. Basis 1 uses the source’s actual-year rules, including leap-year and multi-year averaging logic; it is not simply actual days divided by 365. Do not substitute a basis because its label sounds similar.

The date interface uses whole-day serial dates. Avoid ambiguous date strings and time-of-day fractions. These functions provide no issue date, first coupon, redemption amount, or irregular-coupon arguments.

Enter B2 =DATE(2027;1;1), B3 =DATE(2030;1;1), B4 6%, B5 8%, B6 2, and B7 0. In B9 use =MDURATION(B2;B3;B4;B5;B6;B7). The independent result is 2.676020329150.

In B10 enter =DURATION(B2;B3;B4;B5;B6;B7)/(1+B5/B6). The underlying Macaulay target is 2.783061142316, and dividing by 1.04 gives the modified result. Both functions must use identical dates, rates, frequency, and basis.

For a manual discounted-cash-flow check, use payments 3, 3, 3, 3, 3, and 103 per 100 principal at half-year numbers 1 through 6. Divide each payment by 1.04 raised to that half-year number. Sum the time-weighted present values, divide by their total, then divide by 1.04.

The pinned source counts coupons on a regular maturity-anchored schedule, stepping backward by 12/Frequency months to the coupon date on or before settlement. End-of-month handling belongs to that date helper; do not generate a separate schedule by adding a fixed number of days.

More specifically, let m be Frequency, N the remaining coupon count, T the source’s year fraction from Settlement to Maturity under Basis, and d = Tm − N. For coupon number k=1 through N, the time exponent is k+d, the payment is 100Coupon/m, and the last payment also includes 100 principal. The discounted weight is payment/(1+Yield/m)^(k+d).

DURATION is the sum of time-in-years times discounted weight, divided by the sum of discounted weights. The time in years is (k+d)/m. This explains both the regular-coupon behavior and the source-specific off-coupon time offset. It is not safe to replace T by a coupon-period fraction from another product and assume identical results. The principal of 100 is a normalization, so there is no face-value argument.

MDURATION applies one additional division by 1+Yield/Frequency to that weighted timing. The source therefore retains the same year-fraction and coupon-count conventions; it does not construct an independent schedule.

Put 0.25% in B12 to represent a 25-basis-point yield increase. In B13 use =-B9*B12 and format as a percentage. The independent change estimate is −0.669005082287%. For a hypothetical current model value of 100, =100*(1+B13) is approximately 99.330994917713.

This is a first-order approximation with unchanged cash flows and the same yield convention. It is not a promise about market prices. Larger yield moves need a full repricing and may make curvature important; a callable bond also changes the economic assumptions.

For settlement on 2027-04-01 with the other inputs unchanged, the independent result is 2.435635713765. A zero-coupon version of the original three-year example gives 3/1.04 = 2.884615384615, showing why modified duration is not simply remaining calendar time.

The pinned implementation rejects negative Coupon or Yield, unsupported Frequency, and invalid Basis. Settlement must precede Maturity; the shared coupon helper rejects equality or reversal. Zero coupon and zero yield are allowed by these checks. Nonfinite calculated results are rejected.

Enter 6% rather than 6 for a six-percent rate. Use integer Frequency and Basis values, numeric date cells, and General/Number formatting for the result. A date format can make a valid duration appear to be a calendar date. Exact localized error labels, fractional-argument conversion, and extreme-value numerical behavior have not been native-tested.

This regular fixed-coupon model is unsuitable for a floating-rate instrument, irregular first/last coupons, embedded options, or cash flows that change when yields change. A mathematically valid duration is not a complete risk measure.

Use DURATION for the weighted timing before yield adjustment. Use XNPV for explicit dated cash flows when a regular coupon schedule does not describe the problem; its discount convention is different.

Reviewed on 19 October 2026 against saved Collabora Office 26.04 Help retrieved on 17 October 2026 and the Collabora co-26.04 source snapshot at commit c1013fad0d38683630b48c01b6d5208b8d81181e. The repository is hosted under LibreOffice/core, but the evidence used here is the pinned Collabora branch snapshot, not a claim about another spreadsheet product. The Help URL marked latest can change. Fresh Help retrieval was unavailable during this review, so Help statements were checked against the saved first-party pages; the pinned implementation files were freshly retrieved and verified.

Examples are independently calculated expectations, not recorded Collabora output. Native Collabora and its GUI were unavailable for this review; no screenshots or application runs are claimed. The first release introducing this function and the last working binary version have not been established. Online, mobile, older releases, localized function names, and round-trip compatibility need separate verification. Formulas use English names and semicolon separators; use the separators configured in your installation.

• Collabora Help: MDURATION — reviewed 19 October 2026.
• Pinned Collabora MDURATION implementation — reviewed 19 October 2026.
• Pinned duration, year-fraction, and coupon helpers — reviewed 19 October 2026.