NPER Function (LibreOffice Calc)

=NPER(Rate; Pmt; PV [; FV [; Type]])

Argument order matters. Use a semicolon (;) between arguments in the verified English-language Calc examples. Decimal, date and function-name conventions may vary with locale and chosen formula syntax. LibreOffice Calc Guide 26.2 — Formula settings, checked 2026-10-13.

Official basis: LibreOffice Help — Financial Functions Part Three, NPER (displayed 26.8, checked 2026-10-13); OASIS OpenFormula 1.4 §6.12.29 NPER (standard published 2025-10-06). Both specify the basic five-argument meaning, with FV=0 and Type=0 as defaults.

Can an optional argument be skipped?

In portable models, provide FV explicitly if you want to specify Type. For example:

=NPER(5%/12;-300;10000;0;1)

The official LibreOffice Help says optional parameters should only be omitted when no later parameter follows. However, native Calc 25.2.3.2 also accepted =NPER(5%/12;-300;10000;;1), returning the same value. These are conflicting documentation-versus-runtime observations; use the explicit-zero version for readability and portability, and avoid relying on skipped internal fields. Official NPER Help, accessed 2026-10-13.

A lender advances $10,000, the annual nominal rate is 5%, and the borrower pays $300 at the end of each month.

=NPER(5%/12;-300;10000)

Native-tested output: 35.96233020715945 monthly periods, approximately 35.96 months. This is a mathematical period count, not a literal invoice for a fraction of a month. Under the same idealized constant-rate assumptions, you would usually budget for 36 scheduled payment dates, with a smaller final payment than $300. A rounding function changes only the displayed period count; it does not recompute the reduced final installment.

The signs are essential: 10000 is a loan amount received, and -300 is money paid away. If the borrower pays at the beginning of each month, set Type=1:

=NPER(5%/12;-300;10000;0;1)

Native-tested output: 35.80142857976266. Paying sooner reduces the modeled time required at the same rate and payment amount.

Reproduce with editable cells in LibreOffice

Create a sheet named WorkedExamples and enter:

D9 returns approximately 35.9623302072. This specific cell-reference formula was recalculated in Calc 25.2.3.2, alongside the literal-argument version. The historical workbook records this exact cell-reference example.

Zero-interest version

With no interest, a $10,000 loan paid at $200 monthly lasts 50 months:

=NPER(0;-200;10000)

Native-tested result: exactly 50. In the workbook, B10=10000, C10=0 and E10=200 give the identical result in D10 using =NPER(C10;-E10;B10); the cell-reference version was tested too. A zero interest rate is a legitimate NPER input, unlike NOMINAL, which requires a positive effective rate under OpenFormula.

The following cases were calculated in Calc 25.2.3.2 and cross-checked independently. Values may display with fewer digits depending on the cell format.

About cash-flow signs for FV: A loan of positive PV=10000 can use a negative FV, such as -500, for a balance still owed at the stopping point. The signs in the formula follow the financial cash-flow equation; enter your desired balance consistently, rather than guessing based solely on the absolute value.

Suppose you start with a savings account balance of $1,000 and deposit $100 at each month-end, aiming for $10,000, with a 5% nominal annual rate compounded monthly.

=NPER(5%/12;-100;-1000;10000)

Native-tested output: 73.9499637457, approximately 74 months. Here, both the initial balance (PV=-1000) and monthly deposits (Pmt=-100) represent funds contributed to the account from the saver’s viewpoint; the future value of accumulated funds is positive. This example is a model, not a bank statement: it excludes taxes, account fees, promotional rate changes and irregular deposits.

To convert to years for an approximate answer, divide the monthly period count by 12. This estimate is 6.1625 years when using the exact 73.9499637 months. If reporting the number of whole monthly contributions, round the period count to the next whole payment after confirming the final-period timing.

For a constant positive monthly interest rate, a loan payment at or below the first-period interest often cannot retire the original balance in any finite number of periods.

Example: $1,000 balance, 10% interest per period, $10 payment:

=NPER(10%;-10;1000)

Native-tested output: #NUM! (error code 503). First-period interest alone is $100, so a $10 installment fails even to cover interest. This is not a rounding problem. At exactly $100 payment, another native test returned #NUM! because the original principal does not decrease. The remedy is to revisit the payment or interest assumptions, not hide the error by taking its absolute value.

In a reusable template, you can flag insufficient payments before invoking NPER: for an ordinary positive-rate debt with positive principal B5, monthly rate C5/12 and positive payment amount D5, compare D5 with B5*(C5/12). This is a preliminary diagnostic, not a full formula for irregular fees or changing loan rates.

Holding other inputs fixed, Type=0 and Type=1 produce different values because beginning-of-period payments earn or save an extra period of interest. The test loan yielded 35.962330 periods with Type=0 and 35.801429 with Type=1. Do not change Type casually to force a preferred answer; it represents the actual agreement.

A nonzero FV represents a desired closing balance rather than full payoff. For the sample loan, native Calc gave:

=NPER(5%/12;-300;10000;-500;0)

Result: 34.2863676513 periods, compared with 35.9623302072 for FV=0. This is appropriate for estimating when the debt drops to $500, not when it is fully repaid. For budget planning, name the resulting column “Months to target balance” so users do not mistake it for “Loan paid off”.

The OASIS OpenFormula 1.4 §6.12.29 standard defines NPER using the usual annuity cash-flow relationship. Let r be the periodic rate, p the signed recurring payment, PV the present value, FV the ending target, t payment timing (0 or 1), and n the number of periods. For r different from zero:

0 = PV*(1+r)^n + p*(1+r*t)*((1+r)^n - 1)/r + FV

With A = p*(1+r*t)/r, the mathematically equivalent solution where its logarithm is defined is:

n = LN((A-FV)/(PV+A)) / LN(1+r)

For a zero interest rate, the equation instead becomes PV + p*n + FV = 0, so n = -(PV+FV)/p, provided the payment is nonzero and the model is meaningful. The separate test program used this algebra, not Calc’s NPER function, to calculate its expected outputs. Native Calc results were then compared against those independently computed numbers; 63 numerical checks across NOMINAL and NPER passed the selected tolerance.

The logarithmic equation explains why NPER often returns decimals: the mathematical model solves for a continuous exponent. Actual lenders may require an integer schedule with a different last payment; NPER itself is not an amortization schedule and does not simulate changing rates or real calendar dates.

Rate must be for one payment period. If you were quoted a nominal annual rate of 5%, and payments/compounding are monthly in the same assumed model, 5%/12 is a periodic rate. If you were quoted an effective annual rate of 5%, its equivalent monthly rate under monthly compounding is instead:

=(1+5%)^(1/12)-1

These two monthly rates differ. The choice depends on what the stated rate actually means, not on which choice makes a loan end sooner. The NOMINAL guide explains effective-to-nominal conversion, while RATE solves for an unknown periodic interest rate given a series of payments.

Important documentation/runtime discrepancy for blank optional fields

Help warns that an earlier optional field cannot be omitted if a later field is supplied, yet the tested Calc 25.2.3.2 instance accepted the expression with an empty FV (;;1). This tutorial deliberately shows both and prefers the explicit zero. The UNO test log records the exact input and native result. The same distinction between documented behavior and observed behavior applies to nonbinary Type and to the =NPER(0;0;0) diagnostic.

• LibreOffice Calc: native-tested on 25.2.3.2, Linux; official Help checked 2026-10-13 and displayed 26.8. The native tests do not establish results for 26.8 or other operating systems.
• OpenDocument files: NPER is defined in OASIS OpenFormula 1.4 §6.12.29, with Fv=0, PayType=0 and two normal payment timing settings; evaluators with medium/large support must also accept negative interest rates. This is a standards statement, not a test of every ODF-consuming application.
• Microsoft Excel: Microsoft’s NPER function documentation describes equivalent argument meanings. That confirms documented availability, not matching native Calc error handling, rounding or file interchange; Excel was not executed.
• Google Sheets: Google’s financial-function documentation recognizes number-of-periods calculation, but this review did not native-test Google Sheets or independently verify all argument/locale details in its current product version. Do not infer exact output compatibility from a shared function name.

• PMT — calculate fixed payments when the term is known.
• PV — value of future cash flows today.
• FV — value at the end of a period schedule.
• RATE — periodic interest rate from other loan variables.
• NOMINAL — convert effective annual rates to nominal annual rates.
• PPMT — principal part of a payment.

1. LibreOffice Help, Financial Functions Part Three — NPER, latest English Help displaying 26.8, accessed 2026-10-13: https://help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html . Documents official syntax, required/optional arguments, timing, and the stated optional-argument omission rule.
2. OASIS OpenDocument 1.4, Part 4 OpenFormula, §6.12.29 NPER, OASIS Standard dated 2025-10-06, accessed 2026-10-13: https://docs.oasis-open.org/office/OpenDocument/v1.4/os/part4-formula/OpenDocument-v1.4-os-part4-formula.html . Documents defaults, sign-based annuity equation, zero-rate branch and negative-rate evaluator conformance.
3. LibreOffice Calc Guide 26.2, Chapter 16 — Formula options, accessed 2026-10-13: https://books.libreoffice.org/en/CG262/CG26216-SettingUpCustomizing.html . Supports locale/formula notation distinctions and the English-function-name setting.
4. Microsoft Excel NPER official reference, accessed 2026-10-13: https://support.microsoft.com/en-us/excel/functions/nper-function . Documentation-only compatibility context, not native test evidence.

Scope of testing: The recorded suite contains 63 numerical checks across NOMINAL/NPER and 19 separately counted diagnostics. The saved workbook corroborates the recorded results, including the degenerate NPER(0;0;0) result that contradicted the tester’s initial expectation. No fresh native run or cross-platform test was performed.