ISPMT Function
Returns the interest for a period of a loan repaid in equal principal amounts.
Category
Financial
Available in
All versions, Microsoft 365
Returns
Interest for one period (negative)
Overview
ISPMT is for a different kind of loan: one where the same amount of principal is repaid each period, so the total payment falls over time. It returns the interest for one period.
Watch out — ISPMT counts periods from 0, unlike IPMT, which starts at 1. For normal fixed-payment loans use IPMT.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| rate | Yes | The interest rate per period (annual rate ÷ 12 for monthly payments). |
| per | Yes | The period, counting from 0 for the first. |
| nper | Yes | The total number of payment periods. |
| pv | Yes | The present value — the loan amount, or what a series of payments is worth today. |
Examples
Example 1 — Equal-principal equipment loan
Northstar borrows $120,000 at 8%, repaying $5,000 of principal every month for 24 months.
| A | B | |
|---|---|---|
| 1 | Period (from 0) | Interest |
| 2 | 0 | -$800.00 |
| 3 | 1 | -$766.67 |
| 4 | 23 | -$33.33 |
Period 0 charges interest on the full $120,000. Each month the balance drops by $5,000, so the interest drops by $33.33.
Common mistakes
- Off by one period. ISPMT's first period is 0. Using 1 gives the second month's interest.
- Results don't match the bank's schedule. The loan probably has fixed payments — use IPMT instead.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.