Using the Excel IPMT Function
SummaryThe 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])
Required. Interest rate per period.
Required. Period for which you want to find the interest. Must be in the range of 1 to nper.
Required. Total number of payment periods.
Required. Present value that a series of future payments is worth today. PV should be entered as a negative number.
Optional. The future value desired after the last payment is made. If omitted, FV is assumed to be zero (0).
Optional. Argument indicating when payments are due. Use 1 for beginning of the period and zero (0) for end of the period.
Usage NotesIPMT 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.