Is it possible to calculate the XIRR for the each of the below loans in Qlik ?
For example: what is the XIRR for Loan1 having princiapl value of 3500 and repaid in 4 instalment 1000 each (total repayment 4000, resulting in 500 of interest)?
the key question on how to arrange the data in qlik to arrive for XIRR(-3500,1000,1000,1000,1000; then dates array) ?
|LoanID||Serial#||Principal||Payment Amount||Due Date|
Solved! Go to Solution.
Updated the script to include a flag for concatenated data to identify the rows which is added in the script
LOAD * INLINE [
LoanID, Serial#, Principal, Payment Amount, Due Date
Loan1, 1, 3500, 1000, 1/1/16
Loan1, 2, 3500, 1000, 1/2/16
Loan1, 3, 3500, 1000, 1/3/16
Loan1, 4, 3500, 1000, 1/4/16
Loan2, 1, 4000, 1200, 1/1/17
Loan2, 2, 4000, 1200, 1/2/17
Loan2, 3, 4000, 1200, 1/3/17
Loan2, 4, 4000, 1200, 1/4/17
Only(-Principal) as [Payment Amount],
0 as Serial#,
Only(Principal) as Principal,
Date(AddMonths(Min([Due Date]), -1)) as [Due Date],
'Principal' as Flag
Group By LoanID;
Yes already did.
In the example in the link you've provided, the payments in the XIRR (payments,date) is already arranged to start with one negative value then positives values. This is not the case in the data shown in my question.
I am working on Qlik Sense (not on QlikView), and i have access to the QMC Hub.
I am not able to import and open the the qvw file you sent.
How can i do ?
The results are shown in the attached excel file.
They are different.
Note: Unlike the attached file, the data in the database is not ordered to start with -3500 and -4000 (it is ordered as shown in the first table posted in the thread).