NPER returns the number of payment periods needed to pay off a loan or reach an investment goal at a fixed interest rate.
=NPER(rate, pmt, pv, [fv], [type])Describe what you need. The generator will reach for NPER where NPER is the right tool, and tell you when it is not.
| Argument | Required | Description |
|---|---|---|
| rate | Required | The interest rate per period (e.g., 1% monthly). Must be greater than -1. If zero, no interest accrues. |
| pmt | Required | The payment made each period. Must have opposite sign from pv (one is inflow, one is outflow). |
| pv | Required | The present value—the current debt or investment. Must have opposite sign from pmt. |
| fv | Optional | Optional future value target (defaults to 0). The amount owed or accumulated at the end. |
| type | Optional | Optional; 0 (default) for end-of-period payments, 1 for beginning-of-period payments. |
A number representing the count of payment periods required.
Excel: All · Google Sheets: Supported
| Task | Hours Logged | Hourly Rate | Total Invoice |
| Web Redesign | 120 | $85 | $10,200 |
=NPER(1%, -500, 10200, 0)Result: 21.35
The Web Redesign project invoice totals $10,200 (120 hours × $85/hr). If the client pays $500 monthly with 1% monthly interest on the outstanding balance, NPER calculates it takes approximately 21 periods (months) to fully pay the invoice.
| Project | Equipment Cost | Monthly Payment | Interest Rate |
| Infrastructure Support | $15,000 | $400 | 0.75% |
=NPER(0.75%, -400, 15000)Result: 42.43
A $15,000 equipment purchase to support project deliverables is financed at 0.75% monthly interest. With $400 monthly loan payments, NPER returns approximately 42 periods, meaning the equipment is fully amortized in about 3.5 years.
| Task | Retainer Balance | Weekly Billing | Weekly Interest |
| Mobile App Development | $5,000 | $250 | 0.1% |
=NPER(0.1%, -250, 5000, 0)Result: 20.02
A client retainer of $5,000 is billed at $250 per week with a 0.1% weekly interest charge on the balance. NPER calculates that the retainer is consumed in approximately 20 billing cycles (weeks).
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | When payment (pmt) and present value (pv) have the same sign, or when there is no mathematical solution to the equation. For example, =NPER(1%, 500, 10000) has both pmt and pv positive, so no payoff is possible. | Ensure pmt and pv have opposite signs: pv should be negative (money owed/borrowed) and pmt should be positive (payment made), or vice versa. Change =NPER(1%, 500, 10000) to =NPER(1%, -500, 10000). |
| #DIV/0! | When rate equals exactly -1 (negative 100%). The NPER formula contains division by LN(1 + rate), and LN(0) is undefined, causing a division by zero error. | Use a rate value other than -1. If you intended 0% interest, use 0 instead. Common monthly rates like 0.5%, 1%, or 2% work fine. |
| #VALUE! | When one or more arguments contain text, dates, or other non-numeric values. For example, =NPER('1%', -500, 10200) with the rate as text, or =NPER(1%, -500, 'Invoice') with pv as text. | Convert all arguments to numbers. Remove quotes around numeric values, use VALUE() to convert text numbers, or reference cells containing only numbers. Dates can be multiplied by 1 to convert to their numeric serial value. |
The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.
Open the formula generatorReviewed 2026-09-17