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):

• Using loops (in TIs or rules) to approximate the function
• Calculate externally and upload result in TM1

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:

1. the ‘old’ / V11 way that assumes that you have access to the operating system:
   • Write the data for calculation to a file
   • Run a script or a program on the file and write results to a file
   • Load the calculated results from the generated file back to TM1
2. the ‘new’ / V12 way where every component is a service
   • write the calculation logic you want as an external service or a cloud function
   • invoke this service from PA using an HTTP request that passess through the data
   • read the result of the request and write it back
   • or, alternatively, incorporate both reading PA data and writing data back in the same cloud function using REST API for PA

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.