Excel > Functions

IPMT Function

The Excel IPMT function is used to calculate the interest paid during a period of a loan or investment.

It could be used by an investment company to find out the interest earned on a loan given to a customer. The IPMT function could be used to decide what interest rate to apply to the loan, as changing the rate will change the potential interest earned, ensuring the loan is worth giving.

The IPMT function can also be used to find out the amount of interest earned during a specific period during the length of a loan or investment, such as the 3rd year.

The syntax for the IPMT function is:

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




The interest rate per period


The period for which you want to find the interest amount. It must be entered in the range 1 to nper


The number of payment periods during the lifetime of the loan. If payments are monthly for 5 years, this would be entered 5*12 to calculate 60 payment periods


The present value, or current amount of the loan or investment.


The future value, or amount after the last payment is made.

FV is optional and if omitted the value is assumed to be 0 i.e. the future value of a loan will be 0


When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 0

  • 0 - Payments are made at the end of the period
  • 1 - Payments are made at the beginning of the period

The examples below show the IPMT function being used to return the interest paid on a loan using different parameters.

Loan details used in the IPMT function





Interest paid in the 3rd period of a 5 year loan



Interest paid in the last year of a 5 year loan, with a future value of 0 and payments made at the beginning of each year

Follow us on

Facebook  Twitter  You Tube 

Other Financial Functions

Excel 2010 Superhero Course