TM1 Forum

Dear All,

I have a question about financial function in rule.

IRR and RATE in Rule .

What the parameter or Syntax for use it?

Thank you.

icejung wrote:Dear All,

I have a question about financial function in rule.

IRR and RATE in Rule .

What the parameter or Syntax for use it?

Thank you.

Hi,

TM1 doesn't have built in functions for IRR and RATE. You could implement the functionality using TI, see f.i. this post.

Michel

Thank you, Michel.

Dear All,

I want to solve IRR by this Formula __ ___
| Finance_EXE_Vat + Commission |
|______________________________|
Finance_Exc_Vat = SUM| Term |
|______________________________|
| (n-1) |
| (1+r) |
___ ___

Did you read my post?

As a proof of concept I managed to get an IRR Calculation working with rules and in the process learned way more than one man should ever know about logarithmic calculations. The premise is that the IRR calculation is based on Log formulas (which TM1 has). To perform the calc in rules you really need to know how IRR is calculated and google will help you here. The other part about IRR to be aware of is that each time you perform the log formula it gets a little closer to the true IRR so the calculation needs to be iterative.

My model had two cubes (as I wanted to hide the Iterations dimension from the end user) and the calculation was performed on a monthly basis. One was the standard cashflows and the other was the same but with a dimension filled with 1-20 for iterations.

Please bear in mind this was only a proof of concept but below is the code I used.

CashflowIterations1 Rules

['Cashflow', 'base' ] =n:DB('Cashflow_Iterations', 'Cashflow', !Projects, !Time, !Versions);
['irr', 'period 1', '1'] = stet;
['Discounted Cashflow' ] =n: ['Cashflow' ] /(1+DB('zRates', !Versions, 'Discount Rate'))^(DIMIX('Time', !time)-1);
['NPV' ] =n:['Cashflow','base' ] /(1+['IRR','period 1']/12)^(DIMIX('Time', !time)-1);
['IRR' ] =n:
DB('Cashflow_Iterations1', dimnm('iterations',dimix('iterations',!Iterations)-1), 'IRR', !Projects, 'period 1', !Versions)
*
log((['Cashflow' ,'period 0', 'base'])/((['Cashflow' ,'total periods', 'base'])))
\
log(DB('Cashflow_Iterations1', dimnm('iterations',dimix('iterations',!Iterations)-1), 'npv', !Projects, 'total periods', !versions)
\(['Cashflow' ,'total periods', 'base']))

CashflowIterations Rules (for data entry and to hide the mechanics of the calculation)


['Discounted Cashflow' ] =n: ['Cashflow' ] /(1+DB('zRates', !Versions, 'Discount Rate'))^(DIMIX('Time', !time)-2);
['NPV', 'period 0' ] =n:DB('Cashflow_Iterations', 'discounted cashflow', !Projects, 'Total Periods', !Versions);
['IRR', 'period 0' ] = DB('Cashflow_Iterations1', '20', !IRR_m, !Projects, 'Period 1', !Versions);






regards
Stex

Hi Stex
The rule you write for IRR is quite interesting. I have a POC required to calculate IRR using rule too. I tried copy recreate cube according to your rule but it's not working. As your rule, I have these following questions:
- What is dimension of element named 'base'? is it in Version or Iterations dimension?
- It seems I must have data of Cashflow, ZRate, IRR Period1 Iteration1 before calculation. What is ZRate cube means for calculation and what source I get it from? As far as I know, IRR is not required discount rate to calculate, please collect me if I'm wrong. And How do I get 'IRR Period1 Iteration1'? The rule is stet this area so Does it means I need to input value before calculate IRR?


Now I have 3 cube;
CashflowIterations => times, versions, IRR M
CashflowIteration1=> times,versions, iterations, IRR M
ZRate => version, ZRate M
I imitate rule from your post and try to calculate, the result is the rule still error in IRR and NPV

Thanks =)

Attached is a cut down version of the model I created to demonstrate IRR calculations in TM1. You should be able to see the calculation by opening up the default view in the Cashflow_iterations cube. Place the model into c:\demos\ directory and you should have it.

Regards
Steve

Hey thanks. This rule is work well =)

Thanks very much - really appreciate sharing this solution!!

One big issue though is when trying to implement feeders.
I notice that in the sample this is switched off.
In a real-life situation, implementing feeders seems to make performance unworkable. Reason is really to do with having to feed everything across all iterations.

Any advice on how to get around this?

Many thanks!!

Regards
Chris