Figured out the IRR implementation as well. This involves iterations in which you vary the Discount Rate in your NPV calculation, and test to see whether you are getting closer to zero.
First of all, you need an Iteration dimension in your original NPV cube.
My big revelation was that I needed to calculate IRR in steps. This allowed me to achieve the calculation in a total of ~40 iterations, whereas my previous attempt to do it in one step with a thousand iterations was slowing the implementation down (not to mention it was utterly inefficient).
Step 1: Create your first cube in which you Vary Discount Rate by 10% for each iteration from an initial guess. Determine the +-10% bounds of Discount Rate when your NPV crosses the zero line (i.e., changes from positive to negative, or negative to positive. The way I implemented it, as soon as the value crossed the zero line, I stopped changing the Discount Rate. Thus it remained static for the remaining iterations. Only 11 iterations are required for this step.
Step 2: Create a second cube in which you look up the two bounds from the first cube. Say, for example, the Discount Rate in step one was determined to be between 40 and 50%. Start with 40% in Iteration 1 of your second cube and, this time, vary the Discount Rate by 1%. Again, only 11 iterations are needed.
Keep repeating this process for higher levels of precision. For example, to obtain the IRR at two places after the decimal (e.g., 55.22%), you will need four steps.
The business rules for the later steps can be copied from the previous steps, and only minor cube reference tweaks are needed.
While I am glad I was able to get past this hurdle, I do wish TM1 would include NPV and IRR as standard calculations, such that there is equivalency with Excel.
------------------------------
Shubho Ghosal
------------------------------
Original Message:
Sent: 11-27-2018 03:13 PM
From: Shubho Ghosal
Subject: INSTRUCTIONS NEEDED: IRR/NPV
Figured out how to calculate NPV. Turns out that once you understand the formula, it is pretty simple.
Formula is as follows:
NPV = Sum of Cash Flow / (1 + r)**(Year Number - Current Year Number).
'r' happens to be the discount rate.
In essence, you are discounting future cash flows to bring them to present value (assuming some inflationary rate or other factors, I suppose).
Below was how I was able to calculate NPV.
Create a cube with Year as a dimension, and include the Yearly Cash Flows within (this can be looked up from your Cash Flow Cube)
For each Year, calculate the Discounted Cash Flow. This is simply calculated as below.
(For a given year)
Discounted Cash Flow = Cash Flow / (1 - r)^(DIMIX(Year) - DIMIX(2018))
i.e., You are calculating the discounted term separately, for each year.
A simple sum of all these discounted cash flow values gives you the Net Present Value for 2018.
Note that this was just a calculation of NPV for 2018.
Another way to achieve this is described in this post - Java TM1 Extensions: TM1FinanceLib for calculating IRR, NPV and more
This is also an option we are looking at, though it will take us a bit of time to install the required toolsets. Also, familiarity with TI is required, I believe.
Now, onto IRR.
------------------------------
Shubho Ghosal
Original Message:
Sent: 11-27-2018 12:12 PM
From: Shubho Ghosal
Subject: INSTRUCTIONS NEEDED: IRR/NPV
IRR/NPV calculations are frequently used in financial modeling around cash flows.
Please provide an easy (or easiest) to implement technique on how to implement IRR/NPV in TM1.
------------------------------
Shubho Ghosal
------------------------------
#PlanningAnalyticswithWatson