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.

Related functions

Excel FV function

FV Function

The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.
Excel PV function

PV Function

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.
Excel RATE function

RATE Function

The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.
Excel NPER function

NPER Function

The Excel NPER function is a financial function that returns the number of periods for a loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount.
Excel PMT function

PMT Function

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
Excel PPMT function

PPMT Function

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

IPMT Function

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.
Excel CUMPRINC function

CUMPRINC Function

The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment periods.
Excel CUMIPMT function

CUMIPMT Function

The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period. You can use CUMIPMT to calculate and verify the total interest paid on a loan, or the interest paid between any two payment periods.
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.

Functions

Links