Someone asked about doing an IRR (Internal Rate of Return) calc in TM1 on a Linked In forum. I answered it there, so I thought I would re-post it here in case it is of any use to anyone else.
This can be done in TM1 Rules or alternatively using an iterative WHILE loop in TM1 Turbo Integrator.
Whether it is worth the effort compared to using built in functions in Excel and storing the result in TM1 is debatable.
The following are the rules that prove it - sorry no feeders.
The convergence algorithm is noddy and there are better ones on the web
[img] [/img]
Code: Select all
['IRR','Figures']=['IRR','50'] - 1;
['Figures']=N:STET ;
['Tolerance']=N:['Tolerance','Figures'];
['Year Num']=N: dimix( 'zTD_IRR_Year', !zTD_IRR_Year )
- dimix( 'zTD_IRR_Year', 'Y_All IRR Years' );
# Initial guess at IRR
['IRR','01']=N: ( ['Y_All IRR Years','Figures'] \ ABS( ['Y_2012','Figures'] ) ) ;
# Basic second change
['IRR Change','02']=N:
IF( ['Y_All IRR Years','01'] > 0
,
['IRR Change','Figures']
,
-1 * ['IRR Change','Figures']
) ;
# Later Changes until Tolerance reached
['IRR Change' ]=N:
IF( abs( DB('zTD_IRR','Y_All IRR Years',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )) )
< ['Tolerance','Figures']
,
0
,
DB( 'zTD_IRR','IRR Change',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )
)
*
IF( DB('zTD_IRR','Y_All IRR Years',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 ))
> 0
,
IF( DB('zTD_IRR','Y_All IRR Years',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 ))
> 0
,
1.1
,
-0.1
)
,
IF( DB('zTD_IRR','Y_All IRR Years',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 2 ))
> 0
,
-0.1
,
1.1
)
)
) ;
['IRR']=N: DB('zTD_IRR','IRR',
dimnm( 'zTD_IRR_Iteration', dimix( 'zTD_IRR_Iteration', !zTD_IRR_Iteration) - 1 )
)
+ ['IRR Change'] ;
['IRR Reciprocal']=N:1\['IRR'];
[]=N:['Figures'] * ( ['IRR Reciprocal'] ^ ['Year Num']) ;
IRR
IRR Change
IRR Reciprocal
Tolerance
Y_All IRR Years
Y_2012
Y_2013
Y_2014
Y_2015
Y_2016
Here is the zTD_IRR_Iteration dimension
Figures
Year Num
All Iterations
01
02
03
...
50
You enter what you want in the Figures column. It does the iterations and returns the result in the [IRR , Figures] combination.
I tested it with an initial IRR Change of 0.1 and a Tolerance of 1 and the following figures in Y_2012 to Y_2016
-5000, 2000, 300, 4999, 4444
and the result was pretty close to the Excel IRR.
Regards
Paul Simon