IPMT function

The IPMT function in Excel is used to calculate the interest payment for a specific period of a loan or investment, based on a constant interest rate and periodic payments. It is typically used for amortizing loans, where payments are made over multiple periods (e.g., monthly mortgage payments), and the goal is to determine how much of a payment goes toward the interest.

Syntax

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

Parameters

• rate: The interest rate for each period. For example, if the annual interest rate is 6%, and payments are monthly, the rate would be 6%/12 (0.5% per month).
• per: The period for which you want to calculate the interest payment. For instance, if you are calculating the interest payment for the 5th month of a loan, the per would be 5.
• nper: The total number of periods (payments) for the loan or investment. For example, for a 30-year mortgage with monthly payments, nper would be 30*12 = 360.
• pv: The present value or the principal amount of the loan (the amount borrowed or invested).
• [fv] (optional): The future value, or the cash balance you want to attain after the last payment is made. If omitted, Excel assumes it is 0.
• [type] (optional): The type of payment. Use 0 if payments are due at the end of each period (the default), or 1 if payments are due at the beginning of each period.

How It Works

The IPMT function calculates the portion of the payment that goes toward the interest for a given period in an amortized loan. The result represents the interest payment, which changes over time as the outstanding principal decreases with each payment.

The formula for IPMT is:

$$\text{Interest Payment} = (\text{Principal} \times \text{Interest Rate}) – (\text{Remaining Principal} \times \text{Interest Rate})$$

Example

Let’s say you have the following loan terms:

• Loan Amount (pv): $10,000
• Annual Interest Rate: 6% (monthly interest rate = 6%/12 = 0.5%)
• Number of Periods (nper): 12 months (1 year)
• Period (per): You want to calculate the interest payment for the 3rd month of the loan.

To calculate the interest payment for the 3rd month, use the IPMT function:

=IPMT(6%/12, 3, 12, 10000)

Explanation of the Example:

• 6%/12: The monthly interest rate (6% annual divided by 12).
• 3: The period (you want to find the interest payment for the 3rd month).
• 12: The total number of periods (12 months).
• 10000: The loan amount (present value).

This formula will return the interest portion of the payment for the 3rd month of a $10,000 loan with a 6% annual interest rate and 12-month term.

Important Notes

• The IPMT function returns a negative value because it represents an outflow of money (interest paid).
• The interest payments decrease over time as the outstanding balance on the loan decreases.
• You can use IPMT to calculate interest payments for loans with monthly, quarterly, or annual payment schedules, as long as the interest rate and periods match (i.e., monthly interest rates for monthly payments).
• The type argument is useful for loans where payments are made at the beginning of the period (such as some leases or annuities). If you leave it blank or use 0, payments are assumed to be at the end of the period.

Summary

The IPMT function in Excel helps calculate the interest portion of a loan payment for a specific period in an amortizing loan. It is widely used in financial analysis, especially for loans, mortgages, and investments where regular payments are made, and the interest changes over time as the principal decreases.