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.
