Well, I’m a bit late to the party, so you all know by now that after TM1 10.2.2 you can use something called Java Extensions to execute your own Java code and call it as a Turbo Integrator function. What I’ll show in this post is how to use this approach to calculate the usual ‘finance’ functions like IRR, NPV and some more. And there’ll be freebies, I’ll give you an extension library for some of the functions so you don’t have to write it yourself.

Overview

So I was playing around with trying to convert some of the Excel beauty in TM1 and stumbled on yet another function that Excel (let’s take IRR as an example) has as built-in and TM1 has no clue of (TM1 has only 3: PV, FV, PAYMT). We’ve all been there, you usually end up googling the algorithm, implementing it as a loop (or a number of loops) in TI to do the required calculation and forget about it.

I was thinking that maybe there’s a simpler way now with Java Extensions and of course there is. You can grab any of the IRR Java implementations out there, update it to match Java Extensions syntax run it a few times & compare with Excel, do a happy dance and be off about your other business. Which is exactly what I did, but then I thought that things might be still a bit easier (I’m quite annoying this way).

Enter Apache POI — a very mature Open Source library for manipulating Microsoft documents. More importantly for us the Excel formula part of this library reimplements a lot of the Excel built-in formulas (100+ out of 300 excel ones), so I thought that instead of writing yet another IRR calc I can reuse the one from the POI library.

So with using POI the java extension code for IRR looks like this (it should be a one-liner, but TM1 wants multiple values as strings rather than numbers, I didn’t bother figuring it out why):

public static double IRR(double guess,String [] values) 
{
    double[] nums = new double[values.length];
     for (int i = 0; i < nums.length; i++) {
        nums[i] = Double.parseDouble(values[i]);
       }
  return org.apache.poi.ss.formula.functions.Irr.irr(nums, guess);
}

Same for NPV or anything else that is directly supported by POI, making it heaps faster to add new functions and, more importantly, reducing the need to test so much (POI is widely used and tested).

So I’ve packaged a small ‘wrapper’ library as an example that supports the following functions.

Included Functions

IRR

Syntax:

IRR (guess, values)

Example usage in TI:

vIRR = ExecuteJavaN('tm1financelib.IRR', -0.01, '-70000.00','12000','15000','18000','21000','26000');

NPV

Syntax: NPV(rate, values)

Example usage in TI:

vNPV = ExecuteJavaN('tm1financelib.NPV', 0.1, '-10000.00','3000','4200','6800');

NPER

Syntax: NPER(rate, payment in period, present value, future value, boolean type)

Example usage in TI:

vNPV =  ExecuteJavaN('tm1financelib.NPV', 0.1, '-10000.00','3000','4200','6800');

PPMT

Syntax: PPMT(rate, period, number of periods, present value, future value, boolean type)

Example usage in TI:

vPPMT = ExecuteJavaN('tm1financelib.PPMT', 0.0083333333, 1,24, 2000, 0,0);

IPMT

Syntax: IPMT(rate, period, number of periods, present value, future value, boolean type)

Example usage in TI:

vIPMT = ExecuteJavaN('tm1financelib.IPMT', 0.0083333333, 1,24, 2000, 0,0);

DEVSQ

Syntax: DEVSQ (values)

Example usage in TI:

vDEVSQ = ExecuteJavaN('tm1financelib.DEVSQ', '-70000.00','12000','15000','18000','21000','26000');

AVEDEV

Syntax: AVEDEV (values)

Example usage in TI:

vAVEDEV = ExecuteJavaN('tm1financelib.AVEDEV', '-70000.00','12000','15000','18000','21000','26000');

Median

Syntax: MEDIAN (values)

Example usage in TI:

vMEDIAN = ExecuteJavaN('tm1financelib.MEDIAN', '-70000.00','12000','15000','18000','21000','26000');

VAR

Syntax: VAR (values)

Example usage in TI:

vVAR = ExecuteJavaN('tm1financelib.VAR', '-70000.00','12000','15000','18000','21000','26000');

VARP

Syntax: VARP (values)

Example usage in TI:

vVARP = ExecuteJavaN('tm1financelib.VARP', '-70000.00','12000','15000','18000','21000','26000');

I know these ones existed, but just to preserve excel syntax )

FV

Syntax: FV (rate, payment in period, present value, future value, boolean type)

Example usage in TI:

vFV = ExecuteJavaN('tm1financelib.FV', 0.005, 10,-200,-500,1);

PV

Syntax: PV(rate, number of periods, payment, future value, boolean type)

Example usage in TI:

vPV = ExecuteJavaN('tm1financelib.PV', 0.006666667, 240,500, 0,0);

PMT

Syntax: PMT(rate, number of periods, present value, future value, boolean type)

Example usage in TI:

  vPMT = ExecuteJavaN('tm1financelib.PMT', 0.006666667, 10,10000, 0,0);

Installation instructions

1. Download tm1financelib.jar and poi jar
2. Copy tm1financelib.jar to TM1 server\}javaextensions\ or TM1_installation\}javaextensions (to make it available from all TM1 servers), poi jar to a place you’d keep libraries (I just put it the same place as javaextensions, just create a libraries folder next to ibm). See more details in the IBM instructions
3. Configure java in your tm1s.cfg file:

JavaHome = point to the JVM shipped with TM1
JavaClassPath = add the folder you’ve copied poi jar

For example, with default Windows installation:

JavaJVMPath=C:\Program Files\ibm\cognos\tm1_64\bin64\jre\7.0\bin\j9vm\jvm.dll
JavaClassPath=C:\Program Files\ibm\cognos\tm1_64\samples\tm1\SData\}javaextensions\libraries\poi-3.14-20160307.jar

4. Restart server and you should be able to use the functions straight away as per examples above

Conclusions

Java Extensions are very versatile, but I’d use them with caution (you can go real crazy and, for example, package all the calculation logic in a jar file run from TI to ‘protect IP’). For adding complicated calculations and everything that is not native to TM1 — I think it’s a really neat solution, as it allows using a much more flexible language and the whole heap of Java pre-built libraries out there (matrix calculations anyone?).

My rule of thumb would be to do everything in rules & TIs until you reach something really complex & unsupported and even then limit Java Extensions to a certain set of functions, leaving as much logic as humanely possible in TM1. Just making it easy for the ‘next guy to support this’.

Need another function?

If there’s any usage / demand for this library and you’re missing some function from Excel (Median, anyone?): drop me a line, if it’s in POI I might add it fairly quickly.