Author: Oscar Cronquist Article last updated on September 11, 2018

The IPMT function calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate. The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.

The image above shows the IPMT function in cell E3 calculating the interest for the second month for a 10-year loan of 100 000.

Formula in cell E3:


Excel Function Syntax

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


rate Required.  The interest rate.
per Required. The period.
nper Required.  The total number of periods in an annuity.

A monthly payment - 12
Quarterly payment - 4

pv Required.  Present value.
[fv] Optional.  Future value, default value is 0 (zero).
[type] Optional.  When payments are due.

0 - End of period, default value.
1 - At the beginning of the period.


Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.