Financial
NPER function: calculate how many months it takes to pay off a loan
Find the number of payment periods needed to clear a balance with NPER, match monthly rate and cash-flow signs, and guard payments below monthly interest.
Match periodic interest rate and cash-flow sign convention
NPER(rate, pmt, pv, [fv], [type]) solves for the number of equal payment periods required to move from present value pv to future value fv (0 by default when paying off debt).
For this example, divide the nominal annual rate by 12 to obtain the monthly rate. This is not the conversion for an effective annual rate. Payments occur at month end, with a constant rate and no fees or extra payments. Give pmt the opposite sign of pv: a +10,000 loan uses -250 monthly repayments.
| Loan PV (A) | Annual Rate (B) | Monthly Pmt (C) | Exact NPER Months |
|---|---|---|---|
| 10000 | 0.06 (0.5%/mo) | -250 | 44.74 months (~45 payments) |
| 10000 | 0.06 (0.5%/mo) | -300 | 36.56 months (~37 payments) |
| 10000 | 0.00 (0%/mo) | -250 | 40.00 months |
| 10000 | 0.06 ($50/mo interest) | -40 | Never amortizes (payment < interest) |
Calculate exact and whole-month payoff length
At 6% annual interest (0.5% per month), paying $250/month against $10,000 takes ln(250 / (250 - 50)) / ln(1.005) = 44.74 months—meaning 44 full payments plus a smaller final payment in month 45.
=ROUND(NPER(B2 / 12, C2, A2), 2)Guard monthly payments that do not cover accrued interest
On a $10,000 balance at 0.5%/month, first-month interest is $50. A $40 payment leaves the balance growing forever. Checking -C2 <= A2*(B2/12) catches unpayable schedules cleanly.
=IF(-C2 <= A2 * (B2 / 12), "Payment below interest", ROUND(NPER(B2 / 12, C2, A2), 2))Try the example
Use the original values or change them to test your understanding.