TM1 IRR Calc

TM1 IRR Calc

Postby paulsimon » Fri Oct 26, 2012 9:45 pm

Hi

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]
IRR.jpg
[/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
Attachments
IRR.jpg
IRR.jpg (197.05 KiB) Viewed 456 times
User avatar
paulsimon
MVP
 
Posts: 378
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 9.5.1 HF24
Excel Version: 2003

Re: TM1 IRR Calc

Postby Harvey » Mon Oct 29, 2012 11:55 am

This should probably find it's home in the "Useful Code, Tips and Tricks" forum...
Flow OLAP - adding value to Cognos Partners worldwide.
User avatar
Harvey
Community Contributor
 
Posts: 197
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: TM1, CX, Palo
Version: 9.4+
Excel Version: 2003+


Return to Cognos TM1

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 9 guests

Loading