The IPMT (Interest Payment) function in Excel is used to calculate the interest portion of a payment for a given period of a loan or an investment. It is particularly useful for understanding how much of your periodic loan repayment is going towards interest as opposed to principal.
Here’s how to use the IPMT function:
Syntax
IPMT(rate, per, nper, pv, [fv], [type])
Arguments
- rate (required): The interest rate for the period. If you have an annual interest rate, divide it by the number of payment periods per year to get the periodic rate.
- per (required): The specific period for which you want to find the interest, and it must be between 1 and nper.
- nper (required): The total number of payment periods in the lifespan of the loan or investment.
- pv (required): The present value, or total amount of the loan or investment.
- fv (optional): The future value, or the desired cash balance after the last payment. If omitted, it is assumed to be 0.
- type (optional): The timing of the payment:
- 0 (or omitted) if payments are due at the end of the period.
- 1 if payments are due at the beginning of the period.
Example
Suppose you have a loan of $10,000 with an annual interest rate of 5%, and you want to find out how much of the first monthly payment goes to interest. The loan is to be paid over 3 years (36 months).
- Rate: Since the interest is annual, and you are calculating monthly, use `5%/12`.
- Per: For the first payment, use `1`.
- Nper: The total number of payments is `36`.
- Pv: The loan amount is `10000`.
- Fv: Typically `0` for a loan, or omit.
- Type: `0` is common for loans (end of period).
Formula
=IPMT(5%/12, 1, 36, 10000)
This formula will return the interest portion of the first month’s payment.
Notes
- Ensure the consistency of the payment periods and interest rates (e.g., if payments are monthly, use a monthly interest rate).
- The result from `IPMT` for the initial periods will typically be higher in interest than principal, while in later periods, more of the payment will go toward principal reduction.
Using the IPMT function is a helpful way to understand the cost of borrowing or the income from lending/investing.
Unlock Your Potential
Excel
Access
Power BI
