What is the real math behind PMT and IPMT function?
 

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

Regards

kudanil.

Answer: What is the real math behind PMT and IPMT function?
 

Explanation of PMT and IPMT Functions in Excel

PMT stands for Payment, and it calculates the periodic payment for a loan or investment based on a constant interest rate, constant payments, and a constant loan amount. The formula for PMT is:

Whe
- rate is the interest rate per period
- present value is the loan amount or present value of the investment
- n is the total number of payment periods

For example, if you have a $10,000 loan with a 5% annual interest rate and a 5-year term, the PMT formula would be:

This means that you would need to make monthly payments of $188.71 to pay off the loan in 5 years.

IPMT stands for Interest Payment, and it calculates the interest portion of a loan or investment payment for a specific period. The formula for IPMT is:

Whe
- rate is the interest rate per period
- period is the payment period for which you want to calculate the interest
- present value is the loan amount or present value of the investment
- n is the total number of payment periods

For example, if you want to calculate the interest portion of the third payment for the same $10,000 loan with a 5% annual interest rate and a 5-year term, the IPMT formula would be:

This means that the third payment of $188.71 would consist of $41.67 in interest and $147.04 in principal.

1. Use the PMT function to calculate the periodic payment for a loan or investment based on a constant interest rate, constant payments, and a constant loan amount.
2. Use the IPMT function to calculate the interest portion of a loan or investment payment for a specific period.

What is the real math behind PMT and IPMT function?
 

Hi kudanil,

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kudanil" wrote in message ...
|I need to explain to my student how is PMT and IPMT work. Does anyone can
| explain what is the equation behind those formulas?
|
| Regards
|
| kudanil.

What is the real math behind PMT and IPMT function?
 

Hi kudanil,

The basic formulae 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+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).

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.

Cheers

--
macropod
[MVP - Microsoft Word]


"Kudanil" wrote in message
...
| I need to explain to my student how is PMT and IPMT work. Does anyone can
| explain what is the equation behind those formulas?
|
| Regards
|
| kudanil.

What is the real math behind PMT and IPMT function?
 

Just to add, the formula is found under the PV function in Help:

For an OnLine version:

http://office.microsoft.com/en-us/ex...092251033.aspx

The other key elements are based on an algebraic manipulation of this
formula to get the unknown value alone on one side of the equal sign.

--
Regards,
Tom Ogilvy

"Niek Otten" wrote in message
...
Hi kudanil,

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kudanil" wrote in message
...
|I need to explain to my student how is PMT and IPMT work. Does anyone can
| explain what is the equation behind those formulas?
|
| Regards
|
| kudanil.

What is the real math behind PMT and IPMT function?
 

This article may be helpful as well:

http://office.microsoft.com/en-us/ex...117451033.aspx

--
Regards,
Tom Ogilvy

"macropod" wrote in message
...
Hi kudanil,

The basic formulae 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+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).

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.

Cheers

--
macropod
[MVP - Microsoft Word]


"Kudanil" wrote in message
...
| I need to explain to my student how is PMT and IPMT work. Does anyone
can
| explain what is the equation behind those formulas?
|
| Regards
|
| kudanil.