Skip to main content
XIRR is an aggregate function that calculates the annualized internal rate of return for a series of cash flows that occur on irregular dates. It is Cube’s counterpart to the XIRR function in Excel and DAX, and solves the same equation. The function is available in:
  • The SQL API, in queries with post-processing (v1.3.8 and later).
  • Cube Store (v1.3.12 and later). This is what serves XIRR when it is used in a multi-stage measure queried through any Core Data API, including the REST (JSON) API, as long as the query hits a pre-aggregation.
  • The DAX API.
This page explains how the function itself works. For a complete data model example, see the XIRR recipe.

​
Syntax

The result is a double: an annualized rate expressed as a fraction, so 0.1 means 10% per year. XIRR(DISTINCT ...) is not supported.

​
How the rate is calculated

XIRR finds the rate r at which the net present value of all cash flows in the group is zero:
where t_i is the time of each cash flow in years and date_0 is the earliest date in the group. Note that:
  • Time is measured in years as the number of days since the earliest cash flow, divided by 365. Leap days count as ordinary days.
  • The earliest date is the anchor regardless of row order, so rows can arrive in any order and the result is the same. Unlike Excel, the earliest cash flow does not need to be the first one.
  • Cash flows on the same date are discounted identically, so it makes no difference whether they are summed per day before the call or passed as separate rows.
The equation has no closed-form solution, so it is solved numerically with Newton’s method:
  1. Start at initial_guess (0.1 by default).
  2. Compute NPV(r) and its derivative at the current rate. Rows whose payment is exactly 0 are skipped.
  3. If the absolute value of NPV(r) is below 0.000001, stop and return r.
  4. Otherwise, move the rate by NPV(r) / NPV'(r) and repeat.
  5. After 100 iterations without meeting the tolerance, or as soon as a step produces a rate that is not a number (for example, after stepping to -100% or below), the function reports no solution.
The tolerance is absolute and in the units of payment: the iteration stops when the discounted cash flows sum to less than one millionth. Scaling every payment by the same factor does not change the rate, so if your amounts are very large (billions), passing them in thousands or millions gives the solver more floating-point headroom.

​
Results and errors

The function evaluates once per group and behaves as follows:
A group with no non-zero cash flows does not fail; it echoes initial_guess back (0.1 by default), which looks like a real 10% return. If a group in your data can have no flows, guard the call, for example: CASE WHEN SUM(ABS(payment)) > 0 THEN XIRR(payment, date, 0.1, NULL) END.

​
Convergence

Newton’s method follows the slope from a single starting point, so the starting point matters for unusual series:
  • For strongly negative returns, such as a large loss over the period, the default 0.1 guess can step past -100% and fail. Pass a negative initial_guess, for example -0.9, or evaluate the function twice with two guesses and combine the results with COALESCE.
  • Because the result is annualized, a short window turns a small gain or loss into a large rate. Read XIRR over windows long enough for an annualized figure to be meaningful.

​
Where the function runs

XIRR exists in the SQL API’s post-processing engine and in Cube Store. It does not exist in your upstream database. When you use it in the data model:
  • Define it as a multi_stage measure of type number_agg whose grain includes a day-level time dimension, and serve it from a pre-aggregation with day granularity. Cube Store then receives one row per day and runs the solver. The XIRR recipe shows the full pattern.
  • Every dimension a query groups or filters by must be present in that pre-aggregation. A query that cannot be matched falls through to the upstream database, which fails with its own unknown-function error, for example function xirr(numeric, date) does not exist in Postgres.
  • Applying a time granularity to such a query groups the cash flows into buckets and solves each bucket separately. A bucket that does not contain both an outflow and an inflow has no root, so it returns on_error or fails.