## 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 (net present value) would be 0.

## Syntax

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

Argument | Type | Dimensions | Description |
---|---|---|---|

(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. |

(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. |

(optional) | boolean | No Dimension | Defines if the computation should be done on all If TRUE, starts at the first non-empty If FALSE, compute only on the first non-empty item of By default, set to FALSE. |

(optional) | Dimension (any kind) | Required when Defines the dimension used to perform the calculation on. | |

(optional) | Date | Time Dimension | Required when Defines the date property 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`

, 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.

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 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.

Cashflows 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

Click on image to enlarge.

Formula | Result | Description |
---|---|---|

| Click on image to enlarge.
| This example uses a simple For Here the |

`XIRR('Payment Metric', 0.5, TRUE, 'Fiscal Year', 'Fiscal Year'.Date)` | Click on image to enlarge.
| This example is the same as above but sets the |

`XIRR('Payment Per Country', 0.5, FALSE, 'Fiscal Year', 'Fiscal Year'.Date)` | Click on image to enlarge.
| This example uses a multi-dimensional Payment Metric named `Payment Per Country` and defined on two dimensions: `Fiscal Year` and `Country` . |

## See also

Related articles: