Financial & Accounting
Even-Principal Loan Interest Schedule (ISPMT Formula vs Level-Payment IPMT)
Calculate periodic interest and lifetime interest on straight-line (even-principal) commercial loans and municipal bonds where an equal slice of principal PV / Nper is retired each period.
Why corporate credit lines use even-principal amortization instead of PMT/IPMT
Retail mortgages use level total payments (PMT/IPMT), whereas commercial term loans, sovereign export facilities, and serial bonds frequently amortize a fixed principal slice PV / nper every period plus interest on the declining balance.
Because 1 / nper of the principal is retired each period, the opening balance at 0-based period index per in 0 .. nper - 1 (matching Excel’s ISPMT(rate, per, nper, pv) convention, where per = 0 is the 1st payment) is PV * (1 - per / nper). Periodic interest outflow is -PV * r * (1 - per / nper), and total lifetime interest across all nper payments sums the arithmetic series to PV * r * (nper + 1) / 2.
| Payment # (1-Based) | ISPMT Index (per) | Opening Balance | Interest (-PV*r*(1-per/n)) | Total Payment (Principal + Int) |
|---|---|---|---|---|
| Payment 1 | per = 0 | $120,000.00 | -$600.00 | $5,600.00 ($5,000 + $600) |
| Payment 13 | per = 12 | $60,000.00 | -$300.00 | $5,300.00 ($5,000 + $300) |
| Payment 24 | per = 23 | $5,000.00 | -$25.00 | $5,025.00 ($5,000 + $25) |
| All 24 Months | Sum (0..23) | — | -$7,500.00 | $127,500.00 Total Paid |
Periodic Even-Principal Interest (Excel 0-Based ISPMT Convention)
Computes the negative cash-outflow interest for 0-based period B5 (0 <= B5 < B4) on loan B2 at periodic rate B3 / 12.
=IF(OR(B2 <= 0, B4 <= 0, B5 < 0, B5 >= B4), "Invalid Period", ROUND(-B2 * (B3 / 12) * (1 - B5 / B4), 2))Closed-Form Total Lifetime Interest on an Even-Principal Loan
Sums the linear interest decline over all B4 periods via PV * r * (nper + 1) / 2.
=ROUND(B2 * (B3 / 12) * (B4 + 1) / 2, 2)Try the example
Use the original values or change them to test your understanding.