CubeCalc is a super calculator for IBM TM1 and Planning Analytics. It enables you to execute complex finance calculations which are not available natively in IBM Planning Analytics (TM1) such as IRR (Internal Rate of Return) and NPV (Net Present Value).
Prerequisites
TM1 10.2 FP5 as minimum
TM1py v1.0.3 as minimum
1. Install Python
CubeCalc is a Python script. Steps to install Python can be found in the TM1py getting started guide.
2. Install TM1py
Open a command prompt and type: pip install TM1py
3. Download CubeCalc folder
CubeCalc is open-source and can be downloaded from its GitHub page.
4. Update config.ini file
Add your TM1 instance information to the config file:
Setup samples TM1 cubes
The examples use TM1 objects created by the setup sample.py script:
You should now see two new cubes: Py Project Planning and Py Project Summary.
How to calculate IRR with CubeCalc
The internal rate of return (IRR) is a metric used in capital budgeting to estimate the profitability of potential investments. Now thanks to CubeCalc, with one command line, you can calculate the IRR in IBM TM1 and Planning Analytics.
Calculating IRR for one project
Run the following command:
The tidy parameter enables you to delete the source and target views after use. Set to False to keep them, True to delete.
Recalculate the view to see the result:
If you compare in Excel, you will get the same value:
Calculating IRR for multiple projects
Since release v1.0, CubeCalc can perform the IRR calculation for multiple projects in one execution. Pass three additional arguments: Dimension, Hierarchy, Subset, and make sure the dimension is placed in the titles on both views.
Running CubeCalc from a TM1 process
Use the ExecuteCommand function in the Prolog or Epilog tab:
Troubleshooting CubeCalc
Check the CubeCalc.log file in the same folder as CubeCalc.py. Common errors include: connectivity issues (check config.ini), TM1py version should be v1.0.3 minimum.