Solved: Re: XIRR aggregating the data - Qlik Community

Anonymous · ‎2017-08-29

Hello everyone,

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) ?

thank you

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
Loans	2	4000	1200	1/2/17
Loan2	3	4000	1200	1/3/17
Loan2	4	4000	1200	1/4/17

sunny_talwar · ‎2017-08-29

Updated the script to include a flag for concatenated data to identify the rows which is added in the script

Table:

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

];

Concatenate(Table)

LOAD LoanID,

Only(-Principal) as [Payment Amount],

0 as Serial#,

Only(Principal) as Principal,

Date(AddMonths(Min([Due Date]), -1)) as [Due Date],

'Principal' as Flag

Resident Table

Group By LoanID;

View solution in original post

Anonymous · ‎2017-08-29

Have you looked at using the builtin Qlik function XIRR(), here is the online Help entry for it :

XIRR - chart function ‒ QlikView

Anonymous · ‎2017-08-29

Hello Bill

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.

sunny_talwar · ‎2017-08-29

Is this what you are hoping to see?

Anonymous · ‎2017-08-29

Hello Sunny

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 ?

sunny_talwar · ‎2017-08-29

My bad, but how about the numbers, do they look right? I mean what are the numbers you expect to see?

Anonymous · ‎2017-08-29

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).

thank you

sunny_talwar · ‎2017-08-29

But how do we know the date associated with the Principal?

Anonymous · ‎2017-08-29

It is one month prior to the due date of the first payment (excuse me, my err)

sunny_talwar · ‎2017-08-29

Making changes in script is something you are okay with?