Java TM1 Extensions: TM1FinanceLib for calculating IRR, NPV and more

catIRR

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.

  • Alexander Dvoynev

    Yurii, where do you get this pictures? 🙂

    As an addition, all this functions can be calculated using rules only, using iteration methods
    in the cube with additional “Iteration number” dimension. For example, IRR is solved by Newthon’s method in 2-3 steps.

    Solving of system of linear equations (using Gaussian elimination) using only rules, on the other hand, is rather slow (and combinatorially explosive). Using Java would be a better idea, thanks.

  • ykud

    Hi Alexander,

    I’m a crazy cat lady, I have a full closet of such pics 🙂

    Of course, all those functions are calculated using iterative methods (POI uses Newthon’s method, for example), although I’d seriously avoid doing it on rules as performance would be less than ideal (any recursion kills caching and makes TM1 quite slow).

    The main point of the post is that you don’t have to write such functions at all, clever guys behind POI project did all the heavy lifting & testing. In the same vein, Apache Math project will provide you pretty decent coverage in terms of statistical functions. So from my point of view using external libraries is a very efficient shortcut to some of the ‘complex math’ tasks in TM1.

    Cheers,
    Yuri

  • Pingback: Some statistical functions in TM1FinanceLib: VAR, Median, DevSQ and more | Applied dimensionality()