Whate the IRR and Rate Function in TM1 Rule use?

Post Reply
icejung
Posts: 3
Joined: Sun May 16, 2010 12:58 pm
OLAP Product: IBM COGNOS TM1
Version: 9.5
Excel Version: 2007

Whate the IRR and Rate Function in TM1 Rule use?

Post by icejung »

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.
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by Michel Zijlema »

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
icejung
Posts: 3
Joined: Sun May 16, 2010 12:58 pm
OLAP Product: IBM COGNOS TM1
Version: 9.5
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by icejung »

Thank you, Michel.
icejung
Posts: 3
Joined: Sun May 16, 2010 12:58 pm
OLAP Product: IBM COGNOS TM1
Version: 9.5
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by icejung »

Dear All,

I want to solve IRR by this Formula __ ___
| Finance_EXE_Vat + Commission |
|______________________________|
Finance_Exc_Vat = SUM| Term |
|______________________________|
| (n-1) |
| (1+r) |
___ ___
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by Wim Gielis »

Did you read my post?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
stex2727
Posts: 66
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by stex2727 »

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
busdee
Posts: 7
Joined: Tue Apr 27, 2010 4:45 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by busdee »

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 =)
stex2727
Posts: 66
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by stex2727 »

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
Attachments
IRR.zip
(31.78 KiB) Downloaded 684 times
busdee
Posts: 7
Joined: Tue Apr 27, 2010 4:45 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by busdee »

Hey thanks. This rule is work well =)
chewza
Posts: 146
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Re: Whate the IRR and Rate Function in TM1 Rule use?

Post by chewza »

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
Post Reply