IRR function to operate on a specified time period ranges

nikolay_denisov

Hello team

I would propose an idea of IRR function enhancement to operate on more dynamic environment and respect Anaplan time dimension in the same way as TIMESUM function.

 

Why it is necessary? 

For IRR calculation when the sign of the cash flows changes more than once, for example when positive cash flows are followed by negative ones and then by positive ones (+ + − − − +), the IRR may have multiple real values. So it is essential to have an option to overview overall IRR of the full project, or have a chance to specify periods which need to be calculated. (WIKI)

 

Currently in Anaplan IRR applies to full timescale and "subsetting" dataset to smaller pieces would be not easy and produce sparcity and not aligned to the best practices calculations. 

 

Proposed Syntaxis:

IRR(CF, From, To, [Ev]) where:

• CF - number formatted source of a Cash Flow
• From - Period or Date of a cashflow start 
• To - Period or Date of a cashflow end
• Ev - Optional, number format for expected values of IRR

Expected benefits:

IRR becomes more customisable and could be compared similarly to EXCEL functional analogues with level of calculations customisation.

a.dilieto

I agree, it would be a good improvement of the IRR function!

wwood

Another vote for this functionality.

Axel_H

Agreed with this idea.

Very often we need to calculate the evolution of IRR over time and need to work with transaction lists and create a lot of sparcity.

david.savarin

Wouldnt it work if you switch the cf values to 0 if they are out of range of your required time series (with a conditional line item amount) ?

You have to create an additional line item in your transaction module, yet i believe it would yield what your are aiming ?

Jcorlett

Great suggestion that would eliminate sparsity or more complex workarounds. I'd add a request for NPV to share the same functionality; "rolling" NPV's where the 'range' and period 0 would move with the target cell. Again this would eliminate sparsity and workarounds involving the calculation of discount factors + PV's.

Charanreddy

Have a look into it, It might help 

 

IRR & NPV Calculations with Initial/Start Date - Anaplan Community

 

 

 

Iain_Briggs

Thanks @Charanreddy  however the Anaplan IRR function would still really benefit from the functionality proposed by @nikolay_denisov 

 

For example, I am often asked for an 'inception to date' IRR. In the below screenshot, it takes seconds to do this (for every time period) using Excel's XIRR. However Anaplan's IRR function only calculates across the entire timescale in this case.  Whilst yes, it can be done in Anaplan, it requires a multi-step work-around. 

 

 

Iain_Briggs

Given this thread has 20+ likes, are the product team going to reply please?

Ashutosh

I agree. Must Needed

Paul_Petersen

Just ran into this again, heavy XIRR requirements from a prospect we are demoing to. Have there been any updates, workarounds, or roadmap decisions made on this topic?