Use XIRR Function To Compute Return of Specifically Dated Cashflows

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

Formula

Result

Description

XIRR('Payment Metric', 0.5, FALSE, 'Fiscal Year', 'Fiscal Year'.Date)

Click on image to enlarge.

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.

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.

This example uses a multi-dimensional Payment Metric named Payment Per Country and defined on two dimensions: Fiscal Year and Country.

XIRR function

Description

Syntax

Return type

Examples

See also

Product

Use Cases

Resources

Company

Follow