ISPMT function

The ISPMT function in Excel calculates the interest payment for a specified period of an investment or loan based on a constant interest rate and periodic payments. It is commonly used to calculate the interest portion of a payment on a loan where payments are made periodically (e.g., monthly or yearly).

Syntax

ISPMT(rate, per, nper, pv)

Parameters

• rate: The interest rate for each period. For example, if you have an annual interest rate of 6% and make monthly payments, the rate would be 6%/12 = 0.5% (monthly rate).
• per: The period for which you want to calculate the interest payment. For example, to find the interest payment for the 3rd period, use per = 3.
• nper: The total number of periods for the loan or investment. For example, for a 5-year loan with monthly payments, nper would be 5*12 = 60 months.
• pv: The present value or principal amount of the loan (the initial amount borrowed or invested).

How It Works

The ISPMT function calculates the interest portion of a payment for a specific period in an amortized loan, where payments are divided between interest and principal. It uses the formula:

$$\text{Interest Payment} = \text{Principal} \times \text{Interest Rate} \times \left(1 – \frac{\text{Period} – 1}{\text{Total Periods}}\right)$$

This function is especially useful when dealing with loans where the interest portion changes with each payment.

Example

Let’s say you have the following loan details:

• 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.

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

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

Explanation of the Example:

• 6%/12: The monthly interest rate (6% annual divided by 12).
• 3: The period for which you want to calculate the interest (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 ISPMT function returns a negative value because it represents an outflow of money (interest paid).
• It assumes that payments are made at the end of each period.
• ISPMT is similar to the IPMT function, but IPMT allows for both the beginning and end of the period as options for the payment date, whereas ISPMT assumes payments are always at the end.
• The ISPMT function can be used for various types of loans or investments that involve regular, equal payments (such as mortgages or car loans).

Summary

The ISPMT function in Excel calculates the interest portion of a loan payment for a specific period in an amortized loan or investment. It is typically used when you want to know how much of a payment goes toward interest, such as in a monthly mortgage payment schedule or an installment loan.