Zoho Sheet NPER: Payment Periods and an Adjusted Final Installment
Calculate annuity periods in Zoho Sheet, distinguish fractional terms from payment counts, and reproduce an eleven-payment payoff schedule.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer. =NPER(1%;-200;2000) has an independently calculated result of approximately 10.5886444594 periods. Under the period-end loan model, that is a fractional annuity term; it is not an instruction to make 10 full payments and stop.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked sources.
- Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
- Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
- Checked: October 14, 2026, against the official NPER reference. Documentation and independent annuity models; no native Zoho Sheet test.
Syntax, required inputs and defaults
=NPER(rate; pmt; pv; [fv]; [type])
Rate is the interest rate per payment period. pmt is the fixed amount paid each period, and pv is the current annuity value. These three arguments are required. The future balance fv defaults to 0. Type defaults to 0 for payments at period-end; 1 places them at period-start.
Keep signs consistent with a single viewpoint. In the loan example, receiving 2000 is positive, and repayments of 200 are negative. In a savings example, an initial deposit and recurring deposits are negative outflows, while the future amount available to receive is positive.
The result uses the period represented by rate and pmt. A monthly model returns months. Dividing an explicitly nominal annual rate of 12% by 12 gives the 1% monthly rate used here; converting an effective annual rate requires a different calculation.
Six complete examples
Create a new sheet named Periods. Put Periodic rate, Payment, Present value, Future value, Type and Periods in A1:F1. Enter the inputs below in A2:E7, put =NPER(A2;B2;C2;D2;E2) in F2, and fill through F7.
| Row | A | B | C | D | E | Expected F, 9 decimals |
|---|---|---|---|---|---|---|
| 2 | 1% | -200 | 2000 | 0 | 0 | 10.588644459 |
| 3 | 1% | -200 | 2000 | 0 | 1 | 10.478145085 |
| 4 | 0.5% | -150 | -500 | 5000 | 0 | 27.593029065 |
| 5 | 10% | 0 | 1000 | -1331 | 0 | 3.000000000 |
| 6 | 0% | -200 | 2000 | 0 | 0 | 10.000000000 |
| 7 | 1% | -250 | 2000 | 0 | 0 | 8.379782559 |
Rows 2–4 and 6–7 are monthly models; row 5 is a separate annual lump-sum example. Its 1000 grows to 1331 in three years because 1000×1.1³=1331. The payment is explicitly zero rather than an omitted required argument.
Put =NPER(A2;B2;C2) in H2 to check the omitted fv and type defaults; expect the same result as F2. Put =NPER(A3;B3;C3;D3;1) in H3 to retain the beginning-payment case explicitly.
The zero-rate row uses the annuity model’s simple limiting calculation, 2000/200=10. It does not establish every native rate-domain or coercion boundary. In the savings case, 27.593029065 is the equivalent fractional term, not a guarantee about a bank’s discrete interest or deposit schedule.
Why a fractional answer needs a schedule
NPER solves a constant-rate annuity equation. It need not return a whole number. Rounding 10.5886 down to ten would leave a balance, while treating eleven as eleven full 200 payments would overpay in this model.
For month-end payments, retain ten full installments and calculate the smaller amount due at the next scheduled month-end. This is a separate discrete-payment interpretation of the fractional result. Daily interest, contractual payoff rules, fees and cent rounding can change an actual schedule.
Advanced: ten full payments and one adjusted payment
Create a separate sheet named Payoff. In A1:E1 put Month, Opening balance, Interest, Payment and Closing balance. Put 1% in H1 and -200 in H2.
Enter 1 in A2 and 2000 in B2, then enter:
- C2:
=B2*$H$1 - D2:
=$H$2 - E2:
=B2+C2+D2
In row 3 enter A3 =A2+1, B3 =E2, C3 =B3*$H$1, D3 =$H$2 and E3 =B3+C3+D3. Fill A3:E3 through row 11, giving months 2 through 10.
For the adjusted month 11, enter A12 =A11+1, B12 =E11, C12 =B12*$H$1, D12 =-(B12+C12) and E12 =B12+C12+D12.
| Month | Expected interest | Expected payment | Expected closing balance |
|---|---|---|---|
| 1 | 20.000000 | -200.000000 | 1820.000000 |
| 5 | 12.691278 | -200.000000 | 1081.819098 |
| 10 | 3.136651 | -200.000000 | 116.801743 |
| 11 | 1.168017 | -117.969760 | 0.000000 |
Use full stored precision in the calculations and six-decimal formatting for comparison. The final payment is approximately 117.9697600243 in magnitude. Ten payments of 200 plus that amount total 2117.9697600243, making modeled interest approximately 117.9697600243. Those figures do not apply a contractual cent-rounding policy.
Errors, feasibility and related functions
At a 1% monthly rate, the first month’s interest on 2000 is 20. A payment of only 20 would leave the balance unchanged in this recurrence, so it would not repay the loan in any finite number of periods. The short function page does not specify the exact native error or result for every infeasible combination.
Check signs, rate units, target balance and type before interpreting an unexpected negative term. Zoho’s generic error table is not a complete specification for every numeric domain, blank argument or invalid timing value. Its displayed examples refer to an embedded dataset; the tables here provide their own complete inputs.
PMT solves for the payment, RATE for the periodic rate, and FV for the ending balance. Use the documented semicolon separators and numeric cells rather than currency labels embedded in text.