DURATION in Collabora Office: Measure Fixed-Coupon Bond Duration

=DURATION(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 the following inputs:

In B9 enter =DURATION(B2;B3;B4;B5;B6;B7). The independent target is 2.783061142316. Settlement is on the regular coupon schedule, so six future payments occur at 0.5, 1, 1.5, 2, 2.5, and 3 years. Per 100 principal, the first five payments are 3 and the final payment is 103. Discount each at 4% per half-year.

To reproduce the arithmetic in cells, put payment numbers 1 through 6 in D2:D7. In E2 enter =IF(D2=6;103;3), F2 =E2/(1+8%/2)^D2, and G2 =(D2/2)*F2; fill down. Then =SUM(G2:G7)/SUM(F2:F7) gives the same mathematical target. Keep unrounded intermediates.

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.

Change B2 to =DATE(2027;4;1), leaving maturity, rates, frequency, and Basis 0 unchanged. T becomes 2.75, N remains 6, and d is −0.5. The independent duration becomes 2.533061142316 years. This example deliberately uses ordinary first-of-month dates to isolate the time offset without month-end ambiguity.

As a boundary check, =DURATION(DATE(2027;1;1);DATE(2030;1;1);0;8%;2;0) has independent result 3 years: all value arrives at maturity. With the original 6% coupon and zero yield, the target is 2.809322033898 years.

A shorter duration means the discounted payments arrive earlier on average. For local price sensitivity use MDURATION, which divides this result by 1+Yield/Frequency. PDURATION solves a compound-growth waiting time and is a different calculation.

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.

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: DURATION — reviewed 19 October 2026.
• Pinned Collabora financial entry points — reviewed 19 October 2026.
• Pinned duration, year-fraction, and coupon helpers — reviewed 19 October 2026.