Excel functions / Financial
ISPMT function
The interest paid in a period of a loan that is repaid in equal amounts of principal, so the interest falls every period.
Syntax
=ISPMT(rate, per, nper, pv)
Arguments
rate: The interest rate for one period.per: The period, counting from 0 for the first.nper: How many periods the loan runs for.pv: The amount borrowed.
When to use it
ISPMT gives the interest paid in one period of a loan that is repaid in equal amounts of principal each period, so that the payments shrink. PMT and IPMT are for the more common loan with equal payments. Periods count from 0.
Examples
Interest in the second month of a three-year loan
A B C D E 1 Rate Years Loan 2 10% 3 8000 -64814.81 In E2:
=ISPMT(A2/12, 1, B2*12, C2*1000)→ -64814.81
Common mistakes
- Counting from 1. ISPMT counts periods from 0. IPMT counts from 1.
ISPMT in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: ISPMT
- Danish: ISPMT
- Dutch: ISBET
- Finnish: ISPMT
- French: ISPMT
- German: ISPMT
- Hungarian: LRÉSZLETKAMAT
- Italian: INTERESSE.RATA
- Norwegian: ER.AVDRAG
- Polish: ISPMT
- Portuguese (Brazil): ÉPGTO
- Portuguese (Portugal): É.PGTO
- Russian: ПРОЦПЛАТ
- Spanish: INT.PAGO.DIR
- Swedish: RALÅN
- Turkish: ISPMT