Quick Navigation
ISPMT Function
Summary
The Excel ISPMT function calculates the interest portion of a payment for a specific period in loans or investments that use equal principal repayment schedules. Unlike standard amortization, this function handles loans where the principal repayment remains constant each period while interest decreases over time.
Syntax
ISPMT(rate, per, nper, pv)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| rate | Number |
Yes | The interest rate per period. Use annual rate divided by payments per year (12 for monthly, 4 for quarterly) |
| per | Number |
Yes | The payment period to calculate (must be between 0 and nper) |
| nper | Number |
Yes | Total number of payment periods |
| pv | Number |
Yes | Loan amount or present value (positive for money borrowed) |
Using the ISPMT Function
ISPMT excels at analyzing loans with fixed principal payments where each installment reduces the principal by the same amount, but interest payments decrease over time as the outstanding balance shrinks. This creates an amortization schedule where early payments are mostly interest and later payments are mostly principal repayment.
Common ISPMT Examples
Monthly Car Loan Interest (Period 1)
=ISPMT(5%/12, 1, 60, 25000)Calculates first month's interest on $25,000 car loan at 5% APR over 5 years (60 months). Returns approximately -$104.17.
Investment Interest (Middle Period)
=ISPMT(8%/12, 15, 36, -10000)Interest received in month 15 of 3-year $10,000 investment at 8% APR. Negative PV shows investment.
Annual Business Loan
=ISPMT(7%, 2, 5, 50000)Year 2 interest on 5-year $50,000 business loan at 7% annual rate.
Frequently Asked Questions
Common Errors and Solutions
#NUM! - Invalid period
Cause: Per value is less than 0 or greater than nper
Solution: Ensure per is between 0 and nper inclusive
#NUM! - Invalid rate
Cause: Negative interest rate provided
Solution: Use positive rate values
#VALUE! - Wrong data types
Cause: Non-numeric values in arguments
Solution: All inputs must be numbers
Notes
- Match rate period with nper (monthly rate with monthly periods)
- Period numbering starts at 0, not 1
- Positive PV = loan (money received), Negative PV = investment (money invested)
- Early periods have highest interest payments
- Total interest decreases linearly with principal balance
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+