topic Re: Need 2 Date Ranges for XIRR calc - Help in QlikView

Need 2 Date Ranges for XIRR calc - Help

Anonymous — Mon, 01 Feb 2016 16:21:30 GMT

Hi All,

I’ve been struggle to calculate XIRR with couple date range selection. In current process, I calculate inception to date base on the transaction date and cash flow and it works fine. I wanted to tweet it a bit to add 2 selection dates where user can define. To calculate XIRR, the first cost/payment needs to be a negative.

What I want to accomplish: – 2 Date selection objects

Need to take Date selection # 1 prior date “CashFlow” value and turn into negative for XIRR calculation.
This will serve as my staring point first cost/payment

For Example: If I want to check the XIRR % from 1/1/2015 – 9/30/2015 (base on “Reporting_Date”)

#1 –select Date Range 1/1/2015 base on “Reporting_Date”

#1_a – as I said above regards to negative. I need 12/31/2014 where Flow Type = EMV as my starting point and this value needs to be negative (-1,100,000)

#2 –select Date Range 9/30/2015 base on “Reporting_Date”

#3- XIRR Caculation - xirr(CashFlow, Transaction_Date)

Any help will greatly appreciated!

Thank You,

Frank

Re: Need 2 Date Ranges for XIRR calc - Help

settu_periasamy — Tue, 02 Feb 2016 03:30:41 GMT

Hi,

I think you need to look on the expression with aggregation. Not sure, But you can try something like this..

=XIRR(

Aggr(If(RowNo(TOTAL)=1,

Sum({<FlowType={'EMV'}>}CashFlow)*-1,Sum(CashFlow)),

Transaction_Date,Company),Transaction_Date)

You just play with the expression, based on your need..

Re: Need 2 Date Ranges for XIRR calc - Help

Anonymous — Tue, 02 Feb 2016 18:42:36 GMT

Thanks for the sample expression. I will play around with the expression.