PPMT Function (LibreOffice Calc)

Choose PPMT when you already know a loan’s periodic interest rate, number of regular payments, present value, and specific payment period. It assumes regular, equal payments and a constant interest rate. It does not model changing rates, extra payments, daily accrual rules, lender fees, or an arbitrary bank’s rounding policy. For the entire periodic payment use PMT; for its interest portion use IPMT. The relationship is:

PMT = PPMT + IPMT

All three results generally have the same negative cash-flow sign for a positive loan principal.

The current LibreOffice 26.8 Help, accessed 2026-10-12, documents:

PPMT(Rate; Period; NPer; PV [; FV [; Type]])

In the examples, arguments use semicolons and decimals use a point, matching the tested English-language Calc setup. Function argument separators and decimal formatting can depend on LibreOffice’s formula-separator settings and locale. If your Calc is configured differently, adapt the separators rather than blindly pasting a comma-separated Excel or Google Sheets example. The financial logic is unaffected by whether a currency symbol or thousands separator is displayed.

The two final parameters are optional in order. The safest way to choose beginning-of-period payments is to explicitly supply FV first:

=PPMT(6%/12;1;24;12000;0;1)

LibreOffice Help says you should not omit an optional parameter while passing a later one. Some other argument patterns were accepted in Calc 25.2.3.2, but are not advised for portable examples.

What counts as a period?

Period means payment number, not a date or a number of months automatically inferred from the loan. For a three-year quarterly repayment plan, periods run from 1 to 12 and the periodic rate is the compatible quarterly rate, not the full annual percentage. For a 30-year monthly loan, periods run from 1 to 360. Never combine an annual rate with monthly periods without intentionally converting the rate.

Suppose a loan has these assumptions:

Enter the following formulas directly in Calc:

Month 1 arithmetic is easy to check independently: 12,000 × (6% ÷ 12) = 60 interest. The periodic loan payment is about 531.847323, so the principal component is 531.847323 − 60 = 471.847323. Calc displays that outgoing principal as −471.847323. Later payments devote more to principal because the outstanding balance is declining.

Copyable cell-based version

Create a new Calc sheet and enter this small model:

Expected results are B7 ≈ −531.847323, B8 ≈ −471.847323, B9 = −60, and B10 ≈ 0. Tiny floating-point residuals around zero are possible. For a display-only check, use =ROUND(B8+B9-B7;10), which is expected to return 0 from the saved reconciliation value; that ROUND formula was not separately native executed. The saved Replicable amortization sheet contains this exact ten-row model. The filled 24-period table below is a proposed layout; the saved standalone 24-call sum verifies its expected total.

Start with the preceding model and put these headers in row 13: Period in A13, Principal in B13, Interest in C13, and Total payment in D13.

In row 14 enter:

A14: 1
B14: =PPMT($B$5;A14;$B$6;$B$3)
C14: =IPMT($B$5;A14;$B$6;$B$3)
D14: =B14+C14

In A15, enter =A14+1 and fill that formula down through A37. Copy the three formulas from B14:D14 down through B37:D37. The period column then contains 1 through 24, and the absolute $B$ references keep the loan assumptions fixed.

Because there are 24 payments and a zero future-value target, the unrounded principal portions add up to approximately −12,000:

=SUM(B14:B37)

As a standalone validation independent of the filled table, 24 explicit PPMT calls summed in native Calc also returned approximately −12,000. Display rounding of each individual payment can introduce a small difference when adding individually rounded cents; do not round the underlying amortization arithmetic unless your real-world lender’s contract does so.

For a visual chart, plot period A14:A37 against the negative principal and negative interest series (or use =-B14 and =-C14 to display positive payment components). The principal share grows as the interest share declines.

For a principal of 200,000 over 360 monthly payments at a nominal 6% annual rate, the monthly periodic rate is 6%/12.

=PPMT(6%/12;1;360;200000)
=PPMT(6%/12;120;360;200000)
=PPMT(6%/12;360;360;200000)

Native Calc 25.2.3.2 results, rounded to cents, are −199.10, −360.44, and −1,193.14 respectively. The full periodic payment from PMT is approximately −1,199.10. The very first payment devotes 1,000 to interest (200000*0.06/12); later payments devote much more to principal. This is a mathematical fixed-rate illustration, not a quote for a real mortgage product or a model of lender-specific compounding/fees.

At a 0% periodic rate, with 1,200 repaid across 12 payments, each payment is entirely principal:

=PPMT(0;5;12;1200)

Native result: −100. This was independently confirmed, despite wording in the ODF OpenFormula 1.3 specification suggesting the rate should be positive. For a zero-interest scenario, PPMT correctly behaves like a simple evenly divided payment in the tested Calc version.

Changing payment timing also changes the schedule. For a positive 12,000 loan at 6% nominal annual interest over 24 months, compare:

=PPMT(6%/12;1;24;12000;0;0)  → −471.847323...
=PPMT(6%/12;1;24;12000;0;1)  → −529.201316...

With Type=1, the first payment occurs at the beginning of the first period, before any interest accrues. Native IPMT for the first beginning-of-period payment was 0, using =IPMT(6%/12;1;24;12000;0;1). Subsequent payments still contain an interest portion.

The optional FV parameter changes the payment needed to reach the desired signed future value at the end. Its sign follows annuity cash-flow conventions, so a positive FV should not be naively interpreted as an unpaid balance of the same sign as a positive borrowed principal. Check the sign of the transaction and the balance you intend to model.

LibreOffice’s official Help includes a beginning-of-period example:

=PPMT(8.75%/12;1;36;5000;8000;1)

Calc 25.2.3.2 returned −350.992937… (about −350.99). It is reproducible, but do not substitute an intended positive remaining mortgage balance for the final 8000 without understanding the cash-flow convention. For an ordinary fully paid loan, omit FV or use 0.

Version-sensitive findings: avoid depending on permissive inputs

The following are native Calc 25.2.3.2 observations, not promises of portable behavior:

• Period=NPer worked in the last repayment period. The current LibreOffice Help explicitly describes that case, although the OASIS OpenFormula 1.3 section prints a stricter inequality 0 < Period < Nper. Prefer the documented LibreOffice Help behavior for Calc and check version differences when portability matters.
• A fractional Period=1.5 and a noninteger NPer=12.5 returned numeric results, even though the ODF PPMT signature uses integer period/count arguments. Use whole periods in shared workbooks.
• Type=2 and Type=-1 returned numeric results in this version, but the documented payment types are only 0 and 1. Use only those supported values.
• =PPMT(0.05;1;12;1000;;1) was accepted by the tested engine even though LibreOffice Help’s general rule discourages omitting a middle optional argument. Use the more explicit =PPMT(0.05;1;12;1000;0;1).
• A negative PV produced a positive principal result. Negative interest-rate values also produced numeric results in some tests, notwithstanding restrictive wording in OpenFormula. Negative-rate finance has special assumptions; don’t assume it is portable without targeted verification.

These differences are documented in the archived test results so they can be rechecked against later Calc releases. In particular, the LibreOffice 26.8 Help website was examined, but LibreOffice 26.8 itself was not executed for this guide.

Yes, at the level of the documented core function and argument order. Both Microsoft Excel and Google Sheets have official PPMT documentation showing the same six conceptual arguments: periodic rate, payment period, number of periods, present value, optional future value, and optional payment type. Excel’s common English syntax uses commas:

=PPMT(6%/12,1,24,12000)

The tested Calc examples above use semicolons. Google Sheets’ English Help also uses commas. Whether a pasted formula needs separators changed depends on the configured locale. Neither Excel nor Google Sheets was executed in this review. The compatibility finding is based on their official product documentation, not observed cross-application output or unsupported promises about edge cases and negative rates.

Native execution (observed): 45 numerical assertions passed out of 45 using real LibreOffice Calc 25.2.3.2 on Linux, controlled by UNO. A further 16 inputs were observed to document errors and version-dependent edge behavior; they are diagnostics, not additional passing assertions. The saved ODS, script and raw results support these historical observations.

Independent calculations (expected): A separately implemented amortization recurrence and annuity payment equation computed the expected principal payment for every full loan scenario. Differences from Calc were compared with an absolute tolerance of 1×10⁻⁷ and a relative tolerance of 1×10⁻⁹. This is independent numerical cross-checking, not a claim of cross-application testing.

Documentation checks (not native execution of the vendor release): The current LibreOffice 26.8 Help, the OpenDocument 1.3 OpenFormula specification, Microsoft Excel Support, and Google Sheets Help were checked 2026-10-12. These are documentation checks, separate from the recorded native tests.

Official references, with the principal claims they support:

1. LibreOffice 26.8 Help — Financial Functions Part Two (PPMT and PMT): official function descriptions, syntax, required/optional arguments, payment types, final-period example, and the published FV worked example. Accessed 2026-10-12.
2. LibreOffice 26.8 Help — Financial Functions Part Three (IPMT): companion interest-payment formula and syntax. Accessed 2026-10-12.
3. OASIS — OpenDocument Format 1.3, Part 4 (OpenFormula), §6.12.37 PPMT: typed argument signature, defaults, and the noted period/rate constraints (some differ from native Calc or current Help). Accessed 2026-10-12; specification stage cs02, not a substitute for executable Calc results.
4. Microsoft Excel PPMT function: documented Excel argument order, periodic-rate units, default future value, and 0/1 payment timing. Accessed 2026-10-12; documentation check only.
5. Google Sheets PPMT function: documented Sheets argument order and periodic-rate guidance. Accessed 2026-10-12; documentation check only.