IPMT function

In this lesson you can learn how to use IPMT function in Excel.

This function calculates the amount of interest you have to pay in a given period, assuming that the interest rate and monthly payment in subsequent periods are fixed. Interest with the amount repayable installment loans are periodically calculated using the PMT function

The syntax of this function, the formula is as follows:

=IPMT(rate,per,nper,pv,[fv],[type])

  • rate - the interest rate period - the period for which interest is calculated
  • per - a period to be determined by a percentage (a range of values is from 1 to nper)
  • nper - Total number of payments during the loan repayment or saving
  • pv - the current value of the loan taken
  • fv - the amount you intend to accumulate in your account after the last payment
  • type - 0 is a payment at the beginning of the period and 1 is a payment at the end of the period

 

Example 1 basic calculation of interest rate

Mr. X took the loan of 20 000 $ for 2 years. The interest rate is 25% per annum and the loan will be repaid in monthly installments. Calculate the interest in 7th month.

In any cell enter the formula: =IPMT(0.25/12, 7, 12*2, -20000)

  • 0.25/12 is interest rate per month
  • 7 is a number of installment, because you are looking for seventh installment
  • 12*2 is a number of installments- it is 2 years what is 24 months
  • -20000 is an amount of credit with minus (thats not your money)

The result is 330,96$

Example 2 IPMT basic calculation

What is the value of the interest in the first month of the loan amounting to 1 000 000 $ bearing interest of 8% per annum taken for three years.

In any cell enter the formula: =IPMT(0.08/12, 1, 12*3, -1000000)

  • 0.08/12 is interest rate per month
  • 1 is a number of installment, because you are looking for first installment
  • 12*3 is a number of installments - it is 3 years what is 36 months
  • -1000000 is an amount of credit with minus (thats not your money)

The result is 6 666,67$

 

Example 3 IPMT calculating for 12th month

What is the value of the interest in the twelfth month of the loan amounting to 2 000$ bearing interest of 10% per annum taken for five years.

In any cell enter the formula: =IPMT(0.1/12, 12, 12*5, -2000)

  • 0.1/12 is interest rate per month
  • 12 is a number of installment, because you are looking for twelfth installment
  • 12*5 is a number of installments - it is 5 years what is 60 months
  • -2000 is an amount of credit with minus (thats not your money)

The result is 14,2$

 

Example 4 Bit difficult IPMT to calculate

What is the value of the interest in the eighth week of the loan amounting to 12 000$ bearing interest of 2% per annum taken for ten years. Payments are due at the beginning of each week.

In any cell enter the formula: =IPMT(0.02/12, 8, 52*10, -12000, 0, 1)

  • 0.02/12 is interest rate per month
  • 8 is a number of installment, because you are looking for eighth installment
  • 52*10 is a number of installments - it is 10 years what is 52 each year
  • -12000 is an amount of credit with minus (thats not your money)
  • 0 means that you want pay whole credit
  • 1 means that you will pay at the beginning of each period

The result is 19,8$

Try to calculate amount of the interest when you pay at the end of period. Interest will be higher (but not much here).