XIRR aggregating the data

[Anonymous]

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

[Anonymous]

i can do change in the script as long as the original table is maintained

[sunny_talwar]

Check it this works (attaching qvf)

[sunny_talwar · Accepted solution]

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;

[Anonymous]

and then i apply the XIRR simply as follows:

XIRR(PaymentAmount,DueDate) ??

and grouped by LoanID ?

[sunny_talwar]

On the front end... yes

[Anonymous]

it worked thank you