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:
Formula:
PMT = (rate * present value) / (1 - (1 + rate)^(-n))
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:
Formula:
PMT = (0.05/12 * 10000) / (1 - (1 + 0.05/12)^(-5*12))
PMT = $188.71
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:
Formula:
IPMT = rate * period * present value / n
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:
Formula:
IPMT = 0.05/12 * 3 * 10000 / (5*12)
IPMT = $41.67
This means that the third payment of $188.71 would consist of $41.67 in interest and $147.04 in principal.
- 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.
- Use the IPMT function to calculate the interest portion of a loan or investment payment for a specific period.