NPER Function in Google Sheets: Formula, Examples & Troubleshooting

=NPER(rate, payment_amount, present_value, [future_value, end_or_beginning])

Google documents the timing default but does not state an omitted future-value default in the reviewed reference. Every new formula below supplies both explicitly. Match the rate period to the payment interval. Google NPER reference.

1. In B9 enter =NPER(B2/B3,B4,B5,B6,B7).
2. Expected result: 23.160286 monthly periods.
3. Do not format the result as a percentage or as a date.
4. In B10 enter =ROUNDUP(B9,0). This produces 24 as a planning count of payment dates.

The rounded count does not mean 24 full payments of 550 are necessary. It indicates that the modeled balance remains after 23 full payments and can be cleared on a 24th date with a smaller amount.

For the stated positive rate, zero target, and end timing, the independent mathematical check is:

=LN((-B4)/((-B4)-B5*(B2/B3)))/LN(1+B2/B3)

This is a check of the particular model, not a universal replacement for every NPER argument combination.

Set D2 to =B5. Enter =D2*(1+$B$2/$B$3)+$B$4 in D3 and fill through D25, representing 23 full monthly payments. The remaining balance is approximately 87.902420.

In D26 enter =D25*(1+$B$2/$B$3). The amount needed on the next scheduled payment date is approximately 88.341932, assuming the same full monthly accrual and no fees. Do not enter another −550 in that final cell when auditing the smaller payoff.

A fractional result is a mathematical solution to the constant-payment model. Converting it to an actual calendar payoff depends on the stated payment schedule and rounding rules. This ledger makes that assumption explicit rather than treating 0.160286 of a month as a confirmed lender payoff date.

Google’s published credit-card example uses a positive balance of 2,000, annual rate 18% divided by 12, payment −41, explicit future value 0, and timing 0. It displays 88.36799228 periods. This supports the positive-principal/negative-payment setup in the new example. Published NPER example.

Other published examples reference a blank future-value cell. They do not establish a general omitted-argument default or all blank-reference behavior, so this guide uses explicit numeric zero.

Place −550, −600, and −650 in F2:F4. In G2 enter =NPER($B$2/$B$3,F2,$B$5,$B$6,$B$7) and fill down. Use =ROUNDUP(G2,0) in H2 for planning dates.

With the same ledger assumption, the final smaller payments are approximately 88.341932, 75.007222, and 272.321892, respectively. These are independently calculated forecasts, not quoted settlement amounts.

The first month’s interest in the base example is 60. A payment magnitude no greater than 60 does not reduce the opening balance under this positive-rate, end-payment ledger; it cannot produce the intended finite payoff in the same model. This is a mathematical feasibility check, not a claim about the exact Google error token returned for every such input.

If the answer seems wrong, check signs, payment frequency, annual-to-periodic conversion, target value, and timing before hiding an error. Changing rates, fees, or extra payments require a changing cash-flow model. Zero/negative-rate behavior, beginning-payment edge cases, and a complete error-code map remain unverified here.

Google currently documents NPER. Its introduction date, earliest working version, and retirement history were not established. Other products’ defaults and numerical behavior have not been assumed equivalent.

Related: LN, ROUNDUP, and IF.

References checked October 17, 2026: Google NPER contract, published explicit-FV example.