The IPMT function calculates the interest portion of a loan payment for a specific period. It is commonly used in financial planning and analysis to determine the breakdown of loan payments between principal and interest.


=IPMT(Rate, Period, Num_periods, PV, [FV], [Type])

Rate The interest rate per period for the loan.
Period The period for which you want to calculate the interest payment.
Num_periods The total number of payment periods for the loan.
PV The present value or principal amount of the loan.
FV (Optional) The future value or cash balance of the loan after the last payment. Defaults to 0 if omitted.
Type (Optional) The number 0 or 1 indicating when payments are due. 0 for end of period 1 for the beginning of the period. Defaults to 0 if omitted.

About IPMT

When navigating the labyrinth of loans and seeking clarity on the division of payments into interest and principal components, look no further than the IPMT function in Excel. This function acts as a trusty companion for dissecting loan payments, aiding in financial analyses and budgeting endeavors with its detailed breakdown of the interest portion per period. Utilize IPMT's capabilities to gain insights into the financing landscape, enabling informed decision-making based on a comprehensive understanding of debt repayment dynamics.


Suppose you have taken out a $50,000 loan with an annual interest rate of 6%, payable monthly over 5 years. To calculate the interest portion of the 10th payment, the IPMT formula would be: =IPMT(0.06/12, 10, 60, 50000)

Consider a scenario where you have borrowed $20,000 at an annual interest rate of 4%, with quarterly payments, and you are in the 7th payment period. To find out the interest component of this payment, use: =IPMT(0.04/4, 7, 20,000, , 1)


How does the IPMT function assist in financial planning?

The IPMT function aids in financial planning by revealing the per-period breakdown of interest payments within a loan, facilitating detailed analysis of payment structure and assisting in budgeting decisions.

Can the IPMT function handle loans with irregular payment periods or varying interest rates?

The IPMT function is designed for loans with regular payment schedules and fixed interest rates per period. For loans with irregular payment periods or changing interest rates, consider alternative approaches tailored to the specific loan structure.

What does the 'Type' argument indicate in the IPMT function?

The 'Type' argument in the IPMT function determines the timing of payments, with 0 representing end-of-period payments and 1 indicating beginning-of-period payments. This parameter influences the calculation of interest portions based on when payments are due.

Can the IPMT function be used to calculate principal repayments?

While the IPMT function specifically calculates interest payments, you can derive principal repayments by subtracting the interest component obtained from the total periodic payment amount.

Related functions