ISPMT Function

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. The rate is converted to a monthly value with 0.05/12, and period 0 means the first month in ISPMT's indexing style.

This example is useful because it shows the kind of loan ISPMT is built for. Instead of a fixed-payment loan, the principal is repaid evenly, so the interest pattern follows a different path.

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

Example 2 - Final-Month Audit

Find final-period interest using last index 59. By that point, most of the principal has already been repaid, so the interest amount should be much smaller than in the first period.

This makes the example useful as a contrast with Example 1. Together, the two results show how equal-principal schedules create a steady drop in interest over time.

=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. The comparison is written against a negative number because the interest result is usually returned as a cash outflow.

So the example is not only about the period's interest amount. It also shows how to turn that amount into a quick TRUE or FALSE control for reviews and alerts.

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

Example 4 - Relative Cost Comparison

Compare ISPMT and IPMT outputs for similar first-period context. Both formulas are measuring interest, but they assume different repayment structures, so the result shows the gap between equal-principal and amortized logic.

This is especially helpful for beginners because the two functions can look similar at first. The example shows that loan structure changes the interest result even when the rate, term, and amount look alike.

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

Conclusion Recap

ISPMT is useful when the loan does not use a normal fixed-payment amortization pattern and instead repays the same amount of principal each period. In this lesson, that showed up in first-period and final-period interest checks, threshold tests, and side-by-side comparison with IPMT.

The biggest thing to keep in mind is that this is a different loan structure from the one most people see in mortgages. That is why ISPMT and IPMT can give different answers even when the rate, term, and balance look similar. They are measuring interest under different repayment rules.

• 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.