Summary

The Excel ISPMT function returns the interest paid for a specific period when the loan follows an equal principal repayment model. That is different from the more common amortized schedule where total payment stays fixed each period.

With equal principal repayment, principal decreases by the same amount every period. Because outstanding balance keeps dropping, the interest portion also drops in a near-linear pattern. ISPMT is built exactly for that structure.

This function is very useful for legacy corporate loan contracts, internal debt schedules, and comparison analysis where you want to measure how fixed-principal interest behaves versus standard amortized interest.

Syntax

=ISPMT(rate, per, nper, pv)

rate is interest per period, per is the target period, nper is total periods, and pv is principal.

Important behavior: ISPMT uses period counting that starts at 0 in practical use. So for a 60-period schedule, the first period is 0 and the last period is 59. This is why your quick practice uses per=0 and per=59.

Unit consistency is critical. If your schedule is monthly, use monthly rate and total months. Example: annual 5% over 5 years becomes rate=0.05/12 and nper=60.

Arguments

• rate - Interest rate per period (decimal). For monthly loans, divide annual rate by 12.
• per - Period index for the interest you want. In ISPMT workflows, use 0 to nper-1.
• nper - Total number of payment periods in the full schedule.
• pv - Present value (original loan amount).

Validation checks that prevent most mistakes:

• Confirm per is inside valid index range.
• Confirm rate and nper use the same period unit.
• Confirm sign convention before comparing with IPMT or PMT outputs.
• Confirm at least one manual spot-check (first or last period).

ISPMT vs Other Functions

ISPMT is often confused with IPMT, but they represent different repayment structures.

Short rule: if principal is repaid equally each period, use ISPMT. If total payment is fixed each period, use IPMT/PPMT/PMT family.

Using the ISPMT Function

A practical modeling flow:

• Set loan assumptions: annual rate, term, principal.
• Convert assumptions to period basis (for example monthly).
• Calculate ISPMT for selected periods (start, middle, end).
• Compare with IPMT to show structural difference to stakeholders.

In reporting, this comparison is helpful because many people assume all loan schedules are amortized fixed-payment schedules. ISPMT helps you show when that assumption is wrong.

Example 1 - First-Month Interest

Find first-period interest in a 60-month equal-principal schedule.

=ISPMT(0.05/12,0,60,10000)

Example 2 - Final-Month Audit

Find final-period interest using last index (59).

=ISPMT(0.05/12,59,60,10000)

Example 3 - Interest Threshold Check

Use a boolean test to flag whether period-0 interest exceeds your cost rule.

=ISPMT(0.05/12,0,60,10000)<-40

Example 4 - Relative Cost Comparison

Compare ISPMT and IPMT outputs for similar first-period context.

=ISPMT(0.05/12,0,60,10000)-IPMT(0.05/12,1,60,10000)

Conclusion Recap

• Summary: ISPMT is for interest in equal-principal repayment structures.
• Syntax: =ISPMT(rate, per, nper, pv) with period-level unit consistency.
• Index rule: In practice, use period index 0 to nper-1 for ISPMT workflows.
• Comparison: Use IPMT for amortized fixed-payment loans, not as a direct replacement.
• Best use: Legacy debt audits and structural comparison analysis.