IRR Function

jonathan_fogden
jonathan_fogden
in Modeling

I am attempting to calculate IRR on monthly loan cash flows, but my results are different to Excel and make no sense (e.g. Excel give 14.7% and Anaplan gives 418.80%) . All the examples I have seen (on Anapedia and in the Formulas and Functions app from App Hub) have annual cash flows, for which it seems to work. I have tried both methods illustrated in Anapedia and they produce similar results to each other, but not to Excel. I have also used the NPV function with the Anaplan IRR percentage and Anaplan is consistent in that the NPV function produces net zero cashflows.

 

Example  cashflows are

Sep 19     Oct 19       Nov 19       Dec 19       Jan 20         Feb 20    Mar 20
280330.0 -25188.03 -144385.63 -126720.61 -104812.92 -8014.62 -196.8

 

Can anyone point me in the right direction?

Tagged:

Answers

  • Tiffany.Rice
    Tiffany.Rice
    For the dimensions being used in the module, is there by chance a top level on any of those lists (or a parent hierarchy) assigned? By default modules will sum the values together for roll up levels. You can change this behavior by updating the Summary type to Formula (you do this within the blueprint mode).

    If that isn't the issue, it would be helpful if you could share screenshots of you modules with the formula you are using.
  • jonathan_fogden
    jonathan_fogden
    I have had some conversations with my Customer Success Business Partner and found a solution which involved using the method which uses dates and making the time intervals years rather than month. That works for my simple purposes.

    To your point, the results I reported only had a time dimension where the cashflows were across a six month period, so I can't see where it could have been summarising.

    I tried replicating the examples on Anapedia and it does not work for monthly flows. IRR using period only works for annual flows, and with flows with a date is the equivalent of XIRR in Excel. It would be useful if Anapedia made that clear.
  • daiju.muto
    daiju.muto

    I encountered the same issue and was troubled.
    I even implemented a way to map month to year as suggested here.
    However, I realized that the solution was actually simple: if Anaplan could only return annual IRR results, I could divide that by 12 to get the monthly IRR.

Categories