Interest Payment and Principle Payment Functions

Highlighted
Previous Contributor

Interest Payment and Principle Payment Functions

Is there any easy way to replicate the IPMT() for Interest Payments and PPMT() for Principle Payment functions in M. Excel within Anaplan?   I can only find a PMT() function in Anaplan.

1 REPLY 1
Highlighted
Previous Contributor

RE: Interest Payment and Principle Payment Functions

Anaplan does not currently have functions for these calculations, but I did find these formulas for calculating the necessary components.  You'd have to create a module to calculate the values over time. To solve for the future value, the formula is: fv=-if(rate=0,pmt*nper+pv,(pv*((1+rate)^nper)+pmt*(1+rate*type)*((1+rate)^nper -1)/rate)) To solve for the present value, the formula is: pv=-if(rate=0,pmt*nper+fv,(fv+pmt*(1+rate*type)*((1+rate)^nper-1)/rate)/((1+ra te)^nper)) To solve for the payment value, the formula is: pmt=-if(rate=0,(pv+fv)/nper,(pv*((1+rate)^nper)+fv)/((1+rate*type)*((1+rate)^n per-1)/rate)) To solve for the number of periods, the formula is: nper=-if(rate=0,(pv+fv)/pmt,(log(1+(pv+pmt*type)/pmt*rate)-log(1+(fv+pmt*type) /pmt*rate))/log(1+rate)) To solve for the interest rate, the formula is: rate=(fv/pv)^(1/nper)-1 if pmt is 0. Otherwise, you can only solve for the interest rate through iteration (eg using one of the above formulae). This link may help as well:  http://doc.optadata.com/en/dokumentation/application/expression/functions/financial.html.