IPMT Function

Originally published at: IPMT Function in Excel - Free Example File - Excelbuddy.com

The IPMT function in Excel is used to calculate the interest portion of a loan given a specified payment period.


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


  • rate – Interest rate per period.
  • per – Payment period of interest.
  • nper – Total number of payment periods.
  • pv – Present value/total value of all payments now.
  • fv – [optional] The cash balance desired after last payment is made. Defaults to 0.
  • type – [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

IPMT Function Example:

Using the above formula, we are going to calculate the interest by period on a 10,000 loan.

This can be achieved by using the below formula. You can also download the spreadsheet above to follow along.