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)

Want to practice ISPMT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=ISPMT(rate, per, nper, pv) Explain

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
perYesThe period, counting from 0 for the first.
nperYesThe total number of payment periods.
pvYesThe 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.

B2fx=ISPMT(8%/12,A2,24,120000)
AB
1Period (from 0)Interest
20-$800.00
31-$766.67
423-$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

Kyle Stott

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.