--- title: "Use The IRR Function To Compute Return With Evenly Spaced Cashflows" slug: "irr-function" description: "Learn to use IRR to calculate the internal rate of return for evenly spaced future cashflows where NPV equals zero." updated: 2025-05-28T10:32:52Z published: 2025-08-22T12:00:18Z --- > ## 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. # IRR function ## Description Calculates the internal rate of return of an investment based on future cashflows **evenly spaced** 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 `IRR(Payment Metric [, Guess] [, Compute All Cells] [, Ranking Dimension])` | Argument | Type | Dimensions | Description | | --- | --- | --- | --- | | *Payment Metric* (required) | Number or Integer | *Ranking 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 if the computation should be done on all *Ranking Dimension*’s items. If TRUE, starts at the first non-empty *Ranking Dimension* item until the last non empty. If FALSE, compute only on the first non-empty item of *Ranking Dimension*. By default, set to FALSE. | | *Ranking Dimension* (optional) | Dimension (any kind) | | Required when *Payment Metric* is defined on multiple dimensions. Defines the dimension used to perform the calculation on. | ## Return type | Type | Dimensions | | --- | --- | | Number | No Dimension, or subset of *Payment Metric*’s Dimensions | If *Compute All Cells*is `FALSE`, will return 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`, will return for all items of *Ranking Dimension*the rate for which the NPV of all future payments would be equal to 0. > [!WARNING] > In case you have a dataset where the payments & incomes happen on specific dates (and are not evenly spaced), you cannot use the IRR formula. In that case, you need to use the XIRR formula. The formula only considers the numeric values, and if there are blanks or text values, these would be 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 will return a blank value. Payments need to be of different signs in order to find a possible result, i.e. you will get a blank result if all values are positive or negative. ## Examples | Formula | Result | Description | | --- | --- | --- | | `IRR('Payment Metric')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/e0cc9a5b-9b22-4130-a904-6771b5e9f5ec.png) IRR with default arguments | This example uses a simple `Payment Metric` defined on a Metric with fiscal years. The function returns the value for FY 23, as this is the first item of the `Fiscal Year` dimension. However, you could also use this formula in a Metric without dimensions. *Compute All Cells* defaults to `FALSE`. *Ranking Dimension* is an optional third argument in this example because its only defined on the `Fiscal Year` dimension. | | `IRR('Payment Metric', 0.5, TRUE)` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/bb97821d-fac9-4f18-acec-7c2fb5068b8f.png) IRR with *Compute All Cells* | This example is the same as above but sets the *Compute All Cells* to *TRUE*. This will compute IRR for each item of the *Ranking Dimension*, in this formula the *Ranking Dimension* is optional because the Metric only contains on dimension. Here the *Guess* has been set to `0.5` but it could reference another single integer or number. | | `IRR('Payment Per Country', 0.5, FALSE, 'Fiscal Year')` | Click on image to enlarge. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/e936903d-3c4c-418f-9af1-d5f45beea3f1.png) IRR on multiple dimensions | This example uses a multi-dimensional *Payment Metric* named *Payment Per Country* and defined on two dimensions: `Fiscal Year` and `Country`. It is thus mandatory to specify on which dimension we want to compute IRR by providing the fourth argument, *Ranking Dimension*, which is `Fiscal Year` in this example. | ## 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: [XIRR](/v1/docs/xirr-function)