IPMT Function (LibreOffice Calc)

=IPMT(Rate; Period; NPer; PV [; FV [; Type]])

Use semicolons in these examples: that is the default argument separator for the shown English-language Calc configuration. LibreOffice allows the formula separator and decimal separator to be configured; different local settings can require adjustments. Percentages such as 6% are equivalent to decimal 0.06.

Important omission rule: To provide Type, also provide its preceding FV argument (usually zero):

=IPMT(5%/12; 1; 24; 10000; 0; 1)

Avoid leaving an interior empty argument and relying on Calc’s tolerance. The fully explicit form is clearer and portable.

Official syntax and defaults: LibreOffice Financial Functions Part Three, IPMT and OpenDocument 1.4, §6.12.23 IPMT, checked 2026-10-13. LibreOffice Help confirms the argument order and example; OpenFormula explicitly defines the optional argument defaults.

Imagine borrowing 12,000 at a fixed 6% annual rate and repaying the loan monthly over 36 months. The month-specific interest amount declines as the loan balance is paid down.

Create a new Calc sheet and type:

Native Calc 25.2.3.2 results:

The negative figures are outflows from the borrower’s cash-flow perspective. Format the result as currency if useful; Calc does not automatically impose a currency symbol. For an unsigned interest expense, use =ABS(IPMT(B3/12;B5;B4;B2)), returning about 42.80.

Reproducibility: The native verification workbook contains =IPMT(6%/12;12;36;12000) with unrounded result −42.7957040225633. PMT was independently calculated from the annuity equation; PMT−PPMT was also observed in native Calc as a diagnostic, not included as one of the 33 numerical assertions. The slight ~1e−11 difference between computed paths reflects floating-point arithmetic.

Check the first and last installments

Change B5 to 1 to obtain −60.00 in interest; change it to 36 to obtain about −1.82. These are separately native-verified results for the same 12,000 loan:

Interest falls while principal repayment rises, even though the total payment is constant. This is the key difference between equal total payments (IPMT/PPMT) and equal principal amortization (ISPMT).

LibreOffice 26.8 Help asks for the fifth payment-period interest in a seven-period arrangement with a 5% periodic rate and starting value 15,000:

=IPMT(5%;5;7;15000)

Expected display: −352.97 (raw Calc 25.2.3.2 value approximately −352.973422514774). This exactly reproduces the rounded result in official Help. Here 5% means 5% each period; it is not automatically converted to 5% per year or per month. See LibreOffice 26.8 Help: IPMT example, checked 2026-10-13.

Consider a 10,000 loan over 24 months at 5% per year. You must tell Calc the payment timing if installments are due immediately at the start of each period.

Payment 1 for a beginning-of-period annuity occurs immediately, before the balance accrues interest for a full period. Do not assume Type=1 changes the interest rate or converts an annual percentage into a monthly one.

Evidence: The Type=1 first and second period values were native-tested in Calc 25.2.3.2; timing semantics are defined in OpenFormula §6.12.23 (checked 2026-10-13).

Not every annuity must reach a zero balance. To calculate the interest portion in payment 6 of a 36-month arrangement with starting value 5,000, 5% annual interest and a specified future value of positive 1,000 under Calc cash-flow conventions:

=IPMT(5%/12;6;36;5000;1000)

Native Calc result is approximately −17.5808128908267. FV changes the amount of every regular payment and consequently the interest pattern. Be careful: the sign of FV matters and the correct sign depends on whose cash flows you model; a positive target value is not interchangeable with a negative remaining-debt balance. Record the desired cash-flow perspective explicitly before using an actual balloon-loan contract.

For the 12,000 loan above, enter the inputs B2:B4 as specified in Example 1. Starting in row 14, add headers Period, Interest, Principal, Total payment and Outstanding balance to cells A14:E14.

Use row 15 for the first payment and copy the row’s formulas down through row 50 (36 rows total):

Copy B15:E15 down through row 50; change A16 then fill A16:A50. The first balance E15 is starting principal less the positive magnitude of payment 1 principal. By the end, E50 should be approximately zero; display only two decimal places and allow for tiny binary rounding differences. (The schedule itself is a reproducible formula design; the numerical assertions were for individual IPMT calls, not an unrecorded full 36-row native schedule.)

A built-in cross-check for a period is =PMT(rate;nper;pv)-PPMT(rate;period;nper;pv); it should agree with IPMT within rounding tolerance. To total interest for the term use =SUM(B15:B50), keeping sign convention. CUMIPMT can calculate cumulative interest without a manual row-by-row schedule; confirm the start/end-period and payment timing arguments in its documentation.

For an end-of-period payment loan with Rate = r, NPer = n, PV = L, FV = 0 and Type = 0, a constant scheduled payment is

Payment = -L*r / (1 - (1+r)^(-n))   [r ≠ 0]

Before each payment, the interest outflow is approximately

Interest_outflow(k) = -r * balance_at_start_of_period_k

The principal outflow in period k is Payment − Interest_outflow(k); subtract the magnitude of that principal from the previous balance for the next row. If r=0, interest is zero and the payment is −L/n for a standard zero-balance loan.

This recurrence was used for independent Python Decimal arithmetic in the historical verification. It is not a claim that Calc internally uses these exact source operations in that sequence. See ODF 1.4 OpenFormula §6.12.23, checked 2026-10-13.

Version-specific warning: Although ordinary financial modelling should keep 1 ≤ Period ≤ NPer, native Calc 25.2.3.2 did not always reject out-of-range or fractional periods: =IPMT(5%/12;0;24;10000) returned about −43.31416555 and =IPMT(5%/12;25;24;10000) returned approximately zero. A Type of 2 was also accepted in one diagnostic. These are observations, not recommended inputs and not documented validation guarantees. Explicitly validate user-entered parameters, for example:

=IF(OR(B5<1;B5>B4;B5<>INT(B5));"Invalid payment number";IPMT(B3/12;B5;B4;B2))

Use a positive integer NPer, nonzero target PV if appropriate, and Type of only 0 or 1. Validate input rates and feasibility for your contractual scenario; OpenFormula §6.12.23 states no formal function constraints, which does not make strange inputs financially meaningful.

• LibreOffice: Syntax and presence checked against LibreOffice 26.8 Help, with native Calc 25.2.3.2 execution. Other 25.x/26.x builds and platforms were not run.
• ODF/OpenFormula: IPMT is the standard identifier in OpenDocument 1.4 Part 4 §6.12.23; the standard specifies FV=0 and Type=0 defaults. This checks the specification, not every ODF reader’s execution.
• Microsoft Excel: The vendor documents an IPMT function as well; Excel formula examples conventionally use commas. This is documentation-level compatibility, not an Excel native execution claim. Check payment timing and workbook locale after transfer.
• Other products: No claim is made here for Numbers, Google Sheets, WPS, or other tools because they were not researched or run for this guide.

1. LibreOffice Help 26.8, “Financial Functions Part Three”, IPMT, syntax, definitions, and fifth-period example. Checked 2026-10-13: https://help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html
2. OASIS OpenDocument v1.4, Part 4, §6.12.23 IPMT (OASIS Standard 2025-10-06), formal syntax, default FV and Type, and interest semantics. 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 Formula options, configurable separators. Checked 2026-10-13: https://help.libreoffice.org/latest/en-GB/text/shared/optionen/01060900.html
4. Microsoft Support, “IPMT function”, Excel vendor-documented support only. Checked 2026-10-13: https://support.microsoft.com/en-us/excel/functions/ipmt-function
5. Native evidence: Calc 25.2.3.2 Linux; 19 IPMT numerical assertions independently checked, plus 11 separately recorded IPMT diagnostics (including a PMT−PPMT reconciliation). The saved ODS formulas and CSV output corroborate the historical observations. No new native execution is claimed.