I’ve been using Java extensions for calculating IRR in Planning Analytics for quite a while, but it’s time to revisit this approach with the upcoming Planning Analytics version 2.1 deprecating Java Extensions among other things.
Overall there are 2 ways of calculating IRR (or anything not directly supported in Planning Analytics):
I’m not a big fan of rewriting calculation in PA approach as it’s usually both fairly difficult to write (due to limitations of the TI/rule language), very difficult to read / debug and usually slow to execute as the server cannot cache results. So using something external is my ‘go-to’ approach to get something working (and hopefully correct) fast.
There are a couple of ways to do an external calculation and load the results back into TM1:
First option is simpler as you’re hosting the ‘external’ calculation program on the same server as PA and don’t have to worry about authentication / data security as much. Second option is more ‘future-proof’, but a lot less generic as a lot depends on what flavour of cloud you’re on, where do you deploy the ‘calculation service’, etc.
Here’s an example of IRR / XIRR calculation as option 1 that is just a wrapper around the cool go-finance library. It’s an exe that you run on the file with your cashflows in the format like this
key,date,casfhlow
Project A, 2024-01-01,-1000
Project A, 2024-02-07,400
Project A, 2024-03-01,650
with the parameters of -in file_with_cashflows -out file_with_results -function irr_or_xirr -guess guess_value
and it’ll produce a file like this (for XIRR function):
key,result
Project A, 0.416184717
So you add the export cashflows / run this exe / load data back steps in your IRR or XIRR calculation process and you’re good to go on either PA 2.1 on prem or PA on Cloud.