# XIRR - script function

XIRR() returns the aggregated internal rate of return (yearly) for a schedule of cash flows (that is not necessarily periodic) represented by paired numbers in pmt and date iterated over a number of records as defined by a group by clause. All payments are discounted based on a 365-day year.

Qlik's XIRR functionality (XIRR() and RangeXIRR() functions) uses the following equation, solving for the Rate value, to determine the correct XIRR value:

XNPV(Rate, pmt, date) = 0

The equation is solved using a simplified version of the Newton method.

Syntax:

XIRR(pmt, date )

Return data type: numeric

Arguments
Argument Description
pmt

Payments. The expression or field containing the cash flows corresponding to the payment schedule given in date.

date The expression or field containing the schedule of dates corresponding to the cash flow payments given in pmt.

When working with this function, the following limitations apply:

• Text values, NULL values and missing values in any or both pieces of a data-pair will result in the entire data-pair to be disregarded.

• This function requires at least one valid negative and at least one valid positive payment (with corresponding valid dates). If these payments are not provided, a NULL value is returned.

Information note

For information about recent updates to the underlying algorithm used by this function, see support article XIRR function Fix and Update.

## Interpreting the XIRR return value

The XIRR functionality is usually used to analyze an investment, where there is an outgoing (negative) payment in the beginning, and then a series of smaller income (positive) payments later on. Here is a simplified example with only one negative and one positive payment:

Cashflow:

Date|Payments

2023-01-01|-100

2024-01-01|110

] (delimiter is '|');

We make an initial payment of 100 and get 110 back after exactly one year. This represents a rate of return of 10% per year. XIRR(Payments, Date) returns a value of 0.1.

The return value of the XIRR functionality can be positive or negative. In the case of an investment, a negative result indicates that the investment is a loss. The amount of gain or loss can be calculated simply by making a sum aggregation over the payments field.

In the example above, we are lending out our money for one year. The rate of return can be thought of as interest. It is also possible to use XIRR's functionality when you are on the other side of the transaction (for example, if you are the borrower instead of the lender).

Consider this example:

Cashflow:

Date|Payments

2023-01-01|100

2024-01-01|-110

] (delimiter is '|');

This is the same as the first example but inverted. Here, we are borrowing 100 for one year and we repay it with a 10% interest. In this example, the XIRR calculation returns 0.1 (10%), the same value as the first example.

Note that in the first example, we received a profit of 10, and in the second example, we experienced a loss of 10, but the return value of the XIRR functionality is positive for both these examples. This is because the XIRR functionality calculates the hidden interest in the transaction, regardless of which side you are on in the transaction.

## Limitations with multiple solutions

Qlik's XIRR functionality is defined by the following equation, in which the Rate value is solved:

XNPV(Rate, pmt, date) = 0

It is sometimes possible for this equation to have more than one solution. This is known as the “multiple-IRR problem”, and is caused by a non-normal cash flow stream (also called an unconventional cash flow). The following load script shows an example of this:

Cashflow:

Date|Payments

2021-01-01|-200

2022-01-01|500

2023-01-01|-250

] (delimiter is '|');

In this example, there is one negative solution and one positive solution (Rate = -0.3 and Rate = 0.8). XIRR() will return 0.8.

When Qlik's XIRR functionality searches for a solution, it starts at Rate = 0 and increases the rate in steps until it finds a solution. If there is more than one positive solution, it will return the first one that it encounters. If it cannot find a positive solution, it will reset the Rate back to zero and start searching for a solution in the negative direction.

Note that a “normal” cash flow stream is guaranteed to have only one solution. “Normal” cash flow stream means that all payments with the same sign (positive or negative) are in a continuous group.