> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Calculating the internal rate of return (XIRR)

> We'd like to calculate the internal rate of return (XIRR) for a schedule of cash flows that is not necessarily periodic.

## Use case

We'd like to calculate the internal rate of return (XIRR) for a schedule of cash
flows that is not necessarily periodic.

## Data modeling

XIRR calculation is enabled by the `XIRR` function, implemented in [SQL API][ref-sql-api]
and [DAX API][ref-dax-api]. It means that queries to any of these APIs can use the this function.

The `XIRR` function is also implemented in Cube Store, meaning that queries to the SQL API
or the [REST (JSON) API][ref-rest-api] that hit pre-aggregations can also use this function.
That function would need to be used in a measure that makes use of [multi-stage
calculations][ref-multi-stage-calculations].

<Info>
  Consequently, queries that don't hit pre-aggregations would fail with the following error:
  `function xirr(numeric, date) does not exist`.
</Info>

<Warning>
  Multi-stage calculations are powered by Tesseract, the [next-generation data modeling
  engine][link-tesseract]. In versions before v1.7.0, it was not enabled by default.
</Warning>

Consider the following data model:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: payments
      sql: |
        SELECT '2014-01-01'::date AS date, -10000.0 AS payment UNION ALL
        SELECT '2014-03-01'::date AS date,   2750.0 AS payment UNION ALL
        SELECT '2014-10-30'::date AS date,   4250.0 AS payment UNION ALL
        SELECT '2015-02-15'::date AS date,   3250.0 AS payment UNION ALL
        SELECT '2015-04-01'::date AS date,   2750.0 AS payment

      dimensions:
        - name: date
          sql: date
          type: time

        - name: payment
          sql: payment
          type: number

      # Everything below this line is only needed for querying
      # pre-aggregations in Cube Store
        - name: date__day
          sql: "{date.day}"
          type: time

      measures:
        - name: total_payments
          sql: payment
          type: sum

        - name: xirr
          multi_stage: true
          sql: "XIRR({total_payments}, {date__day})"
          type: number_agg
          grain:
            include:
              - date__day

      pre_aggregations:
        - name: main_xirr
          measures:
            - total_payments
          time_dimension: date
          granularity: day
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`payments`, {
    sql: `
      SELECT '2014-01-01'::date AS date, -10000.0 AS payment UNION ALL
      SELECT '2014-03-01'::date AS date,   2750.0 AS payment UNION ALL
      SELECT '2014-10-30'::date AS date,   4250.0 AS payment UNION ALL
      SELECT '2015-02-15'::date AS date,   3250.0 AS payment UNION ALL
      SELECT '2015-04-01'::date AS date,   2750.0 AS payment
    `,

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      },

      payment: {
        sql: `payment`,
        type: `number`
      },

      // Everything below this line is only needed for querying
      // pre-aggregations in Cube Store
      date__day: {
        sql: `${CUBE.date.day}`,
        type: `time`
      }
    },

    measures: {
      total_payments: {
        sql: `payment`,
        type: `sum`
      },

      xirr: {
        multi_stage: true,
        sql: `XIRR(${CUBE.total_payments}, ${CUBE.date__day})`,
        type: `number_agg`,
        grain: {
          include: [date__day]
        }
      }
    },

    pre_aggregations: {
      main_xirr: {
        measures: [
          total_payments
        ],
        time_dimension: date,
        granularity: `day`
      }
    }
  })
  ```
</CodeGroup>

## Query

### DAX API

You can use the `XIRR` function in DAX.

### SQL API

[Query with post-processing][ref-query-wpp] in the SQL API:

```sql theme={"dark"}
SELECT                                                                                   
  XIRR(payment, date) AS xirr
FROM (
  SELECT
    DATE_TRUNC('DAY', date) AS date,
    SUM(payment) AS payment
  FROM payments
  GROUP BY 1
) AS payments;
```

[Regular query][ref-query-regular] in the SQL API that hits a pre-aggregation in Cube Store:

```sql theme={"dark"}
SELECT MEASURE(xirr) AS xirr
FROM payments;
```

## Result

All queries above would yield the same result:

```text theme={"dark"}
        xirr        
--------------------
 0.3748585976775555
```

[ref-sql-api]: /reference/core-data-apis/sql-api/reference#custom-functions

[ref-dax-api]: /reference/dax-api/reference#financial-functions

[ref-rest-api]: /reference/core-data-apis/rest-api

[ref-query-wpp]: /reference/core-data-apis/queries#query-with-post-processing

[ref-query-regular]: /reference/core-data-apis/queries#regular-query

[link-tesseract]: https://cube.dev/blog/introducing-next-generation-data-modeling-engine

[ref-multi-stage-calculations]: /docs/data-modeling/measures#multi-stage-measures