Announcements
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER

# XIRR function Fix and Update

cancel
Showing results for
Did you mean:
Employee

## XIRR function Fix and Update

Last Update:

Mar 26, 2023 12:39:24 PM

Updated By:

Torbjorn_Soderberg

This is a breakdown and explanation of the new XIRR function implementation that is currently being rolled out to Qlik Cloud Services. This effect the use of XIRR chart and script function as well as the RangeXIRR function. There are two changes that impacts your uses of XIRR, listed as 1 and 2 below:

#### 1. Fix for the "loan" scenario

The XIRR function is defined as calculating the internal rate of return of an investment, where an "investment" means some opportunity to spend money initially to later earn more money than was spent initially, making a profit.

The fix for the XIRR function enables the correct calculation of also the inverted scenario of an investment. This is a scenario where you are receiving money initially, and then later repay the money probably at a loss. I don’t have a financial/economics education, but I would call it “interest rate” rather than “rate of return” since it has the characteristics of a loan. One example would be if you have a payment plan for repaying a loan with predetermined payment sizes and dates. You could use XIRR to calculate the yearly interest rate equivalent of what you are paying for the loan.

Example:

 Payment Date 1000 2021-01-01 -450 2022-01-01 -400 2023-01-01 -350 2024-01-01

The new XIRR implementation returns 0.1016.

The old implementation returns NULL for this example. For other “loan” scenarios the old implementation might return -0.99 or close to that. Note that XIRR never returns a lower value than -1. This is true both for the old and the new implementation. This is just part of XIRRs nature.

#### 2. Adjustment for the multiple-IRR scenario

Under certain circumstances it is possible for the XIRR 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 unconventional cash flow).

Example of a non-normal cash flow stream:

 Payment Date -200 2021-01-01 500 2022-01-01 -250 2023-01-01

In this example we have an outflow (negative value) at the beginning and also at the end, with an inflow (positive value) in between. This is a non-normal cash flow stream because the payments switch sign twice.

The example has two solutions, one is -0.309 and the other one is 0.809.

The new Qlik XIRR implementation will always search for a solution with a positive answer before searching for a negative one. It starts from zero and searches towards infinity. It is not possible to ask XIRR for more solutions or guide it to look for an answer in a certain range of values. The old XIRR might pick a negative solution even when a positive one exists.

“Normal” cash flow stream Explanation:

Note that a “normal” cash flow stream is guaranteed to have only one solution, so in that case you don’t need to wonder if there could be other solutions. “Normal” cash flow stream means that all payments with the same sign (positive or negative) is in a continuous group. The first example presented in this article follows the normal cash flow pattern, so does the example below:

Example of a normal cash flow stream:

 Payment Date -100 2021-01-01 -15 2021-03-01 -30 2021-08-01 -30 2022-01-15 25 2024-01-01 30 2025-01-01 15 2026-01-01 10 2027-01-01

The XIRR for this example is -0.1287

#### Summary:

So as a summary, a "normal" cash flow stream is one where the sign switches only once, such a stream can resemble either an investment or  a loan depending on if it starts with inflow or outflow payments. The XIRR equation for a "normal" cash flow stream has only one solution, which our new XIRR implementation will return even when it is of the "loan" type. A "non normal" cash flow stream is one where the sign switches more than once. This makes it a hybrid between an investment and a loan, although for example the "investment" part might be dominant over the "loan" part. A non normal cash flow stream can possibly have more than one solution. Our new XIRR will prioritize a positive solution over a negative one.

Labels (2)

• ### QlikView

Contributor II

Is there a timeline for this fix to be included in the Qlik Enterprise/Managed platform?

Employee

Yes, this fix is included in the May 2023 releases.

Contributors
Version history
Last update:
‎2023-03-26 03:39 PM
Updated by: