The IPMT function in Excel is used to calculate the interest portion of a payment for a given period in an amortized loan or investment. This function is particularly useful for breaking down the interest and principal components of a payment, which is common in loan schedules or financial planning.
Syntax
| Formula | Description |
|---|---|
| =IPMT(rate, per, nper, pv, [fv], [type]) | Calculates the interest payment for a specific period of a loan or investment. |
- rate: The interest rate for each period.
- per: The specific period for which you want to find the interest. Must be an integer between 1 and nper.
- nper: The total number of payment periods in the loan or investment.
- pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal.
- [fv]: An optional argument that represents the future value, or the cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0.
- [type]: An optional argument that specifies when payments are due. Use 0 if payments are due at the end of the period (default), or 1 if payments are due at the beginning of the period.
Example
Let's consider a scenario where you have taken out a loan of $30,000, to be repaid over 7 years with an annual interest rate of 5%. The loan payments are made monthly. We want to calculate the interest payment for the 12th month.
| Input | Value |
|---|---|
| Loan Amount (pv) | $30,000 |
| Annual Interest Rate | 5% |
| Number of Years (nper) | 7 |
| Payments per Year | 12 |
| Period (per) | 12 |
To calculate the interest payment for the 12th month, use the following formula:
=IPMT(5%/12, 12, 7*12, -30000)This formula divides the annual interest rate by 12 to get the monthly rate, uses the 12th period, and multiplies the number of years by 12 to get the total number of payments.
Practice Exercise 1
Task: Calculate the interest portion of the payment for the 24th month of a $100,000 loan to be repaid over 15 years with an annual interest rate of 4%. The payments are made at the end of each month.
Hint: Remember to adjust the annual interest rate to a monthly rate by dividing by 12, and to multiply the number of years by 12 to calculate the total number of payments.
Practice Exercise 2
Task: Calculate the interest portion of the payment for the 36th quarter of a $250,000 loan to be repaid over 10 years with an annual interest rate of 6%. The payments are made at the beginning of each quarter.
Hint: Since the payments are made at the beginning of each quarter, use 1 for the type argument in the IPMT function. Also, adjust the annual interest rate to a quarterly rate by dividing by 4.
Solution for Practice Exercise 1:
The formula to calculate the interest payment for the 24th month in Practice Exercise 1 is:
=IPMT(4%/12, 24, 15*12, -100000)Solution for Practice Exercise 2:
The formula to calculate the interest payment for the 36th quarter in Practice Exercise 2 is:
=IPMT(6%/4, 36, 10*4, -250000, 0, 1)