XIRR Function (LibreOffice Calc)
Learn the XIRR function in LibreOffice Calc: Returns the internal rate of return for a non-periodic schedule of payments.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
Testing scope (13 October 2026): The numerical examples and diagnostics identified as native results come from a recorded LibreOffice Calc 25.2.3.2 Linux run. Its saved formulas and results were checked against the retained workbook, and the numerical examples were independently recalculated. No new Calc run was performed for this review. Current online Help displays 26.8; that application version and other platforms were not tested.
XIRR calculates the annualized internal rate of return for an investment whose deposits, withdrawals, and proceeds occur on actual calendar dates rather than at evenly spaced intervals. Unlike simply dividing total profit by the original investment, XIRR recognizes when each cash flow occurred. A return of 18% means an annualized, compounded discount rate that makes the dated cash flows’ present value zero; it does not promise that the investment earned exactly 18% cash profit in the sample period.
Syntax and return type
=XIRR(Values; Dates [; Guess])
| Parameter | Required | Meaning and handling |
|---|---|---|
Values |
Yes | A one-dimensional series of cash flows: negative amounts paid out (investments/expenses) and positive amounts received. At least one amount of each sign is necessary. Keep cash flows in the same rows as their dates. |
Dates |
Yes | Real Calc dates, one for each cash flow. The first date is the investment’s anchor and all other dates should be on or after it; later entries do not have to be sorted chronologically. |
Guess |
No | A starting estimate for the iterative annualized rate. Default 0.1 (10%) if omitted. Try a different informed guess if the solver cannot find a root. It is not a guaranteed answer, bounds check, or maximum rate. |
Function names in English Calc use XIRR, and the standard English example uses semicolons between arguments. The user’s locale affects how decimals, function names, separators and dates are displayed/entered. For portable examples use DATE(year;month;day) or properly formatted date cells, and 10% or the locale-correct equivalent of 0.1. Do not type a date-looking string and assume it is a valid date serial. See the LibreOffice financial-function help and OpenFormula §6.12.51.
Result: a decimal annual rate. For example 0.1828434858 appears as 18.28% when the result cell is formatted as a percentage. Returns may be negative. The solver finds an approximate root rather than a fixed closed-form answer, so tiny floating-point differences are normal. OpenFormula §6.12.51, specification published 2025-10-06; consulted 2026-10-13.
Worked example: the official irregular-payment schedule
Enter these five pairs into a new Calc sheet, starting on row 1. Put each date in column A as a real date (you can type the ISO date and verify Calc recognizes it, or use the explicit DATE expression supplied below). The figures follow the official LibreOffice Help example; -10000 is the initial cash outflow, not a negative interest rate.
| Cell A: Date | Cell B: Cash flow | How to enter column A reliably |
|---|---|---|
| A1: 2001-01-01 | B1: -10000 | =DATE(2001;1;1) |
| A2: 2001-02-01 | B2: 2000 | =DATE(2001;2;1) |
| A3: 2001-03-15 | B3: 2500 | =DATE(2001;3;15) |
| A4: 2001-05-12 | B4: 5000 | =DATE(2001;5;12) |
| A5: 2001-08-10 | B5: 1000 | =DATE(2001;8;10) |
In an empty cell enter:
=XIRR(B1:B5;A1:A5)
Expected/native Calc result: approximately 0.182843485820784, or 18.2843485820784% before rounding. Displayed with two percentage decimals: 18.28%. Adding the explicit guess, =XIRR(B1:B5;A1:A5;10%), produced the same result in native Calc 25.2.3.2. LibreOffice Help rounds this example to 18.28%. Help example checked 2026-10-13.
Interpretation: the answer is the annual rate that discounts the five dated values to a combined net present value of approximately zero. It is not the simple 500/10000 = 5% total profit divided by days. A short holding period can create a much larger annualized rate than total percentage profit because time matters.
Verify the answer with XNPV — an important audit step
Using the same rows A1:A5 and B1:B5:
=XNPV(XIRR(B1:B5;A1:A5);B1:B5;A1:A5)
The result should be approximately 0 (a negligible floating-point residual, not always an exact stored zero). The matching calculation was verified in the native test workbook using the computed XIRR rate. If the result is materially far from zero, inspect the cash-flow signs, date alignment, and whether the range includes an unexpected row. OpenFormula §§6.12.51–52, 2025-10-06.
You can also put the return from XIRR in D1 and use =XNPV(D1;B1:B5;A1:A5) to obtain the same check. Conversely, the original example’s cash flows discounted at an assumed 6% (not their implied return) give =XNPV(6%;B1:B5;A1:A5) ≈ 323.016893587303. An XIRR of 18.28% and an XNPV of 323.02 at 6% are therefore compatible, not contradictory.
What XIRR actually solves
Let C1 … Cn be the flows and let d1 … dn be their associated dates. XIRR seeks an annual rate r such that
0 = Σ[ Ci / (1 + r)^((di − d1) / 365) ]
The exponent uses the actual number of calendar days between each date and the first date divided by 365. It does not switch the divisor to 366 in leap years. The formula assumes a rate above -100% for its usual real-valued financial interpretation. XIRR uses numerical iteration; if there is no suitable root or iteration cannot converge, an error may be returned. LibreOffice Help XIRR and XNPV; OpenFormula §§6.12.51–52, consulted 2026-10-13.
Reproducible two-payment check
For A1 =DATE(2023;1;1), B1 -1000, A2 =DATE(2024;1;1), B2 1100, the two dates are 365 days apart. Enter =XIRR(B1:B2;A1:A2) to get 0.10 or 10%. Independently, -1000+1100/(1+0.10)^(365/365)=0.
Leap-year pitfall: change A1 to =DATE(2024;1;1) and A2 to =DATE(2025;1;1): these dates are 366 days apart. Native Calc returned approximately 0.0997135859341414 or 9.9713585934%, rather than exactly 10%, because the annualization base remains 365 days. The independent answer is (1100/1000)^(365/366)-1, not 1100/1000-1. These results were checked on Calc 25.2.3.2 and agree with the official 365-day definition. LibreOffice Help, reviewed 2026-10-13.
Negative and zero returns
On a 365-day interval with initial cash flow -1000 and terminal 900, native Calc returned -10%. With terminal 1000 it returned a result extremely close to zero, within floating-point rounding. That is a valid outcome, not an input error: the signs of the two flows still differ. A negative XIRR indicates negative annualized performance, whereas all-negative flows cannot establish an investment return without any positive receipts.
Advanced applications
Compare two projects fairly
Make two independent date/flow columns for projects with different investment dates, and compute a separate XIRR for each. A simple cash-flow sum overlooks the timing of early versus late distributions. Do not concatenate the two projects into one range unless you mean to evaluate their combined investment. Because XIRR does not measure absolute profit, also compare invested capital, dollar gain, and project risk.
Evaluate a series of irregular contributions
List initial funding, later contributions as negatives, and actual distributions or a final independently valued liquidation as positives. XIRR can model irregular investment flows, but a final estimated balance is not cash you actually received; label the ending value as hypothetical and specify its valuation date. Excluding unsold holdings can distort returns dramatically.
Use different guesses for difficult cash-flow schedules
Try =XIRR(B1:B10;A1:A10;5%), =XIRR(B1:B10;A1:A10;20%), or an appropriately negative guess for a losing investment. These are alternative starting points for numerical search, not promises of convergence. The tested simple two-flow schedule converged at Guess=100 to its expected 20% root, while Guess=-1 returned Err:502; this is a Calc 25.2.3.2 observation, not a universal guess-limit rule. OpenFormula §6.12.51.
Beware multiple returns
When cash flows change sign more than once (for example, an investment followed by proceeds and later major remediation costs), the XNPV-versus-rate equation may have multiple roots or no economically useful root. Different initial guesses can then lead to distinct numerical outcomes, or an error. Inspect XNPV at several reasonable discount rates and assess whether the cash-flow story makes business sense before comparing returns. Do not automatically assume one XIRR result is unique. OpenFormula §6.12.51 iteration semantics, reviewed 2026-10-13.
Time-independent checks on inputs
For A1:A10 and B1:B10, count populated numeric entries first and inspect whether each date aligns with the intended amount. Useful guards include =COUNT(B1:B10) and =COUNT(A1:A10); for proper date serials, these should match the intended number of payment events. A count check alone does not verify that each pair is aligned, nor detect a date accidentally parsed as a different locale.
Errors, limits, and troubleshooting
| Symptom | Cause / resolution | Evidence |
|---|---|---|
Err:502 for a list of only deposits or only receipts |
At least one positive and one negative cash flow is required. Both sign-homogeneous cases returned Err:502 in Calc 25.2.3.2. |
Native diagnostics; official XIRR constraint. |
Err:502 for different sized ranges |
Values and Dates must contain equal numbers of paired entries. Matching two rows of values to one date returned Err:502 natively. |
Native diagnostic and OpenFormula §6.12.51. |
Err:502 when any date precedes the first |
The first event is the anchor. A later row with an earlier date produced Err:502 in the tested version. |
Native diagnostic; LibreOffice Help. |
| Solver fails or produces an unexpected annual rate | Confirm signs, dates, money units, and data order. Try a sensible Guess and examine whether there is a real/unique root. |
OpenFormula iteration behavior. |
| Unexpectedly high annual rate over a few days | XIRR annualizes a short holding period; even a small gain can compound to a high quoted annual rate. This does not forecast future returns. | 365-day equation; independent/native tests. |
| Text or empty cells silently change the calculation | Calc Help says text/empty cells in data ranges may be ignored. The tested version returned a plausible 20% despite an intervening empty or nonnumeric flow: check your inputs. | Help note and native diagnostics. |
| Dates look correct but differ from expectations | Check actual numeric serials and locale parsing; use DATE(...), apply a date display format, and keep every amount aligned with its date. |
Input-control recommendation, not a native failure finding. |
Displays 0.182843... when you expected 18.28% |
Format the output cell as a percentage; do not multiply by 100 inside the XIRR formula. | Native numerical result. |
What is not measured
XIRR does not account for taxes, inflation, risk, transaction fees unless you include them in the dated cash flows, nor does it predict future performance. It is generally a money-weighted return: its output responds to when and how much you invested. It is not the same as a time-weighted performance series, or an accounting return on assets. An external interest-rate benchmark can be evaluated with XNPV rather than treating XIRR as a discount rate provided by the market.
XIRR versus IRR and other functions
| Function | Use case | Key distinction |
|---|---|---|
| XIRR | Irregularly dated investment cash flows | Annualizes with exact day differences ÷365 and finds a rate. |
| IRR | Periodic cash flows at equal intervals | Solves the rate per period; dates are not supplied. |
| XNPV | Value irregular cash flows at a chosen annual discount rate | Returns a currency amount, not the rate. |
| NPV | Periodic cash flows at a supplied discount rate | Uses equal periods; no actual date series. |
| MIRR | Periodic cash flows with distinct financing/reinvestment rates | Makes reinvestment-rate assumptions different from basic IRR. |
Compatibility, official references, and verification record
Native execution observed: LibreOffice Calc 25.2.3.2 on Linux, tested 2026-10-13 using an ODS workbook recalculated via LibreOffice’s own UNO engine. In its UNO representation, some formulas use the internal COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXIRR name; users should enter the ordinary Calc =XIRR(...) syntax shown here. The bundled test workbook and JSON record both the formula entered and the native result. Claims about numbers above rely on native results plus separate Python calendar-day calculations; neither a Python result alone nor a cached XLSX result is labeled a native Calc execution.
Standards/documentation checked: OpenDocument v1.4, Part 4 OpenFormula §6.12.51 (XIRR) and §6.12.52 (XNPV), OASIS standard dated 2025-10-06; LibreOffice Help: Financial Functions Part Three — XIRR/XNPV, viewed 2026-10-13 (the dynamic /latest/ page may describe a newer release). Current source front matter retains its original broad Help reference and date. ODF standardization is not proof of identical execution in all spreadsheet programs; Windows, macOS, other Calc releases, Excel, and round-trip .xlsx compatibility were not tested here.