PlanMaker NPER Function: How Many Payments Until a Loan Is Paid Off?
Calculate the number of regular payment periods in SoftMaker PlanMaker with the NPER function, including monthly loans, savings goals and payment timing.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
NPER estimates how many equal payment periods a loan or savings plan needs. =NPER(6%/12,-300,10000) has an independently expected result of approximately 36.555396 months.
Availability and version history
- Introduced: Documented in the PlanMaker 2016 manual; the original introduction release and date remain unknown.
- Availability: Documented in the 2026 English reference; no final supported-version endpoint is established.
- Scope: Versioned documentation does not establish identical behavior in every desktop, mobile or localized build.
- Checked: October 14, 2026, against the official NPER reference, with independent arithmetic. No native PlanMaker test.
Official syntax, inputs and defaults
=NPER(Rate, Pmt, PV [, FV] [, Type])
| Position | Argument | Required? | Type and meaning |
|---|---|---|---|
| 1 | Rate |
Yes | Numeric rate per payment period, e.g. 6%/12 for 6% nominal annually with monthly payments. |
| 2 | Pmt |
Yes | Numeric fixed payment amount each period. Payments out usually have a negative sign. |
| 3 | PV |
Yes | Numeric present value or initial loan/investment amount. For a borrower’s loan, this is typically positive. |
| 4 | FV |
No | Desired ending balance. SoftMaker explicitly sets it to 0 if omitted. |
| 5 | Type |
No | 0 or omitted: cash payments at period-end. 1: at period-start. |
NPER returns the number of payment periods; it does not multiply this automatically by 12 or convert it into a calendar date. The output can be a fractional period because the calculation solves a financial equation mathematically. This is useful for estimating a payoff horizon; actual loan statements may differ due to accrued interest, rounding, fees, prepayment rules or final-payment handling. Manufacturer reference.
Worked worksheet: estimate loan payoff time
Enter a simple loan model, with the labels in column A and numeric inputs in column B:
| Cell | Entry |
|---|---|
| A2 | Annual nominal rate |
| B2 | 6% |
| A3 | Monthly payment (money out) |
| B3 | -300 |
| A4 | Loan amount (money in) |
| B4 | 10000 |
| A5 | Interest rate per month |
| B5 | =B2/12 |
| A7 | Periods required (monthly) |
| B7 | =NPER(B5,B3,B4) |
| A8 | Years as an average |
| B8 | =B7/12 |
Expected model outputs: B7 ≈ 36.555396 monthly periods and B8 ≈ 3.046283 years when simply dividing 12 months per year. A period count does not specify an actual final debit date or amount.
Why a negative payment? A positive initial loan is money received by the borrower. The regular repayments move money in the opposite direction, represented by -300.
What the calculation actually solves
NPER finds n such that the loan’s accumulated cash flows balance:
PV*(1+r)^n + Pmt*((1+r)^n - 1)/r + FV = 0
Here PV=10000, Pmt=-300, r=0.06/12, and the omitted FV=0. Substituting the independently derived n≈36.555396 gives a residual near zero. The mathematical inversion (for r≠0, Type=0) is:
n = ln(Pmt/(Pmt + PV*r)) / ln(1+r)
This log expression is only meaningful where its arguments are positive and the chosen cash flows are feasible; the NPER function handles its own numeric validity and errors.
Turn the fractional term into a payment check
For the original month-end loan, 36 payments of 300 leave approximately 165.973759. One more month’s interest produces a final payment of approximately 166.803628, rather than another full 300. In B10 enter =10000*(1+B5)^36-300*((1+B5)^36-1)/B5; in B11 enter =B10*(1+B5). This is an idealized schedule: contractual interest, rounding and fees may change the actual payoff.
The first month’s interest is 10000*0.005=50. A payment of only 50 would not reduce principal in this simple model. Check feasibility before interpreting an extreme period count.
Advanced examples
1. Beginning-of-month payments. If payments happen at the beginning of each period instead of at the end, include FV=0 explicitly to reach the fifth argument:
=NPER(6%/12,-300,10000,0,1)
Expected: approximately 36.355993 periods, different from the end-of-period result. Beginning payments reduce the time required because they reduce the balance earlier.
2. Savings target with a starting balance. Suppose you already have 1,000 invested and contribute 100 at each period start at a nominal 5% annual rate, aiming for 5,000. With a consistent cash-flow sign convention:
=NPER(5%/12,-100,-1000,5000,1)
Expected: about 35.562763 monthly periods. The initial investment and contributions are negative cash flows, whereas the targeted future proceeds are positive.
3. Zero interest (special-case mathematical check). For a loan of 5,000 with payments of 250:
=NPER(0,-250,5000)
The direct cash-flow equation 5000+(-250)*n=0 implies 20 periods mathematically. This independent zero-rate equation avoids the logarithmic formula’s division by zero.
4. How much must be paid to finish within a fixed term? NPER answers how long a fixed payment takes. If you know the term but not the payment, use the officially documented PMT function instead; for example =PMT(6%/12,36,10000) gives an independently expected -304.219375 payment that reaches the target on the 36th period. Official PMT reference.
Troubleshooting and limits
| Symptom | Check and action |
|---|---|
| An unexpectedly long or negative period count | Verify Pmt and PV signs; amounts that do not repay the loan at the stated rate may not lead to a meaningful payoff term. |
| Formula reports an error | Confirm all three required numeric arguments, syntax, local function name/separator and the intended 0/1 Type. |
| Loan term differs from lender statement | Real-world effective rates, payment rounding, daily interest, fees and the final partial payment may differ from the fixed-rate fixed-payment annuity model. |
| Value has a decimal fraction | NPER solves for a continuous mathematical period count. Do not silently treat 36.56 as an actually scheduled 36.56th full monthly debit; check the lender’s payoff conditions. |
| Payment timing omitted accidentally | The fifth argument is Type; insert a zero fourth argument first: =NPER(rate,pmt,pv,0,1). |
| Annual/monthly units are mixed | Match a monthly periodic rate, monthly signed payment and count of months. |
| Division-by-zero or unsuitable input while checking with a manual logarithm | Use the cash-flow identity rather than the logarithmic formula for zero-rate conditions, and check whether the desired balance can be reached. |
SoftMaker’s NPER help does not specify a particular error code for every infeasible financial input. Use the documented general error meanings without inventing a specific error for every infeasible payment pattern.
Related guides and workbook portability
PMT solves for a constant payment. NPV discounts a sequence of periodic cash flows with different amounts. Keep rates, periods and cash-flow signs consistent when comparing results. Similar function names in another spreadsheet do not prove identical solver behavior, locale syntax or imported-file recalculation.