ISPMT
Quirk foundCategory: Financial · Last tested 2026-09-01
Real compatibility results for the ISPMT function: executed in Excel for the web, Google Sheets and LibreOffice Calc, with desktop Excel behavior from Microsoft’s official documentation (we do not run desktop Excel — Excel for the web is a different application and is executed separately). Syntax and links to that documentation are below.
Support matrix
| Engine | Documented | Live-tested | Verdict |
|---|---|---|---|
| Excel (desktop) | Yes | No — documented only | n/a |
| Excel for the web | — | Yes (recalc, 2026-09-01) | Supported, behaves as documented |
| Google Sheets | Yes | Yes (Drive import, 2026-08-31) | Supported, behaves as documented |
| LibreOffice Calc | Yes | Yes (25.8.7.3, 2026-08-31) | Quirk found |
LibreOffice version history
We executed the same test cases under each LibreOffice release to show exactly when ISPMT’s support changed — not documentation claims, real results.
| LibreOffice version | Verdict | Tested |
|---|---|---|
| 24.2.0.3 | Quirk found | 2026-08-31 |
| 24.8.7.2 | Quirk found | 2026-08-31 |
| 25.2.0.3 | Quirk found | 2026-08-31 |
| 25.8.7.3 | Quirk found | 2026-08-31 |
Why isn't ISPMT working in LibreOffice?
ISPMT exists in LibreOffice 25.8.7.3, but it is not a drop-in match for
Excel — our executed tests found real behavioral differences (detailed in the test results on this
page). If a formula that works in Excel or Google Sheets misbehaves in LibreOffice, compare your usage
against the failing cases above before assuming your data is wrong.
Discovered quirks
-
=ISPMT(0.1,1,0,8000000) on
LibreOffice Calc returned
#NUM!, but the documented/expected
result is #DIV/0!.
Provenance
DERIVED, not quoted: Microsoft's ISPMT page documents no error conditions at all. The formula confirmed by both vendors' worked examples divides per by nper, so nper = 0 is a division by zero and #DIV/0! is the consistent result; MISMATCH vs expected: expected '#DIV/0!', got '#NUM!'
Executed test cases
Excel for the web (executed 2026-09-01 via OneDrive recalculation)
These values come from Excel for the web, not from desktop Excel. They are two different implementations of the calculation engine, and this run measured only the web one: the corpus was uploaded to OneDrive as .xlsx, recalculated by Excel for the web on open, and downloaded again for readback. Excel for the web is a rolling service with no pinnable version, so the run is identified by its date. Where a value here disagrees with the Expected column — which is Microsoft’s documentation of the desktop product — we cannot tell you whether the web engine diverges from the desktop one or the documentation is wrong about both, because we do not run desktop Excel.
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =ROUND(ISPMT(0.1/12,1,3*12,8000000),4) | Microsoft's documented example: interest paid in the first month of an 8,000,000 loan at 10% over three years | -64814.8148 | -64814.8148ProvenanceMicrosoft's ISPMT page publishes no formula and no text example (its worked example is an image), and it contradicts itself on indexing: the argument table says per 'must be between 1 and Nper' while Remarks says 'ISPMT counts each period beginning with zero, not with one'. The even-principal formula ISPMT = pv * rate * (per/nper - 1) is confirmed by LibreOffice's own documented example (see the ISPMT_libreoffice_documented_example case) and reproduces Microsoft's own published figure of -64,814.8148 here: 8000000 * (0.1/12) * (1/36 - 1) = -64814.81481. Negative because it is cash paid out |
Matched |
| =ROUND(ISPMT(0.1,1,3,8000000),2) | Documented example: interest paid in the first year of the same loan on an annual schedule | -533333.33 | -533333.33ProvenanceSame formula on an annual schedule: 8000000 * 0.1 * (1/3 - 1) = -533333.3333, matching the -533,333.33 figure published for this example |
Matched |
| =ROUND(ISPMT(0.1,3,3,8000000),7) | DIVERGENCE PROBE: at per = nper the remaining balance is zero, so the interest is exactly zero | 0 | 0ProvenanceThe documented formula pv*rate*(per/nper - 1) gives 8000000*0.1*(3/3 - 1) = 0. This is the clearest test of ISPMT's period indexing: an engine that treats per as 0-based, or that uses (1 - per/nper) with the opposite sign convention, will not return 0 here |
Matched |
| =ROUND(ISPMT(0.1,0,3,8000000),4) | At per = 0 the whole principal is still outstanding, so the interest is the full period charge | -800000 | -800000Provenance8000000 * 0.1 * (0/3 - 1) = -800000, i.e. one period's interest on the undiminished balance |
Matched |
| =ROUND(ISPMT(0.01,18,24,120000),7) | Cross-vendor check: LibreOffice's help page publishes a worked example for the same function | -300 | -300ProvenanceLibreOffice's Calc help documents ISPMT(Rate; Period; TotalPeriods; Invest) with the worked example =ISPMT(1%;18;24;120000) = -300. That is exactly pv * rate * (per/nper - 1) = 120000 * 0.01 * (18/24 - 1) = -300, so both vendors' documentation agrees on the formula and on the sign convention |
Matched |
| =ISPMT(0.1,1,0,8000000) | A zero number of periods divides by zero inside the per/nper term | #DIV/0! | #DIV/0!ProvenanceDERIVED, not quoted: Microsoft's ISPMT page documents no error conditions at all. The formula confirmed by both vendors' worked examples divides per by nper, so nper = 0 is a division by zero and #DIV/0! is the consistent result |
Matched |
Google Sheets (executed 2026-08-31 via Drive import)
Google Sheets is a rolling service with no pinnable version, so this run is identified by its date. The corpus was imported to Drive as .xlsx, recalculated by Sheets, and exported back for readback.
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =ROUND(ISPMT(0.1/12,1,3*12,8000000),4) | Microsoft's documented example: interest paid in the first month of an 8,000,000 loan at 10% over three years | -64814.8148 | -64814.8148ProvenanceMicrosoft's ISPMT page publishes no formula and no text example (its worked example is an image), and it contradicts itself on indexing: the argument table says per 'must be between 1 and Nper' while Remarks says 'ISPMT counts each period beginning with zero, not with one'. The even-principal formula ISPMT = pv * rate * (per/nper - 1) is confirmed by LibreOffice's own documented example (see the ISPMT_libreoffice_documented_example case) and reproduces Microsoft's own published figure of -64,814.8148 here: 8000000 * (0.1/12) * (1/36 - 1) = -64814.81481. Negative because it is cash paid out |
Matched |
| =ROUND(ISPMT(0.1,1,3,8000000),2) | Documented example: interest paid in the first year of the same loan on an annual schedule | -533333.33 | -533333.33ProvenanceSame formula on an annual schedule: 8000000 * 0.1 * (1/3 - 1) = -533333.3333, matching the -533,333.33 figure published for this example |
Matched |
| =ROUND(ISPMT(0.1,3,3,8000000),7) | DIVERGENCE PROBE: at per = nper the remaining balance is zero, so the interest is exactly zero | 0 | 0ProvenanceThe documented formula pv*rate*(per/nper - 1) gives 8000000*0.1*(3/3 - 1) = 0. This is the clearest test of ISPMT's period indexing: an engine that treats per as 0-based, or that uses (1 - per/nper) with the opposite sign convention, will not return 0 here |
Matched |
| =ROUND(ISPMT(0.1,0,3,8000000),4) | At per = 0 the whole principal is still outstanding, so the interest is the full period charge | -800000 | -800000Provenance8000000 * 0.1 * (0/3 - 1) = -800000, i.e. one period's interest on the undiminished balance |
Matched |
| =ROUND(ISPMT(0.01,18,24,120000),7) | Cross-vendor check: LibreOffice's help page publishes a worked example for the same function | -300 | -300ProvenanceLibreOffice's Calc help documents ISPMT(Rate; Period; TotalPeriods; Invest) with the worked example =ISPMT(1%;18;24;120000) = -300. That is exactly pv * rate * (per/nper - 1) = 120000 * 0.01 * (18/24 - 1) = -300, so both vendors' documentation agrees on the formula and on the sign convention |
Matched |
| =ISPMT(0.1,1,0,8000000) | A zero number of periods divides by zero inside the per/nper term | #DIV/0! | #DIV/0!ProvenanceDERIVED, not quoted: Microsoft's ISPMT page documents no error conditions at all. The formula confirmed by both vendors' worked examples divides per by nper, so nper = 0 is a division by zero and #DIV/0! is the consistent result |
Matched |
LibreOffice Calc 25.8.7.3 (tested 2026-08-31)
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =ROUND(ISPMT(0.1/12,1,3*12,8000000),4) | Microsoft's documented example: interest paid in the first month of an 8,000,000 loan at 10% over three years | -64814.8148 | -64814.8148ProvenanceMicrosoft's ISPMT page publishes no formula and no text example (its worked example is an image), and it contradicts itself on indexing: the argument table says per 'must be between 1 and Nper' while Remarks says 'ISPMT counts each period beginning with zero, not with one'. The even-principal formula ISPMT = pv * rate * (per/nper - 1) is confirmed by LibreOffice's own documented example (see the ISPMT_libreoffice_documented_example case) and reproduces Microsoft's own published figure of -64,814.8148 here: 8000000 * (0.1/12) * (1/36 - 1) = -64814.81481. Negative because it is cash paid out |
Matched |
| =ROUND(ISPMT(0.1,1,3,8000000),2) | Documented example: interest paid in the first year of the same loan on an annual schedule | -533333.33 | -533333.33ProvenanceSame formula on an annual schedule: 8000000 * 0.1 * (1/3 - 1) = -533333.3333, matching the -533,333.33 figure published for this example |
Matched |
| =ROUND(ISPMT(0.1,3,3,8000000),7) | DIVERGENCE PROBE: at per = nper the remaining balance is zero, so the interest is exactly zero | 0 | 0ProvenanceThe documented formula pv*rate*(per/nper - 1) gives 8000000*0.1*(3/3 - 1) = 0. This is the clearest test of ISPMT's period indexing: an engine that treats per as 0-based, or that uses (1 - per/nper) with the opposite sign convention, will not return 0 here |
Matched |
| =ROUND(ISPMT(0.1,0,3,8000000),4) | At per = 0 the whole principal is still outstanding, so the interest is the full period charge | -800000 | -800000Provenance8000000 * 0.1 * (0/3 - 1) = -800000, i.e. one period's interest on the undiminished balance |
Matched |
| =ROUND(ISPMT(0.01,18,24,120000),7) | Cross-vendor check: LibreOffice's help page publishes a worked example for the same function | -300 | -300ProvenanceLibreOffice's Calc help documents ISPMT(Rate; Period; TotalPeriods; Invest) with the worked example =ISPMT(1%;18;24;120000) = -300. That is exactly pv * rate * (per/nper - 1) = 120000 * 0.01 * (18/24 - 1) = -300, so both vendors' documentation agrees on the formula and on the sign convention |
Matched |
| =ISPMT(0.1,1,0,8000000) | A zero number of periods divides by zero inside the per/nper term | #NUM! | #DIV/0!ProvenanceDERIVED, not quoted: Microsoft's ISPMT page documents no error conditions at all. The formula confirmed by both vendors' worked examples divides per by nper, so nper = 0 is a division by zero and #DIV/0! is the consistent result |
Mismatch |
Docs & syntax
- Excel (desktop): official documentation
- Google Sheets: official documentation
- LibreOffice Calc: official documentation
Where ISPMT behaves differently
- Every error code LibreOffice reports as #VALUE!
Across our 2,334-case executed corpus, 272 cases in 135 functions return #VALUE! in LibreOffice Calc 25.8.7.3 where Microsoft documents #NUM! (244), #N/A (15), #DIV/0! (10) or #REF! (3). Google Sheets returns the documented code on 239 of the 249 it has a function for. Identical in all four LibreOffice builds tested. - When the documentation is wrong: 29 vendor doc defects found by execution
Independent derivation across 586 executed functions found 29 places where a vendor's own page is contradicted by its own inputs, its own table, or the live engine: 23 Microsoft, 5 Google, 1 LibreOffice. Includes T.INV.2T's doubly-wrong Remark, DISC's stale figure, ISDATE's page against the live engine, and RAWSUBTRACT's help against LibreOffice's own result.