IPMT

Using the Excel IPMT Function

Summary

The Excel IPMT function will return the interest payment for a given period for an investment. The interest payment can be based on periodic, constant payments and a constant interest rate.

To find the total payment for a period, both interest and principal, use the PMT function. Alternatively, you can find the principal only portion of a payment using the PPMT function.


Syntax

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

Syntax Breakdown

Rate
Required. Interest rate per period.

Per
Required. Period for which you want to find the interest. Must be in the range of 1 to nper.

Nper
Required. Total number of payment periods.

PV
Required. Present value that a series of future payments is worth today. PV should be entered as a negative number.

FV
Optional. The future value desired after the last payment is made. If omitted, FV is assumed to be zero (0).

Type
Optional. Argument indicating when payments are due. Use 1 for beginning of the period and zero (0) for end of the period.


Usage Notes

IPMT is used to calculate the interest payments for an investment in a given payment period.

Rate and Nper Units
It is critical that you are consistent with the units used when entering values for rate and nper. Some conversion (i.e. from annual to monthly) may be required.

For example, if you are making monthly payments on a 10-year loan at 3.5% annual interest, use 3.5%/12 for rate (to get the monthly rate) and 10*12 for nper (to get the total number of monthly payments).

Failing to ensure the units are the same will produce incorrect results.