ISPMT Function
Calculates the interest paid during a specific period of a loan with equal principal payments.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| rate | Interest rate per period. | Required |
| per | The period for which to calculate interest. | Required |
| nper | Total number of payment periods. | Required |
| pv | Present value or loan principal. | Required |
Basic Example
Interest for a specific period of an interest-only or equal-principal loan
=ISPMT(0.05/12, 1, 12, 12000)For a $12,000 loan at 5% annual interest, the first month's interest is $50 if principal is repaid equally.
Advanced Examples
Example 1: Later period interest
Amortization scheduleInterest decreases as principal is repaid
=ISPMT(0.05/12, 6, 12, 12000)How ISPMT Works
ISPMT calculates interest based on the outstanding principal balance at the start of the period. Unlike IPMT, it assumes equal principal payments rather than equal total payments.
Important Notes & Limitations
ISPMT is different from IPMT. IPMT is for loans with equal payments.
Returns negative values by default.
Requires consistent period units.
Common Errors & Fixes
Wrong resultConfused ISPMT with IPMT.Fix: Use IPMT for standard amortized loans with equal payments.
#NUM! errorPer is outside the range 1 to nper.Fix: Ensure per is valid.
Download Practice File
ISPMT
ispmtPractice ISPMT with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the ISPMT function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps