Page 1 of 1
function rate in Cognos TM1 Аrchitect
Posted: Tue Apr 01, 2014 2:05 pm
by 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!
Re: function rate in Cognos TM1 Аrchitect
Posted: Tue Apr 01, 2014 2:31 pm
by 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.
Re: function rate in Cognos TM1 Аrchitect
Posted: Tue Apr 01, 2014 2:39 pm
by Wirt
Thank you, but perhaps you own the information how to do it or where I can see how it is being implemented
Re: function rate in Cognos TM1 Аrchitect
Posted: Tue Apr 01, 2014 10:10 pm
by declanr
Wirt wrote: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
Re: function rate in Cognos TM1 Аrchitect
Posted: Wed Apr 02, 2014 8:27 am
by Wirt
thanks for the help