Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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..
Thanks for the sample expression. I will play around with the expression.