XIRR Function (Calligra Sheets)

• Introduced: Original introduction release is not established. A checked release is not an introduction date.
• Source checked: Calligra Sheets 26.08.2, first-party implementation retrieved on October 18, 2026.
• Retired or deprecated: Neither is established. The function remains registered in the inspected source.
• Numerical evidence: The selected formulas below were executed in Calligra Sheets 25.04.2 on October 18, 2026, with formulas and results read back from a native-saved ODS. Independent calculations provide separate checks. Version 26.08.2 was source-checked only; these selected cases do not establish general import/export or cross-version runtime compatibility.
• Entry style: English names, semicolon separators, decimal points and DATE(year;month;day) values. Brackets in syntax indicate optional arguments; do not type them.

XIRR solves for the annual rate that makes the dated net present value of a cash-flow series zero. Dates need not be equally spaced. Cash outflows are negative and inflows positive. The functions use actual calendar-day offsets divided by 365, including when an interval contains a leap day; there is no optional day-count basis.

=XIRR(values; dates[; guess])

The function accepts two or three arguments, and its value/date arguments accept ranges. Both ranges must contain the same number of elements and at least two entries. The first date is the reference date; keep rows paired when sorting. Use actual date-valued cells, not date-looking text labels.

On a fresh worksheet enter these cells:

Enter =XIRR(H2:H3;I2:I3) in B2. The independent expectation is 0.1, or 10%. The dates are exactly 365 days apart. The discounted equation is therefore −1000 + 1100/(1+r); its root is r=0.1.

The general residual is:

sum(value_i / (1+rate)^((date_i−first_date)/365))

The initial cash flow has exponent zero, so it is included immediately rather than discounted one full period. This differs from periodic conventions that assume every supplied cash flow occurs at the end of a period. Dates determine the exponent, not the row number.

Enter −1000, 550 and 605 in J2:J4. In K2:K4 enter DATE formulas for January 1 of 2025, 2026 and 2027. The day offsets are 0, 365 and 730. At rate 0.1 the two positive present values are 550/1.1 = 500 and 605/1.1^2 = 500. Together they offset the initial −1000 exactly.

Use =XIRR(J2:J4;K2:K4;0.1) to solve this known-root case. Then substitute its result into the residual formula; agreement with zero is an independent check of the result’s meaning.

Move a real payment date only when the underlying transaction date changes. Moving the receipt later generally lowers its present value for a positive discount rate. Simply sorting one column without the other changes which amount belongs to which date and silently changes the financial model.

The inspected solver uses Newton’s method with a maximum of 50 iterations and tolerance 1e−10. Each step computes the current residual and its derivative, then updates the rate. Continuation requires both the rate change and the absolute residual to exceed tolerance; the loop can therefore stop when either is small. If the continuation flag still remains true after the iteration limit, it returns internal errorVALUE.

This is not a guarantee of convergence, uniqueness or financial relevance. The body does not explicitly guard a zero derivative or every nonfinite intermediate value. An iteration can enter invalid real-power territory or produce nonfinite arithmetic; comparisons involving nonfinite values need not provide a reliable failure signal. A returned number alone does not prove that the original cash-flow equation has been solved.

Use cash flows with at least one negative and one positive amount for an ordinary investment-return problem. Inspect the residual at the returned rate and use a scale-appropriate tolerance. Compare a few sensible initial guesses when the timing/sign pattern can admit multiple roots; never claim that a different guess finds every root. The one-year example with guesses 0.1 and 0.2 should reach the same known 10% solution, but that simple case does not establish global solver robustness.

Mismatched range lengths, fewer than two pairs and an initial guess at or below −1 explicitly return internal errorVALUE. The mismatched third-date range and a guess of −1 both displayed #VALUE! in the selected native cases. Keep the ranges aligned and start with a valid guess.

The implementation’s date validation is incomplete: the helper converts dates without the same explicit per-element validity checks found in XNPV. Validate all date cells before solving, and avoid assuming that blanks, text or invalid dates are safely ignored. Same-date flows and a zero derivative can make the problem unidentifiable; change the model or report that no meaningful unique annual return has been established.

Enter each formula on a fresh worksheet. Format numeric results with at least ten decimal places before comparing; percentage formatting multiplies the displayed number by 100. The errors for these selected formulas were also observed in the native application.

These selected formulas were recalculated in the native application. The formulas below use worksheet notation; native ODS range references may serialize differently. Display precision can differ from the independent arithmetic.

The date inputs in I2:I4 and K2:K4 are actual DATE formulas. The screenshot uses numeric formatting, so January 1 of 2025, 2026 and 2027 appears as serials 45658, 46023 and 46388. These are calculated date serials, not typed date labels. The DATE formulas and resulting serials were verified in the saved workbook.

Calligra Sheets 25.04.2: selected cell B2 evaluates =XIRR(H2:H3;I2:I3) and displays 0.1000000000. Column B contains calculated results; column C shows entered formula text. The date-input columns show numeric serials rather than formatted calendar dates.

The registered alternate spelling is COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXIRR. It uses the same handler; it is not a separate calculation. Matching names in other spreadsheet products do not guarantee matching argument order, day counts, validation or file-import behavior. This guide does not establish general XLSX/ODS round-trip compatibility. Check a small workbook with known results before relying on a migrated model.

Related guides: DURATION and PDURATION cover compound-growth periods; PMT handles equal periodic payments; DATE constructs date values. Keep investment returns and asset depreciation in separate models.

• Calligra released function implementation, including the actual argument reads and return expressions.
• Calligra function descriptions, useful for terminology but subordinate to the inspected implementation where they differ.
• Calligra year-fraction implementation, relevant to basis-dependent year fractions.

References checked October 18, 2026. Numerical examples are spreadsheet calculations, not a recommendation to buy, sell or price a particular security without checking its contractual terms.