Need 2 Date Ranges for XIRR calc - Help

[Anonymous]

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

[settu_periasamy]

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

[Anonymous]

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