Page 1 of 1

TM1 IRR Calc

Posted: Fri Oct 26, 2012 9:45 pm
by paulsimon
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
IRR.jpg (197.05 KiB) Viewed 11407 times
[/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

Re: TM1 IRR Calc

Posted: Mon Oct 29, 2012 11:55 am
by Harvey
This should probably find it's home in the "Useful Code, Tips and Tricks" forum...

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 1:01 am
by kidyee
Hi paulsimon

Your IRR calc is excellent.!
I have used IRR on Rule reference by your posting

IRR cube basically too big. so It is necessity to the use of SKIPCHECK & FEEDERS in rule.
but.I'm thoroughly confused to the use of SKIPCHECK & FEEDERS

Especially like this Statement difficult for me.

Code: Select all

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

I need help.

I'm not good English.
understands me.
thanks for read my word.

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 3:56 am
by BariAbdul
IRR cube basically too big. so It is necessity to the use of SKIPCHECK & FEEDERS in rule.
but.I'm thoroughly confused to the use of SKIPCHECK & FEEDERS
As far as I understand,SkipCheck and feeders are not needed:
1.Cube is highly dense
2. Cube contains only strings.
So,Check the sparsity of your cube.You can estimate density by answering this question: If one element in the dimension has a value, keeping the elements of the other dimensions constant, what is the probability that the other elements in the dimension have a value? Thanks

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 4:34 am
by kidyee
I just need to FEEDERS.
So. I try many time.
that result is like this

Code: Select all

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

FEEDERS;
['50'] => ['Figures'];
['Figures'] => ['Tolerance'];
['Y_All IRR Years'] => ['Year Num'];
['01'] => ['IRR Change'];

['Y_All IRR Years'] => ['All Iterations'];
['Figures'] => ['02'];

['Y_All IRR Years'] => ['IRR Change','Year Num' ];
['IRR Change'] => ['IRR'];
['All Iterations'] => ['IRR'];
['IRR'] => ['IRR Reciprocal'];
['Figures'] => DB('zTD_IRR', !zTD_IRR_Year, !zTD_IRR_Iteration);
But I'm not sure If my coding is rights or wrong.
※ once running well.

I'm need recommend to my feeders coding.

thanks.

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 7:43 am
by Steve Rowe
Hi Kidyee,

If your example is the same as Pauls then feeders are not required since the cube is 100% dense, i.e. every cell is populated with numbers. In this situation skipcheck / feeders don't help as there is no blank space to skip.

If it is not like Pauls example then you'll need to share some detail but I would say that almost definitely the feeder would be the value in the equivalent position to the figures column in Pauls example.

Cheers,

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 8:35 am
by kidyee
Steve Rowe wrote:Hi Kidyee,

If your example is the same as Pauls then feeders are not required since the cube is 100% dense, i.e. every cell is populated with numbers. In this situation skipcheck / feeders don't help as there is no blank space to skip.

If it is not like Pauls example then you'll need to share some detail but I would say that almost definitely the feeder would be the value in the equivalent position to the figures column in Pauls example.

Cheers,
Good to see you Steve Rowe
according to your opinion, 100% dense cube is not required skipcheck& feeders right?
I make sense new. thanks.

so, I have question.
Q1. how % cube have needed skipchek & feeders
Q2. charactor data need not skipckek & feeders? right?

Re: TM1 IRR Calc

Posted: Tue Jun 02, 2015 12:00 pm
by Steve Rowe
Hi,

Q1. I'm not going to attempt to put an exact % on this, it really depends on the number of dimensions in the cube and how hard the rules are working. Bottom line is the more sparse your data is and the harder the rules are working the greater the benefit of feeding the rules.

Q2. The decision to feed or not feed is the same irrespective of strings or data. Often text data is close to 100% dense and so may not need feeding.

Cheers

Re: TM1 IRR Calc

Posted: Wed Jun 03, 2015 9:12 pm
by paulsimon
Hi Kidyee

It was a long time ago since I wrote that IRR code. It was only ever intended to show that an IRR calc could be done in TM1. I did mention that it might be more efficient in TI or to just use Excel's IRR formula and DBS in the value.

However, I am curious as to why you are saying that it is using to much memory. The example I produced was tiny.

Are you perhaps not showing the real code that you are using? Are you perhaps trying to apply the IRR calc for a thousand cost centres perhaps combined with some other splits?

In that case you will need feeders. I would suggest that you keep the IRR area fully fed, but only feed if there are values entered against the cost centre.

Regards

Paul Simon

Re: TM1 IRR Calc

Posted: Thu Jun 04, 2015 12:59 am
by Oratia623
Hi,
As another option (and in my mind a superior one) we now use java to calculate our IRRs.
We trialed these rules and a cube/TI combination option and the java results are 100% accurate to excel, and blisteringly fast, compared to other alternatives
So, we now have a TI process that generates the cashflow from the source (cube), and then calls a "home-built" java function that uses an embedded opensource IRR java class to actually calculate the IRR. This is then returned to the TI to be written back to TM1. No need for rules, feeders or dedicated cubes.
From a standing start of knowing nothing about java, it took me about 2 (very hard) days to get there to a first working prototype.

Paul

Re: TM1 IRR Calc

Posted: Thu Jun 04, 2015 1:53 am
by nick_leeson
Hi,
As another option (and in my mind a superior one) we now use java to calculate our IRRs.
We trialed these rule rules and a cube/TI combination option and the java results are 100% accurate to excel, and blisteringly fast, compared to other alternatives
So, we now have a TI process that generates the cashflow from the source (cube), and then calls a "home-built" java function that uses an embedded opensource IRR java class to actually calculate the IRR. This is then returned to the TI to be written back to TM1. No need for rules, feeders or dedicated cubes.
From a standing start of knowing nothing about java, it took me about 2 (very hard) days to get there to a first working prototype.
Thank you for the idea :D

Re: TM1 IRR Calc

Posted: Thu Jun 04, 2015 9:29 am
by kidyee
Hello paulsimon

I'm not sure if I understand your word or not. because I'm not in English Culture
anyway My Cube has 6 Dimensions
COM_Year has 10 elements('2011' ~ '2020')
COM_Month has 13 elements('Jan' ~ 'Dec' and 'Full_Year')
COM_Day has 32 elements ('01' ~ '31' and 'Full_Month')
COM_NoWithTotal 1008 elements ('1' ~ ' 1008') This Dimensions make One day tranjection for contract
TY_DailyIRR_Iteration 64 elements ('Figures', 'ZeroCNT', 'Payment Num', 'All Iterations', and '1st' ~ '59th')
TY_DailyIRR_Payment 97 elements ('Contract No.','Product_Type','Segment3','IRR','IRR Change','IRR Reciprocal','Tolerance','Financed Amount','Total IDC','Total Subsidy','All IRR Payment','Initial Cash Outflow' and 'Payment 1' ~ 'Payment 84' and 'Residual Value')

Here is my rule

Code: Select all

#SKIPCHECK;
# INITIAL SETTING FOR IRR
['IRR Change','Figures'] =
	N:IF(DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, 'Contract No.') @= ''
		,0
		,0.1
	);
['Tolerance' ,'Figures'] =
	N:IF(DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, 'Contract No.') @= ''
		,0
		,1
	);

['Initial Cash Outflow', 'Figures'] = N:['TotalSubsidy','Figures'] - ['Financed Amount','Figures'] - ['Total IDC','Figures'];
['IRR','Figures']=(['IRR','59th'] - 1)*12;
#######

['Figures' ] = N:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
['Figures' ] = S:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
#['Figures' ] = N:STET ;
['Tolerance'] = N:['Tolerance','Figures'];
['Product_type'] = ['Contract No.'];


#Payment period Calculation for IRR
['ZeroCNT'] = N:IF( ['Figures'] = 0, 1, 0);
['PayCNT'] =
	N:IF(dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' ) > 0
		,dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' )
		,0
	);
['Residual Value','Payment Num'] =
	N:IF( ['Figures','Residual Value'] = 0, 0, ['Residual Value','PayCNT']-['All IRR Payment','ZeroCNT']);

['Payment Num'] =
	N:IF(dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' ) > 0
		,IF( ['Figures'] = 0
			,0
			,dimix( 'TY_DailyIRR_Payment', !TY_DailyIRR_Payment ) - dimix( 'TY_DailyIRR_Payment', 'All IRR Payment' )
		)
		,0
	);

# Initial guess at IRR
['IRR','1st'] = N:ABS( ['All IRR Payment','Figures'] \ ['Initial Cash Outflow','Figures']  ) ;

# Basic second change
['IRR Change','2nd'] =
	IF( ['All IRR Payment','1st'] > 0
		,['IRR Change','Figures']
		,-1 * ['IRR Change','Figures']
	) ;

# Later Changes until Tolerance reached
['IRR Change'] =
	N:IF( ABS( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'All IRR Payment') ) < ['Tolerance','Figures']
	# N:IF( ABS( ['All IRR Payment']-1previous cell ) < ['Tolerance','Figures']
		,0
		,DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'IRR Change')
		#['IRR Change']-1previous cell
		*IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'All IRR Payment') > 0
		#*IF(['All IRR Payment']-1previous cell > 0
			,IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 2 ), 'All IRR Payment') > 0
			#*IF(['All IRR Payment']-2previous cell > 0
				,1.1
				,-0.1
			)
			,IF( DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 2 ), 'All IRR Payment') > 0
			#*IF(['All IRR Payment']-2previous cell > 0
				,-0.1
				,1.1
			)
		)
	);

['IRR'] = N:DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, dimnm( 'TY_DailyIRR_Iteration', dimix( 'TY_DailyIRR_Iteration', !TY_DailyIRR_Iteration) - 1 ), 'IRR') + ['IRR Change'] ;
['IRR Reciprocal']=N:1\['IRR'];
[]=N:['Figures'] * ( ['IRR Reciprocal'] ^ ['Payment Num']) ;

#FEEDERS;
# INITIAL SETTING FOR IRR
#['TotalSubsidy','Figures'] => ['Initial Cash Outflow', 'Figures'];
#['Financed Amount','Figures'] => ['Initial Cash Outflow', 'Figures'];
#['Total IDC','Figures'] => ['Initial Cash Outflow', 'Figures'];

#['IRR','59th'] => ['IRR','Figures'];
#######

#['Figures' ] = N:STET ;
#['Tolerance','Figures'] => ['Tolerance'];

#Payment period Calculation for IRR

#['All IRR Payment'] => ['PayCNT'];

#['Residual Value','PayCNT'] => ['Residual Value','Payment Num'];
#['All IRR Payment','ZeroCNT'] => ['Residual Value','Payment Num'];

#['All IRR Payment'] => ['Payment Num'];

# Initial guess at IRR
#['All IRR Payment','Figures'] => ['IRR','1st'];
#['All IRR Payment'] => ['All Iterations'];

# Basic second change
#['IRR Change','Figures'] => ['IRR Change','2nd'];

# Later Changes until Tolerance reached
#['IRR Change'] => ['IRR Change'];
#['All IRR Payment'] => ['IRR Change'];
#['IRR Change'] => ['IRR'];
#['IRR'] => ['IRR'];
#['IRR'] => ['IRR Reciprocal'];
#['Figures'] => DB('TY_Daily_cIRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Iteration, !TY_DailyIRR_Payment);
Image
Image
Image
Image
I hide contract no element for security

But I used skipcheck & feeders But Saved not (I'm about to push save button and not repones.)

Re: TM1 IRR Calc

Posted: Thu Jun 04, 2015 10:22 pm
by Steve Rowe
I'd suggest that you have a think about feeders and get straight in your head exactly what a feeder is and what its for.

You are simply telling the engine when the value is going to generate a non-zero result, generally it is the rule "backwards", but it doesn't have to be you can use whatever is convenient to flag the calc to take place. It also does not need to be 100% correct, you can over feed the values and providing you are happy with the calculation speed the job is done. (Under feeding is obviously bad as you get the wrong answer).

These two lines form the source of the calculations

['Figures' ] = N:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);
['Figures' ] = S:DB('int_TY_Daily_IRR', !COM_Year, !COM_Month, !COM_Day, !COM_NoWithTotal, !TY_DailyIRR_Payment);

The feeder for Figures needs to go in the rule sheet for int_TY_Daily_IRR cube, I don't think that's the rule sheet you have shown, not sure. I assume that is where you are picking up the starting conditions. All the stuff above All IRR Payment.

Then use one of the starting conditions Financed Amount, say to feed the consolidation All IRR Payment, I think that will do the job.

If that works and performs I'd leave it at that.

If it doesn't then you need to get clever and stop the feeder from firing when Payment XX is going to evaluate to 0, this is going to be much harder as you will probably need to repeat the maths in the feeder calc. Probably not be worth the effort since you only have 86 elements in the consolidation and I wouldn't worry about the fact that sometimes the Payment elements are over fed.

Put another way I'd regard each column in your view as either needing to be fed or not and then just feed it with the one thing that indicates that there is work to do.

What I would also look at is ensuring that payment calc evaluates to zero so that it does the minimum amount of work when it is overfed

An obvious problems

This is circular, not sure what would happen, don't think I've tried but it won't make the rule engine happy.
#['IRR'] => ['IRR'];

Hope this helps,

Steve