What is the calculation behind PMT and IPMT function

[GuruT]

I need to explain to my student how is PMT and IPMT work. Does anyone can explain what is the equation behind those formulas ?

[BillieJ]

Excel's financial functions (PMT, RATE, IPMT, NPER, FV) are one family with one basic formula. That is the formula for PV. It is described fully in Excel Help and I can't reproduce it here because of the math symbols.

[JustineBB]

The basic formula underlying Excel's financial functions a To solve for the future value, the formula is:

• fv=-if(rate=0,pmt*nper+pv,(pv*((1+rate)^nper)+pmt*(1+r ate*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+ra te)^nper-1)/rate)/((1+rate)^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 formula).

The fv, pv, pmt, nper and rate variables are explained in Excel's Help on PV. Functions like ipmt are variations on the above, as also described in Excel's Help file.

[Panop]

Just to add, the formula is found under the PV function in Help. See the link. The other key elements are based on an algebraic manipulation of thisformula to get the unknown value alone on one side of the equal sign.

Microsoft Excel PV

[Koman]

I have list of some function which with relevant information what they do.

• PMT Returns the periodic payment for an annuity.
• CUMIPMT Returns the cumulative interest paid between two periods.
• IPMT Returns the interest payments on an investment over a given period.
• PPMT Returns the payment of principal of an investment over a given period.

[Leanne]

The function = PMT () function calculates the periodic payments that we have to "drop" on a loan at an interest rate determined, in a time x. I wonder will go to those who want a loan or you are already paying. We see how much we have to pay monthly, or as we nail the banks of interest. We can all play different capitals, years, or interest rates. The command syntax is:= PMT (Interest, Time, Capital).

This formula will calculate the annual payment. If you want to know the monthly payments will have to divide the interest by 12 and multiply the time by 12. Observe:= PMT (Interest/12; Time * 12; Capital)