The ISPMT function in Excel is used to calculate the interest payment for a specific period of an investment or loan that is being paid down with regular installments. It is particularly useful for understanding how much of a payment is going towards interest in a given period, as opposed to the principal.
Here’s the syntax for the ISPMT function:
ISPMT(rate, per, nper, pv)
- rate: The interest rate for the investment or loan per period.
- per: The specific period for which you want to find the interest, expressed as an integer (must be between 1 and nper inclusive).
- nper: Total number of payment periods in the investment or loan.
- pv: The present value, or the total amount that a series of future payments is worth now. For a loan, this would be the principal amount.
Example:
Suppose you have a loan of $10,000 with an annual interest rate of 6%, to be paid over 10 years (120 months). You want to calculate the interest portion of the payment in the 6th month.
- rate: Since the loan is paid monthly, convert the annual rate to a monthly rate by dividing by 12. So, the rate is 6%/12 = 0.5% = 0.005.
- per: You are interested in the 6th month, so `per` is 6.
- nper: This is the total number of payment periods, which is 10 years × 12 months = 120 months.
- pv: The present value of the loan is $10,000.
Plug these values into the ISPMT function in Excel:
=ISPMT(0.005, 6, 120, 10000)
This formula will return the interest payment for the 6th month of the loan. Remember, the ISPMT function will give a negative number because it represents an outgoing payment. If you want the result as a positive number, you can add a negative sign before the ISPMT function:
=-ISPMT(0.005, 6, 120, 10000)
Notes:
- Ensure your rate matches the period. If your periods are monthly, adjust the rate to a monthly rate.
- The ISPMT function is most useful for financial analysis and planning, giving insight into how much interest is paid at any point in a repayment schedule.
Unlock Your Potential
Excel
Access
Power BI
