XIRR in Collabora Office: Calculate a Return from Dated Cash Flows
Use Collabora XIRR with paired dates and cash flows, audit its fixed-365 discount equation, and distinguish leap-year timing from regular-period IRR.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: For −100 on 2024-01-01 and 110 on 2025-01-01, XIRR has independently expected annual rate approximately 9.971358593%. The interval contains 366 actual days, while the documented annualization basis is fixed at 365 days.
Syntax and documented input rules
=XIRR(Values; Dates [; Guess])
Values and Dates are matching cash-flow and date series. The first date anchors the plan. The vendor requires every other date to be later than the first, although those later entries need not be sorted. Values must include at least one negative and one positive amount.
Guess is optional and defaults to 10%. The dedicated section states that the calculation uses a 365-day year, ignoring leap-year changes to the year denominator. This does not mean discarding February 29 from the actual elapsed-day count.
The source says text and empty cells in data ranges are ignored. Validate both paired ranges carefully; missing or text entries should not silently shift which amount belongs to which date. Use DATE to create genuine date values.
Example: a leap-year two-flow control
Enter A2 as =DATE(2024;1;1) and B2 as −100. Enter A3 as =DATE(2025;1;1) and B3 as 110. In D2 use:
=XIRR(B2:B3;A2:A3;0.1)
The independent date difference is 366 days. The rate solves −100+110/(1+r)^(366/365)=0, giving r≈0.099713585934.
A simple 10% holding-period gain is therefore slightly different from the fixed-365 annualized rate over this 366-day interval. If the interval were exactly 365 days, the corresponding independent two-flow annual rate would be exactly 10%.
Reproduce the vendor’s five-payment example
Enter these dates with DATE formulas and the corresponding amounts:
| Date | Cash flow |
|---|---|
| 2001-01-01 | −10000 |
| 2001-02-01 | 2000 |
| 2001-03-15 | 2500 |
| 2001-05-12 | 5000 |
| 2001-08-10 | 1000 |
With dates in A6:A10 and amounts in B6:B10, =XIRR(B6:B10;A6:A10;0.1) has documented result 0.1828, or 18.28%. An independent numerical solution gives approximately 18.284348582%, consistent with the source’s rounding.
The elapsed-day offsets are 0,31,73,131,221. They are not equally spaced, so assigning period numbers 0,1,2,3,4 would be a different timing model.
Advanced audit: reconstruct the dated present-value sum
For a calculated rate in D6, each payment’s independent discount term is:
=B6/(1+$D$6)^((A6-$A$6)/365)
Fill down through the five payments, then total the terms with SUM. The independent sum is approximately zero at the computed rate. The initial term has exponent zero and remains −10000.
Use unrounded rates and actual date values in this check. ABS can measure the residual, but choose tolerance relative to the cash-flow scale and required precision rather than expecting exact binary zero.
Advanced use: preserve date/value pairs when sorting
The vendor permits later dates in any order, provided they all remain later than the first date. Moving an amount together with its matching later date leaves the independent discounted sum unchanged. Sorting only the date column changes the cash-flow schedule and can produce a different rate.
Keep the earliest anchor first and sort complete rows when reorganizing a worksheet. Do not mistake a row-order cleanup for permission to alter the timing attached to a payment.
Errors and limitations
Check signs, the first-date anchor, actual date types, and pair alignment before adjusting Guess. The dedicated XIRR section does not provide a complete convergence/error-code table, so this guide does not import a code from IRR or another product.
Cash-flow patterns with several sign changes can have ambiguous or multiple mathematical roots; the guide does not certify native root selection for any Guess. XIRR does not model unlisted fees, taxes, or reinvestment, and its annual rate is a mathematical summary of the supplied dated flows rather than an investment recommendation.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.
The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.
References
- Collabora Office 26.04: XIRR (checked October 17, 2026).
- Editorial testing standards.