---
title: "Use XIRR Function To Compute Return of Specifically Dated Cashflows"
slug: "xirr-function"
description: "Learn to use the XIRR function to calculate the internal rate of return from irregular cashflows and specific dates in Pigment. "
updated: 2026-05-14T14:35:52Z
published: 2026-05-14T14:35:52Z
canonical: "kb.pigment.com/xirr-function"
---

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

# XIRR function

## Description

Calculates the internal rate of return of an investment based on future cashflows on **specific dates** for a given time. The internal rate of return is the rate for which the [NPV](/v1/docs/npv-function) (net present value) would be 0.

## Syntax

`XIRR(Payment Metric [, Guess] [, Compute All Cells] [, Ranking Dimension] [, Days Used])`

| Argument | Type | Dimensions | Description |
| --- | --- | --- | --- |
| *Payment Metric* (required) | Number or Integer | *Ranking Dimension -* must be a Dimension with a Property of type Date or a Time Dimension | This represents the cashflow of the investment. Cashflow can be positive if there is income or negative if payments are needed. |
| *Guess* (optional) | Number or Integer | No Dimension | This number represents a value close to what you expect your IRR to be. It cannot be below -1 and should rarely be above 100. If not filled, it is by default set at 0.1. |
| *Compute All Cells* (optional) | Boolean | No Dimension | Defines whether the computation should be done on all *Ranking Dimension*’s Items. If TRUE, it starts at the first non-empty *Ranking Dimension* Item until the last non-empty. If FALSE, it computes only on the first non-empty item of *Ranking Dimension*. By default, it’s set to FALSE. |
| *Ranking Dimension* (optional) | Dimension |  | Required when *Payment Metric* is defined on multiple Dimensions. Defines the Dimension used to perform the calculation on. |
| *Days Used* (optional) | Date | Property of a Dimension (cannot be a Metric) | Required when *Ranking Dimension* is not a Time Dimension generated by Pigment. Defines the Property of type Date that provides the specific dates of the future cashflows. |

## Return type

| Type | Dimensions |
| --- | --- |
| Number | No Dimension, or subset of *Payment Metric*’s Dimensions |

If *Compute All Cells* is `FALSE`, it returns for the first Item of *Ranking Dimension* for which *Payment Metric was not empty* the rate for which the NPV of all future payments would be equal to 0.

If *Compute All Cells* is `TRUE`, it returns for all Items of *Ranking Dimension* the rate for which the NPV of all future payments would be equal to 0.

> [!NOTE]
> ℹ️ Note
> 
> When *Compute All Cells* is `FALSE`, Items of the Ranking Dimensions without a payment are ignored. When it’s `TRUE`, empty Items are set to 0 for the computation.

The formula only considers the Number values, other value types are ignored.

Pigment uses the [Newton-Raphson method](https://www.sciencedirect.com/topics/mathematics/newton-raphson-method#:~:text=The%20Newton-Raphson%20method%20begins,0%20crosses%20the%20x-axis.) to find the IRR and uses the guess as the first point. A result is considered as found once the method has found an IRR for which the NPV is equal to 0 with a 0.00001 percent accuracy.

If, after 200 iterations, no result is found, the system returns a blank value.

Payments need to be of different signs in order to find a possible result. It returns a blank result if all values are positive or negative.

## Examples

Click on image to enlarge.

| Formula | Result | Description |
| --- | --- | --- |
| `XIRR('Payment Metric', 0.5, FALSE, 'Fiscal Year', 'Fiscal Year'.Date)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/34a1e6d7-9aed-4ec9-8cef-08e9a78cca75.png) XIRR with defaults and dates | This example uses a simple `Payment Metric` defined on a *Ranking Dimension* named *Fiscal Years*. The function returns the value for FY 23, as this is the first year of the *Ranking Dimension* and *Compute All Cells* is set to `FALSE`. However, you could also use this formula in a Metric without dimensions. For *Days Used*, we use `Fiscal Year'.Date` payment date that is a property on the *Ranking Dimension* , `Fiscal Year`. Here the *Guess* has been set to `0.5` but it could reference another single integer or number. |
| `XIRR('Payment Metric', 0.5, TRUE, 'Fiscal Year', 'Fiscal Year'.Date)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/a1cd809d-b49b-4e0e-a6a9-2382f00efdad.png) XIRR with *Compute All Cells* | This example is the same as above but sets the *Compute All Cells* to `TRUE`. This will compute XIRR for each item of the *Ranking Dimension*, in this case for each year in the `Fiscal Year` Dimension. This example is assuming each year is the ‘start’ of a new XIRR computation. |
| `XIRR('Payment Per Country', 0.5, FALSE, 'Fiscal Year', 'Fiscal Year'.Date)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/84b4cf19-93b4-468e-9026-05d45b97ff71.png) XIRR on multiple dimensions | This example uses a multi-dimensional *Payment Metric* named `Payment Per Country` and defined on two dimensions: `Fiscal Year` and `Country`. |

## See also

Excel: [IRR](https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc), [XIRR](https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d)

Related articles:

[IRR](/v1/docs/irr-function)