Opening
Internal Rate of Return (IRR) is a metric frequently used in finance and capital budgeting to assess the comparative profitability of potential investments. It helps decision-makers prioritize where to allocate capital for the best financial outcomes. It represents the discount rate that makes the net present value (NPV) of all cash flows equal to zero.
Calculating IRR is mathematically complicated. Even the Microsoft Excel function uses a guess-and-check algorithm (reference). Unfortunately, TM1 does not offer a native IRR function. I’ve been researching this topic on-again/off-again for three years. While others have tried to build a solution for IRR in TM1, everything I’ve found online has one of four shortcomings:
- Does not work with SKIPCHECK: So cannot perform for large models.
- Uses Turbo Integrator Processes: So not real time.
- Uses tm1py: So not real time and requires a separate server to calculate IRR.
- Uses Java libraries: So not supported on IBM Cloud / V12.
My curiosity persisted in the face of a problem many have faced. I was able to come up with this methodology which has been used in a production environment for over a year. I wanted to share it for the benefit of all TM1ers: benefactors, users, power users, and developers.
Benefits of methodology
- Rule-based, not process-based: Users don’t want to click a button and wait; Also, how would process-based approach work with modern user experience with bespoke consolidations?
- SKIPCHECK-enabled: Other solutions found online don’t work without checking all cells which doesn’t scale.
- No FEEDERS required as this uses C-level rules.
- Zero suppressible: Not a concern with modern IBM Planning Analytics user experiences but can understand how this may be important to some.
- Scalable and calculates values “instantaneously”: Love this!
- Plugs into existing cube design: Very straightforward approach to calculate IRR.
Dimensions to create for IRR capability
IRR Iteration
- Create a dimension with 20 top-level numeric-type elements (i.e. the numbers 1 through 20).
- Represents each iteration of the IRR calculation.
- Flexibility: You can add more iterations by adding more elements, but accuracy does not materially change after 20 iterations using this algorithm
Measure – IRR
- Create a dimension with 3 top-level numeric-type elements for the IRR calculation:
- IRR Minimum
- IRR Maximum
- IRR Guess
- Format each of these elements to your standard number format.
Dimension to modify (that already exist in your model) for IRR capability
Measure – Valuation Model
- Your tm1 model will already have a Valuation Model measure dimension.
- That dimension will already have a Net Cash Flows element.
- For this example, the Net Cash Flows element is represented as a numeric-type element for simplicity only. In your model it may be a consolidated-type element with deep layers of consolidation. This algorithm is able to handle that complexity.
- Create a consolidated-type element named IRR. Its weight should be 0. Set its number formatting to %.
- Add to the above IRR element, a consolidated-type element named DCF @ IRR – Step 20. This represents the 20th iteration of the IRR calculation. Its weight should be 1. Set its number formatting to #.
- Add to the above DCF @ IRR – Step 20, a consolidated-type element named DCF @ IRR – Step 19. This represents the 19th iteration of the IRR calculation. Its weight should be 1. Set its number formatting to #.
- (Repeat the above until you get to…)
- Add to the above DCF @ IRR – Step 2, a consolidated element named DCF @ IRR – Step 1. This represents the 1st iteration of the IRR calculation. Its weight should be 1. Set its number formatting to #.
- Add to DCF @ IRR – Step 1:
- The Net Cash Flow element or consolidation. Its weight should be 1.
- A numeric-type element named Year Number. The time dimension in this model is year-based. Its weight should be 0.
Other dimensions that exist in your model
Project
- No special considerations for the Project dimension, which likely already exists in the model.
Scenario
- No special considerations for the Scenario dimension, which likely already exists in the model.
Time
- The Time dimension for this model is Years.
- The Time dimension has a Prior Year string attribute.
Cube to create for IRR capability
IRR
- One you set up this cube and apply rules, you’re not going to spend a lot of time here, rather all of the action is in the Valuation Model cube.
- The IRR cube can have as many context dimensions as you’d like. In this example, there are two: Project and Scenario.
- IRR Iteration will be drilled down.
- Measure – IRR will be drilled across.
- Apply the rules in the appendix 1 at the bottom of this whitepaper to the IRR cube.
- It is possible to simplify the number of lines in the rule file using lookups, but is that calculation overhead worth it?
Cube to modify (that already exist in your model) for IRR capability
Valuation Model
- The Valuation Model cube should already exist in your model, and has as many context dimensions as you’d like. In this example, there are two: Project and Scenario
- Measure – Valuation Model will be drilled down
- Time will be drilled across
- Apply the rules in the appendix 2 at the bottom of this whitepaper to the Valuation Model cube.
How the IRR algorithm works in the IRR cube
- Calculations underneath IRR Calculation – Step 1
- Set the absolute minimum and maximum for the IRR range – in this example, cannot be lower than (100%) and higher than 100%
- Any IRR under 0% is nonsense but providing some flexibility for negative IRR
- Any IRR over 100% is equally nonsense, but feel free to adjust upward as needed. This may require adding iterations above 20 to solve accurately
- Calculations underneath IRR Calculation – Step 2
- Same across all IRR Iterations (Step)
- Connected to Valuation Model cube
- Valuation Model cube performs DCF calculation using IRR Guess from a certain IRR Iteration (Step)
- If result is a negative number:
- IRR Minimum is unchanged
- IRR Maximum is set to the guess
- If result is a positive number:
- IRR Minimum set to the guess
- IRR Maximum is unchanged
- If result is a negative number:
- Next guess is average of IRR Minimum and IRR Maximum
- Repeated 20 times, you solve for IRR
- For IRR Calculation – Step 20 the guess has a special condition: If the result is essentially 100%, it is force set to zero, meaning no solution. This is to hide nonsense results. Adjust this as needed.
How the IRR algorithm works in the Valuation Model cube
- If there’s a reasonable answer, IRR is the last guess (20th iteration / 20th step) in the IRR cube
- Calculations underneath DCF @ IRR – Step 20
- For All Years / Time Dimension Consolidation: You want to consolidate the immediate children because without this it will pull through the consolidation of Net Cash Flow. No significant performance hit.
- By Year, you want to apply the IRR Guess as the Discount Rate. If the sum across All Years is zero / close to zero you have the IRR.
- Iterative nature explained above for IRR cube
- Year number is a simple counter from when the first non-zero Net Cash Flow is recorded. Note this is intentionally the same rule across both c-type/numeric.
Conclusion
Using this algorithm, TM1 returns an IRR consistent with the Excel formula, virtually instantaneously, across any combination of numeric/consolidated selections. Try it and let me know your thoughts – reach out with any questions or comments. I wanted to emphasize the point that I’ve been using this in a production model for several years now.
Appendix 1 – Rules to add to IRR cube
SKIPCHECK;
# ------------------------
# IRR Calculation - Step 1
# ------------------------
['IRR Iteration':'1', 'Measure - IRR':'IRR Minimum'] = -1;
['IRR Iteration':'1', 'Measure - IRR':'IRR Maximum'] = 1;
['IRR Iteration':'1', 'Measure - IRR':'IRR Guess'] = 0;
# ------------------------
# IRR Calculation - Step 2
# ------------------------
['IRR Iteration':'2', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 1') <= 0,
['IRR Iteration':'1', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'1', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'2', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 1') >= 0,
['IRR Iteration':'1', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'1', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'2', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 3
# ------------------------
['IRR Iteration':'3', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 2') <= 0,
['IRR Iteration':'2', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'2', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'3', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 2') >= 0,
['IRR Iteration':'2', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'2', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'3', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 4
# ------------------------
['IRR Iteration':'4', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 3') <= 0,
['IRR Iteration':'3', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'3', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'4', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 3') >= 0,
['IRR Iteration':'3', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'3', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'4', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 5
# ------------------------
['IRR Iteration':'5', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 4') <= 0,
['IRR Iteration':'4', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'4', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'5', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 4') >= 0,
['IRR Iteration':'4', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'4', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'5', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 6
# ------------------------
['IRR Iteration':'6', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 5') <= 0,
['IRR Iteration':'5', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'5', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'6', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 5') >= 0,
['IRR Iteration':'5', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'5', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'6', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 7
# ------------------------
['IRR Iteration':'7', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 6') <= 0,
['IRR Iteration':'6', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'6', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'7', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 6') >= 0,
['IRR Iteration':'6', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'6', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'7', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 8
# ------------------------
['IRR Iteration':'8', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 7') <= 0,
['IRR Iteration':'7', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'7', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'8', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 7') >= 0,
['IRR Iteration':'7', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'7', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'8', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# ------------------------
# IRR Calculation - Step 9
# ------------------------
['IRR Iteration':'9', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 8') <= 0,
['IRR Iteration':'8', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'8', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'9', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 8') >= 0,
['IRR Iteration':'8', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'8', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'9', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 10
# -------------------------
['IRR Iteration':'10', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 9') <= 0,
['IRR Iteration':'9', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'9', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'10', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 9') >= 0,
['IRR Iteration':'9', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'9', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'10', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 11
# -------------------------
['IRR Iteration':'11', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 10') <= 0,
['IRR Iteration':'10', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'10', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'11', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 10') >= 0,
['IRR Iteration':'10', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'10', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'11', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 12
# -------------------------
['IRR Iteration':'12', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 11') <= 0,
['IRR Iteration':'11', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'11', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'12', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 11') >= 0,
['IRR Iteration':'11', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'11', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'12', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 13
# -------------------------
['IRR Iteration':'13', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 12') <= 0,
['IRR Iteration':'12', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'12', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'13', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 12') >= 0,
['IRR Iteration':'12', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'12', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'13', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 14
# -------------------------
['IRR Iteration':'14', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 13') <= 0,
['IRR Iteration':'13', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'13', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'14', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 13') >= 0,
['IRR Iteration':'13', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'13', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'14', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 15
# -------------------------
['IRR Iteration':'15', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 14') <= 0,
['IRR Iteration':'14', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'14', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'15', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 14') >= 0,
['IRR Iteration':'14', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'14', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'15', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 16
# -------------------------
['IRR Iteration':'16', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 15') <= 0,
['IRR Iteration':'15', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'15', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'16', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 15') >= 0,
['IRR Iteration':'15', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'15', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'16', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 17
# -------------------------
['IRR Iteration':'17', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 16') <= 0,
['IRR Iteration':'16', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'16', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'17', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 16') >= 0,
['IRR Iteration':'16', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'16', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'17', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 18
# -------------------------
['IRR Iteration':'18', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 17') <= 0,
['IRR Iteration':'17', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'17', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'18', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 17') >= 0,
['IRR Iteration':'17', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'17', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'18', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 19
# -------------------------
['IRR Iteration':'19', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 18') <= 0,
['IRR Iteration':'18', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'18', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'19', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 18') >= 0,
['IRR Iteration':'18', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'18', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'19', 'Measure - IRR':'IRR Guess'] = (['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2;
# -------------------------
# IRR Calculation - Step 20
# -------------------------
['IRR Iteration':'20', 'Measure - IRR':'IRR Minimum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 19') <= 0,
['IRR Iteration':'19', 'Measure - IRR':'IRR Minimum'],
['IRR Iteration':'19', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'20', 'Measure - IRR':'IRR Maximum'] =
IF(
DB('Valuation Model', !Project, !Scenario, 'All Years', 'DCF @ IRR - Step 19') >= 0,
['IRR Iteration':'19', 'Measure - IRR':'IRR Maximum'],
['IRR Iteration':'19', 'Measure - IRR':'IRR Guess']);
['IRR Iteration':'20', 'Measure - IRR':'IRR Guess'] =
IF(
ROUNDP((['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2, 0) = 1,
0,
(['Measure - IRR':'IRR Minimum'] + ['Measure - IRR':'IRR Maximum']) / 2);Appendix 2 – Rules to add to Valuation Model cube
SKIPCHECK;
['Measure - Valuation Model':'IRR', 'Time':'All Years'] = C: DB('IRR', !Project, !Scenario, '20', 'IRR Guess');
['Measure - Valuation Model':'IRR'] = C: 0;
['Measure - Valuation Model':'DCF @ IRR - Step 20', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 20'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '20', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 19', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 19'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '19', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 18', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 18'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '18', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 17', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 17'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '17', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 16', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 16'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '16', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 15', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 15'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '15', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 14', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 14'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '14', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 13', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 13'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '13', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 12', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 12'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '12', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 11', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 11'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '11', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 10', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 10'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '10', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 9', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 9'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '9', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 8', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 8'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '8', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 7', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 7'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '7', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 6', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 6'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '6', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 5', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 5'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '5', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 4', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 4'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '4', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 3', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 3'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '3', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 2', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 2'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '2', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'DCF @ IRR - Step 1', 'Time':'All Years'] = C: ConsolidateChildren('Time');
['Measure - Valuation Model':'DCF @ IRR - Step 1'] = C: ['Measure - Valuation Model':'Net Cash Flows'] \ (1 + DB('IRR', !Project, !Scenario, '1', 'IRR Guess')) ^ ['Measure - Valuation Model':'Year Number'];
['Measure - Valuation Model':'Year Number'] =
IF(
['Measure - Valuation Model':'Net Cash Flows'] <> 0,
DB('Valuation Model', !Project, !Scenario, DB('}ElementAttributes_Time', !Time, 'Prior Year'), 'Year Number') + 1,
CONTINUE);
['Measure - Valuation Model':'Year Number'] =
IF(
DB('Valuation Model', !Project, !Scenario, DB('}ElementAttributes_Time', !Time, 'Prior Year'), 'Year Number') <> 0,
DB('Valuation Model', !Project, !Scenario, DB('}ElementAttributes_Time', !Time, 'Prior Year'), 'Year Number') + 1,
0);