MS Excel: ISPMT function to calculate interest paid

The ISPMT function in Excel calculates the interest paid during a specific period of an investment or loan with even principal payments (not even total payments like with IPMT).

In simple words:ISPMT gives you the interest portion for a given period assuming you pay back the principal evenly across the loan term.

Syntax

ISPMT(rate, per, nper, pv)

Argument	Description
rate	Interest rate per period.
per	The specific period you want to calculate interest for.
nper	Total number of periods.
pv	Present value or principal amount (loan or investment).

How ISPMT Works

The ISPMT function assumes that principal payments are equal for each period, and calculates only the interest portion for a specific period based on the remaining balance.

Important:

• ISPMT is different from IPMT (which assumes equal total payments).

• Here, the interest decreases linearly with time.

Example 1: Basic Loan Interest Calculation

Suppose you borrow $10,000 with an annual interest rate of 6% to be repaid evenly over 5 years (60 months).

Find the interest for the first month.

Table Setup:

Cell	Data
A2	Annual Interest Rate: 6%
A3	Period (Month): 1
A4	Total Periods: 60
A5	Loan Amount: 10000

Formula:

=ISPMT(A2/12, A3-1, A4, A5)

Note: Period counting starts from 0 (so 1st period is 0).

Step-by-Step:

• Monthly interest rate = 6% ÷ 12 = 0.5%

• Period = 0 (first month)

• Formula:

Interest = PV × rate × ( 1 − per / nper )

Thus,

10000 × 0.005 × ( 1 − 0 / 60 ) = 50

Interest for the first month = $50

Example 2: Interest in the 10th Month

Find the interest portion in the 10th month.

Cell	Data
A3	Period: 10

Formula:

=ISPMT(A2/12, A3-1, A4, A5)

Result: Interest ≈ $45.83

It’s lower because the principal has been reduced more after several months.

Full Example Table:

Period	Formula	Result (Interest)
1	=ISPMT(6%/12, 0, 60, 10000)	50.00
2	=ISPMT(6%/12, 1, 60, 10000)	49.58
10	=ISPMT(6%/12, 9, 60, 10000)	45.83
60	=ISPMT(6%/12, 59, 60, 10000)	0.83

You see: interest decreases linearly as time goes on.

Key Differences: ISPMT vs IPMT

Feature	ISPMT	IPMT
Principal payment	Equal	Varies
Total payment	Varies	Equal
Interest pattern	Decreases linearly	Decreases exponentially
Use case	Straight-line amortization	Loan/mortgage-style payments

Real-World Applications

• Simple loans with straight-line amortization.

• Project financing.

• Custom financial models.

Conclusion

The ISPMT function in Excel is a valuable tool for calculating straight-line interest payments.If you're dealing with loans or investments where the principal is repaid evenly over time, ISPMT will give you an accurate, easy-to-understand interest calculation. Mastering it makes your financial spreadsheets even smarter and more flexible!