Summary

The Excel IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in between.

Purpose 

Get interest in given period

Return value 

The interest amount

Arguments 

  • rate - The interest rate per period.
  • per - The payment period of interest.
  • nper - The total number of payment periods.
  • pv - The present value, or 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.

Syntax 

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

How to use 

The IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in between.

Notes

  • Be consistent with inputs for rate. For example, for a 5-year loan with 4.5% annual interest, enter the rate as 4.5%/12.
  • By convention, the loan value (pv) is entered as a negative value.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.