Apologies if the terms are worded differently in English, but you'll understand what I mean.
I was looking to do some mortgage calculations in TM1. First off, I started to fill a cube with four dimensions:
- a dimension containing different Loan amounts
- a dimension containing different Interest rates
- a dimension containing different Durations (in years)
- a dimension containing different periodic Payments
If you know 3 out of 4, you can calculate the other one. Except that... TM1 does not have a function for all of the 4 (Excel has all 4 and many more).
You can use the PV function to calculate present values (Loan amount), and the PAYMT function to calculate periodic (for instance monthly) payments. No built-in function for the duration or the interest rate.
I wrote a TI process to create such a cube, do the calculations within TI and fill the cube with the results. Nothing dynamic, so adding an element will not calculate its results.
I have done the same using rules, but I could not solve it for Interest rates (since these require a loop). If someone is interested in the rules, please leave a message.
Wim
The code in the Prolog tab:
Code: Select all
vCubeName='Mortgage';
# Loan amount
vLoanAmountDimName='Loan amount';
vLoanAmountLowerBound=80000;
vLoanAmountUpperBound=150000;
vLoanAmountStep=1000;
# Interest rate
vInterestRateDimName='Interest rate';
vInterestRateLowerBound=0.04;
vInterestRateUpperBound=0.05;
vInterestRateStep=0.0005;
# Duration
vDurationDimName='Duration';
vDurationLowerBound=20;
vDurationUpperBound=30;
vDurationStep=5;
# Payment
vPaymentDimName='Payment';
vPaymentLowerBound=500;
vPaymentUpperBound=900;
vPaymentStep=25;
#####################
## CREATING DIMENSIONS
#####################
# 1. Handling the dimension for Loan amount
IF(DIMENSIONEXISTS(vLoanAmountDimName)=0);
DIMENSIONCREATE(vLoanAmountDimName);
ELSE;
DIMENSIONDELETEALLELEMENTS(vLoanAmountDimName);
ENDIF;
DIMENSIONELEMENTINSERT(vLoanAmountDimName,'','dummy','N');
i=vLoanAmountLowerBound;
While(i <= vLoanAmountUpperBound);
DIMENSIONELEMENTINSERT(vLoanAmountDimName,'',NUMBERTOSTRING(i),'N');
i=i+vLoanAmountStep;
End;
# 2. Handling the dimension for Interest rate
IF(DIMENSIONEXISTS(vInterestRateDimName)=0);
DIMENSIONCREATE(vInterestRateDimName);
ELSE;
DIMENSIONDELETEALLELEMENTS(vInterestRateDimName);
ENDIF;
DIMENSIONELEMENTINSERT(vInterestRateDimName,'','dummy','N');
i=vInterestRateLowerBound*100;
While(i <= vInterestRateUpperBound*100);
DIMENSIONELEMENTINSERT(vInterestRateDimName,'',NUMBERTOSTRING(i/100),'N');
i=i+vInterestRateStep*100;
End;
# 3. Handling the dimension for Duration
IF(DIMENSIONEXISTS(vDurationDimName)=0);
DIMENSIONCREATE(vDurationDimName);
ELSE;
DIMENSIONDELETEALLELEMENTS(vDurationDimName);
ENDIF;
DIMENSIONELEMENTINSERT(vDurationDimName,'','dummy','N');
i=vDurationLowerBound;
While(i <= vDurationUpperBound);
DIMENSIONELEMENTINSERT(vDurationDimName,'',NUMBERTOSTRING(i),'N');
i=i+vDurationStep;
End;
# 4. Handling the dimension for Payment
IF(DIMENSIONEXISTS(vPaymentDimName)=0);
DIMENSIONCREATE(vPaymentDimName);
ELSE;
DIMENSIONDELETEALLELEMENTS(vPaymentDimName);
ENDIF;
DIMENSIONELEMENTINSERT(vPaymentDimName,'','dummy','N');
i=vPaymentLowerBound;
While(i <= vPaymentUpperBound);
DIMENSIONELEMENTINSERT(vPaymentDimName,'',NUMBERTOSTRING(i),'N');
i=i+vPaymentStep;
End;
###################
## CREATING THE CUBE
###################
IF(CUBEEXISTS(vCubeName)=0);
CUBECREATE(vCubeName,vLoanAmountDimName,vInterestRateDimName,vDurationDimName,vPaymentDimName);
ELSE;
VIEWCREATE(vCubeName,'ZeroOut');
VIEWZEROOUT(vCubeName,'ZeroOut');
VIEWDESTROY(vCubeName,'ZeroOut');
ENDIF;Code: Select all
###############################
## CALCULATE THE DUMMY ELEMENTS
###############################
# 1. Calculate Loan amount and put it on element 'dummy'
b=vInterestRateLowerBound;
WHILE(b<=vInterestRateUpperBound);
c=vDurationLowerBound;
WHILE(c<=vDurationUpperBound);
d=vPaymentLowerBound;
WHILE(d<=vPaymentUpperBound);
vResult=PV(d,b/12,c*12);
CELLPUTN(vResult,vCubeName,'dummy',NUMBERTOSTRING(b),NUMBERTOSTRING(c),NUMBERTOSTRING(d));
d=d+vPaymentStep;
END;
c=c+vDurationStep;
END;
b=b+vInterestRateStep;
END;
# 2. Calculate Interest rate and put it on element 'dummy'
a=vLoanAmountLowerBound;
WHILE(a<=vLoanAmountUpperBound);
c=vDurationLowerBound;
WHILE(c<=vDurationUpperBound);
d=vPaymentLowerBound;
WHILE(d<=vPaymentUpperBound);
b=0.04;
WHILE(b<=0.06);
IF(PV(d,b/12,c*12)>=a & PV(d,(b+0.0005)/12,c*12)<=a);
vResult=b;
CELLPUTN(vResult,vCubeName,NUMBERTOSTRING(a),'dummy',NUMBERTOSTRING(c),NUMBERTOSTRING(d));
b=0.06;
ENDIF;
b=b+0.0005;
END;
d=d+vPaymentStep;
END;
c=c+vDurationStep;
END;
a=a+vLoanAmountStep;
END;
# 3. Calculate Duration and put it on element 'dummy'
a=vLoanAmountLowerBound;
WHILE(a<=vLoanAmountUpperBound);
b=vInterestRateLowerBound;
WHILE(b<=vInterestRateUpperBound);
d=vPaymentLowerBound;
WHILE(d<=vPaymentUpperBound);
IF(a/d*(b/12)<1);
vResult=-LN(1-(a/d*(b/12)))/LN(1+(b/12));
CELLPUTN(vResult,vCubeName,NUMBERTOSTRING(a),NUMBERTOSTRING(b),'dummy',NUMBERTOSTRING(d));
ENDIF;
d=d+vPaymentStep;
END;
b=b+vInterestRateStep;
END;
a=a+vLoanAmountStep;
END;
# 4. Calculate Payment and put it on element 'dummy'
a=vLoanAmountLowerBound;
WHILE(a<=vLoanAmountUpperBound);
b=vInterestRateLowerBound;
WHILE(b<=vInterestRateUpperBound);
c=vDurationLowerBound;
WHILE(c<=vDurationUpperBound);
vResult=PAYMT(a,b/12,c*12);
CELLPUTN(vResult,vCubeName,NUMBERTOSTRING(a),NUMBERTOSTRING(b),NUMBERTOSTRING(c),'dummy');
c=c+vDurationStep;
END;
b=b+vInterestRateStep;
END;
a=a+vLoanAmountStep;
END;