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])
Required. The interest rate.
Required. The period.
Required. The total number of periods in an annuity.
A monthly payment - 12
Quarterly payment - 4
Required. Present value.
Optional. Future value, default value is 0 (zero).
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.
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Functions in 'Financial'
The IPMT function function is one of many functions in the 'Financial' category.