Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bassofrem
New Contributor

XIRR aggregating the data

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

LoanIDSerial#PrincipalPayment AmountDue Date
Loan11350010001/1/16
Loan12350010001/2/16
Loan13350010001/3/16
Loan14350010001/4/16
Loan21400012001/1/17
Loans2400012001/2/17
Loan23400012001/3/17
Loan24400012001/4/17
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: XIRR aggregating the data

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;

15 Replies

Re: XIRR aggregating the data

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

XIRR - chart function ‒ QlikView

bassofrem
New Contributor

Re: XIRR aggregating the data

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.

MVP
MVP

Re: XIRR aggregating the data

Is this what you are hoping to see?

Capture.PNG

bassofrem
New Contributor

Re: XIRR aggregating the data

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 ?

MVP
MVP

Re: XIRR aggregating the data

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

bassofrem
New Contributor

Re: XIRR aggregating the data

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

MVP
MVP

Re: XIRR aggregating the data

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

bassofrem
New Contributor

Re: XIRR aggregating the data

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

MVP
MVP

Re: XIRR aggregating the data

Making changes in script is something you are okay with?