NPER in Excel: Solve the Period Count and Interpret Fractional Results

=NPER(rate, pmt, pv, [fv], [type])

rate, pmt and pv are required: periodic interest rate, constant signed payment and initial present value. fv defaults to zero. type is zero or omitted for end-of-period payments and one for beginning-of-period payments.

Use matching units. A monthly rate produces a monthly period count. Dividing that result by twelve expresses years only when twelve modeled periods actually correspond to one year.

An initial loan receipt can be positive pv with negative repayments. A savings model can have negative pv and negative periodic deposits balanced by a positive future receipt. The signs describe different cash-flow setups; they are not interchangeable labels.

Put 1% in B2, −100 in B3 and 1000 in B4. Use =NPER(B2,B3,B4,0,0) in B6. The independent result is 10.5886444594.

For these inputs, first-period interest is ten and the payment magnitude is one hundred, so the balance decreases. Increasing the payment magnitude to 150 gives an independent period count of 6.9337263760. The direction is sensible: a larger payment retires this ordinary balance sooner.

A payment of only ten at 1% on 1000 would cover interest without reducing principal in a period-end schedule. A finite payoff to zero would not occur in that model. NPER does not make an infeasible cash-flow plan feasible.

Starting at 1000, apply ten end payments of 100 with 1% interest per period. The independent balance after payment ten is 58.4008712992. One further full period of interest makes the final amount due 58.9848800122 at the eleventh period end.

Thus eleven payment dates with a smaller final payment can settle this illustrative schedule. Eleven payments of exactly 100 would overpay it. Rounding NPER upward can identify a possible count of payment dates, but it does not by itself calculate the adjusted final payment.

The fractional annuity result follows the continuous exponent/logarithm relation. Actual payment calendars, contractual interest and cent-rounding rules require a separate schedule.

=NPER(12%/12,-100,-1000,10000,0) has independent target 60.0821228538 monthly periods. Both the initial amount and deposits are outgoing; the future target is a positive receipt.

Changing type to one gives 59.6738656743 because deposits occur earlier. The current Microsoft example’s second row describes beginning payments even though its formula omits type. The documented default is end-of-period; this guide follows the formula and default, not that inconsistent row description.

Use a forward FV calculation with the solved nper as a mathematical inverse check. Preserve the calculated nper instead of retyping its rounded display. Such a fractional-period check is not a native payment-calendar test.

The source also shows =NPER(1%,-100,-1000) returning approximately −9.57859404. The zero future value is implicit, while the initial amount and regular payments have the same negative sign.

A negative mathematical result is not a positive-time savings horizon. Examine the target and cash-flow signs before interpreting it. If the intended question was how long to reach a positive balance, supply the target fv explicitly as in the savings example.

At zero rate, the balance equation gives nper=-(pv+fv)/pmt when pmt is nonzero. A present receipt of 1000 repaid by 100 per period therefore targets 10 periods.

Check whether the payments can reach the requested target under the chosen rate and timing. The dedicated reference does not enumerate all native no-solution errors or unusual rate domains, so this guide does not assign an invented error code to every infeasible setup.

Keep nominal annual-rate conversion distinct from effective-rate conversion. Rate, payment frequency and nper must agree. NPER assumes constant rate and payment; rate changes, irregular payments and fees need a more detailed model.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft NPER reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.