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']) ;

Here is the zTD_IRR_Year dimension

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