Interest Payment and Principle Payment Functions

PrevContributor
PrevContributor
in Modeling

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.

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    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.

Categories