Using the Excel ISPMT Function


The Excel ISPMT function calculates the interest paid, or received, for the specified period of a loan, or investment, with even principal payments.


=ISPMT (rate, per, nper, pv)

Syntax Breakdown

Required. Interest rate for the loan or investment.

Required. Period for which you want to find the interest rate. Must be between 1 and nper.

Period uses a zero-based index. For example, use 0 (zero) for period 1, 1 for period 2, etc.

Required. Total number of payment periods for the loan or investment.

Required. Present value for an investment. The loan amount for a loan.

Usage Notes

ISPMT returns the interest payment for loans that use a repayment schedule with even principal payments. To find the interest payment for loans with even periodic payments, use the IPMT function.

Rate and Nper Units
It is critical that you are consistent with the units used when entering values for rate and nper. Some conversion (i.e. from annual to monthly) may be required.

For example, if you are making monthly payments on a 10-year loan at 3.5% annual interest, use 3.5%/12 for rate (to get the monthly rate) and 10*12 for nper (to get the total number of monthly payments).

Failing to ensure the units are the same will produce incorrect results.