function rate in Cognos TM1 Аrchitect

[Wirt]

hello,

I have a question about function in Cognos TM1.

I have a situation, that I must be transferred from the Excel to Cognos TM1 Аrchitect function RATE.

its transcription in Excel - RATE ( number of periods; PMT; amount of credit)

financial functions that are in the manual are not suitable (FV, PAYMT, PV)

I know that PMT(Excel) = PAYMT(Аrchitect), but have no idea how to implement RATE in Аrchitect.

If anyone encountered this problem and knows the solution, I will be very grateful for the help.

Thanks!

[declanr]

Rate is an iterative function so you will need to create it in TI; fairly simple to do and tm1 will be more accurate than excel - if memory serves excel gives up after about 20 attempts.

[Wirt]

Thank you, but perhaps you own the information how to do it or where I can see how it is being implemented

[declanr]

Thank you, but perhaps you own the information how to do it or where I can see how it is being implemented

I had nothing on this and the last time I looked at it was a couple of years ago so my knowledge on such things would be rusty. I did however spend the last few minutes writing some messy code that might point you in the right direction, I haven't done any real testing so there might be some flaws and no doubt with a small amount of time it could be made more efficient but is just intended as a pointer:

Code: Select all

nPeriods = CellGetN ( sCub, sType, 'Periods' );
nValueInitial = CellGetN ( sCub, sType, 'Initial Value' );
nValuePeriod = CellGetN ( sCub, sType, 'Period Value' );
nValueFinal = CellGetN ( sCub, sType, 'Final Value' );

nResult = nValueInitial;
nFlagOld = 0;
nFlag = 0;
nRate = 0;
nIncrement = 0.01;
nAcceptableDifference = 0.0001;
iFinish = 0;
While ( iFinish = 0 );
	nResult = nValueInitial;
	If ( nFlagOld <> nFlag );
		nIncrement = nIncrement * -0.1;
	EndIf;	
	nRate = nRate + nIncrement;
	iCount = 1;
	iMax = nPeriods;
	While ( iCount <= iMax );
		nResult = ( ( nResult + nValuePeriod ) * nRate ) + ( nResult + nValuePeriod );
		iCount = iCount + 1;
	End;
	nFlagOld = nFlag;
	If ( nResult > nValueFinal );
		nFlag = 1;
	Else;
		nFlag = 0;
	EndIf;
	If ( Abs ( nResult - nValueFinal ) < Abs ( nAcceptableDifference ) );
		iFinish = 1;
	EndIf;
End;

CellPutN ( nRate, sCub, sType, 'Rate' );

That code just relates to the assumption that all payments are made at the beginning of a month and everything is input as positive values.


Edit - Straight after posting I decided to search for the subject in the forum and found the topic below; at first glance it looks like it will be a more tested starting point for you - I would always recommend searching the forum to see what people have done first:

http://www.tm1forum.com/viewtopic.php?f ... wtopic.php

[Wirt]

thanks for the help