ISPMT Function (LibreOffice Calc)

=ISPMT(Rate; Period; TotalPeriods; Invest)

All four arguments are required. There are no optional FV or Type arguments in LibreOffice’s documented ISPMT syntax.

Calc formulas here use semicolon argument separators, with decimal point convention set for English. Locale and configurable function separator settings can differ. LibreOffice 26.8 Help, ISPMT documents the four arguments. OpenDocument 1.4, §6.12.25 ISPMT specifies the same standard identifier and no constraints. Both checked 2026-10-13.

Why ISPMT uses a different period numbering model

The official LibreOffice example uses Period=18 for interest 1.5 years into a two-year loan. Our native Calc 25.2.3.2 tests show the function’s arithmetic is based on the original balance at Period=0. Thus, when using its linear model, period 0 corresponds to interest computed on the full starting amount, while period 24 has an outstanding principal of zero.

This numbering is not the same as the typical IPMT 1..NPer payment numbers, and it can produce off-by-one mistakes. LibreOffice 26.8 Help gives the example but does not explicitly lay out the period-zero explanation. The period-zero interpretation here is supported by the native tests and independently calculated model; it should not be asserted as a universal guarantee for untested versions or applications.

The official LibreOffice example asks for the monthly interest level after 1.5 years on a 120,000 loan for two years at 12% per year. There are 24 monthly intervals; the monthly rate is 1%.

=ISPMT(1%;18;24;120000)

Native Calc result: −300.00. This matches the official LibreOffice 26.8 Help example exactly.

Work through the independent equal-principal arithmetic:

Starting principal                 = 120,000
Equal principal repayment         = 120,000 / 24 = 5,000 per month
Balance after 18 principal steps  = 120,000 − 18 × 5,000 = 30,000
Monthly interest charge magnitude = 30,000 × 1% = 300
ISPMT cash outflow                = −300

Official source: LibreOffice Financial Functions Part One, ISPMT example, checked 2026-10-13. The sign reflects the loan borrower’s interest paid; it does not indicate negative economic interest.

In a fresh Calc sheet, enter these cells:

Expected amounts to two decimals: B8=−300.00, B9=300.00, B10=5000.00, B11=30000.00, B12=−300.00, B13=0.00 (within floating-point tolerance). Only the direct ISPMT formula has a matching recorded native case; the surrounding input layout is reproducible tutorial guidance, not a falsely claimed separately executed full-sheet test.

Try changing B6:

This table is particularly useful for spotting off-by-one errors. A period-0 result is supported by native Calc evidence; it must not be inferred from IPMT’s period numbering.

For a loan principal of 10,000 over 36 equal-principal intervals at an annual 6% rate, inspect the balance after five full principal steps:

=ISPMT(6%/12;5;36;10000)

Native result: about −43.0555555555556, displayed to cents as −43.06.

Independent sanity check:

Principal reduction per step = 10,000/36 ≈ 277.77777778
Remaining balance at index 5 = 10,000×(1−5/36) ≈ 8,611.11111111
Periodic rate                = 0.06/12 = 0.005
Interest outflow             = −8,611.11111111 × 0.005
                             ≈ −43.05555556

Compare this to a standard constant-payment loan: =IPMT(6%/12;5;36;10000) computes a different interest schedule because the principal repayments are not equal. Do not compare different period indexes without accounting for the model differences.

Set input B2=120000, B3=12%, B4=12, and B5=24 as above. Start a schedule on row 16:

Put =A17+1 in A18 and copy A18 through A41 for indexes 0 through 24. Copy formulas B17:E17 through row 41. Formulas in B17:B40 (indexes 0–23) represent the 24 interest intervals of the equal-principal model. Row 41 is a zero-balance boundary check, not an additional contractual payment.

To calculate positive interest paid over 24 intervals using this model:

=-SUM(B17:B40)

For these inputs the independent arithmetic series sums the charges 1200+1150+...+50, giving 15,000. (This is an independently derived expected total; the 25-row table and SUM were not separately native-executed in the test suite, so do not report them as verified native outputs.)

A more compact audit formula

For a valid 0 ≤ Period ≤ TotalPeriods and positive TotalPeriods, the model observed in Calc 25.2.3.2 is:

Interest_outflow = −Invest × Rate × (1 − Period/TotalPeriods)

That is independent arithmetic used for validation, not a quotation of Calc’s source implementation. It explains why the interest decreases by the same increment each period (Invest × Rate / TotalPeriods): the principal decreases uniformly.

For the tutorial’s 120,000 example the decline is 50 per interval, from 1,200 at index zero to 50 at index 23.

Important: In a typical equal-principal loan, the total installment changes every period because principal is constant while interest shrinks. ISPMT returns only the interest; it does not calculate a contractual payment or account for fees, compounding convention, taxes or irregular cash flows.

Native diagnostic evidence (not endorsed semantics): In Calc 25.2.3.2, =ISPMT(1%;-1;24;120000) returned −1250 and =ISPMT(1%;25;24;120000) returned approximately +50. A fractional period and fractional total-period count also yielded numeric values. These are version-scoped observations, not documented promises. For reliable contractual schedules, insist on a positive integer total-period count and an integer period in 0..TotalPeriods−1 for interest-bearing intervals.

For the example sheet above, a defensive wrapper is:

=IF(OR(B5<1;B5<>INT(B5);B6<0;B6>=B5;B6<>INT(B6));"Invalid period setup";ISPMT(B3/B4;B6;B5;B2))

This allows only index 0 through B5−1. To audit the zero-balance endpoint separately, use B6=B5 in a deliberate check without treating it as a contractual interest installment. This wrapper is recommended input validation, not a native-tested assertion in the archived suite.

With zero periodic interest, ISPMT returns zero; native evidence includes =ISPMT(0;2;24;10000) returning 0. Negative rates and negative principal are mathematically possible and changed the returned sign in the native tests, but they require their own contractual interpretation. Do not confuse a spreadsheet numeric result with the validity of a financial arrangement.

Calc stores numeric values with floating-point precision. Rounding the displayed result to two decimal places does not necessarily round intermediate balances. If the agreement specifies per-period cents rounding, create an explicit rounding policy and reconcile principal/interest cash flows accordingly.

• LibreOffice: ISPMT appears in LibreOffice 26.8 official Help; all recorded numerical assertions here were executed in LibreOffice Calc 25.2.3.2 on Linux. No native cross-platform comparison was performed.
• ODF: ISPMT is specified in OpenDocument 1.4 Part 4 §6.12.25. The standard declares four number parameters and Constraints: None; that is not the same as a recommendation to supply out-of-range periods.
• Excel: Microsoft’s own ISPMT documentation establishes Excel support, but the formulas have not been executed in Excel for this review. Read its zero-counting note and verify sign/period conventions carefully after moving a workbook between programs.
• Other programs: No unsupported assumption is made about Google Sheets, Numbers, WPS or any other software.
• Locale: English Calc examples here use semicolons as function argument separators. Other Calc settings may use a different separator or decimal mark; save as ODS when possible and verify recalculation in your intended target program.

1. LibreOffice 26.8 Help: “Financial Functions Part One”, ISPMT — syntax, argument descriptions, official 120,000 example. Checked 2026-10-13: https://help.libreoffice.org/latest/en-US/text/scalc/01/04060103.html
2. OASIS OpenDocument 1.4 Part 4 OpenFormula, §6.12.25 ISPMT — syntax, standardized name, number argument types and no formal constraints. OASIS Standard dated 2025-10-06; checked 2026-10-13: https://docs.oasis-open.org/office/OpenDocument/v1.4/os/part4-formula/OpenDocument-v1.4-os-part4-formula.html
3. LibreOffice Calc formula separator options — configuration-specific separator caveat. Checked 2026-10-13: https://help.libreoffice.org/latest/en-GB/text/shared/optionen/01060900.html
4. Microsoft Support: ISPMT function — Excel compatibility at the documentation level, including its zero-counting note; not native-tested in Excel. Checked 2026-10-13: https://support.microsoft.com/en-us/excel/functions/ispmt-function
5. Native and independent evidence — 14 ISPMT independent numerical assertions passed in Calc 25.2.3.2, with six additional ISPMT diagnostic observations kept separate. The saved workbook and output records corroborate these historical observations.