RangeXIRR - script function

RangeXIRR() returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the RangeIRR function.

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:  

RangeXIRR(values, dates[, Expression])

Return data type: numeric

Arguments:  

• dates: A payment date or a schedule of payment dates that corresponds to the cash flow payments.
• values: A cash flow or a series of cash flows that correspond to a schedule of payments in dates. Each value may be a single value or a range of values as returned by an inter-record function with a third optional parameter. The series of values must contain at least one positive and one negative value.
• Expression: Optional expressions or fields containing the range of data to be measured.

Limitations:  

Text values, NULL values and missing values are disregarded.

All payments are discounted based on a 365-day year.

Example 1:  

RangeXIRR(-2500,'2008-01-01',2750,'2008-09-01') returns 0.1532.

Example 2:  

Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document to see the result.

RangeTab3:

LOAD *,

recno() as RangeID,

RangeXIRR(Field1,Field2,Field3) as RangeXIRR;

LOAD * INLINE [

Field1|Field2|Field3

10|5|-6000

2|NULL|7000

8|'abc'|8000

18|11|9000

5|5|9000

9|4|2000

] (delimiter is '|');

The resulting table shows the returned values of RangeXIRR for each of the records in the table: