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

When *Compute All Cells* is FALSE, items of the ranking dimensions without a payment will be ignored. When it’s TRUE, empty items will be set to 0 for the computation.

The formula only considers the numeric values, other value types are 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.

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

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: