NPER tells you how many monthly payments it takes to clear a balance at a given interest rate and a fixed payment. The formula is =NPER(monthly rate, -payment, balance). Run it twice with two different payments and you can see how much time and interest a slightly larger payment saves.
What do I enter?
Set up three cells. B1 Balance, for example 80000. B2 Interest rate per month, for example 3%. B3 Monthly payment. Many statements show an annual rate; in that case use B2/12 inside the formula. Then in B4 write:
=NPER(B2,-B3,B1)
Put the payment in as a negative number, because it is money going out, and the balance as positive. The result is the number of payments, usually with a fraction. Wrap it in ROUNDUP(B4,0) to get whole months.
What does a credit card example look like?
Suppose your statement carries a balance of ₹80,000 and the card charges 3% a month. That is a made-up rate for illustration; read your own statement for the real one. Monthly interest on the balance is 80,000 x 3% = ₹2,400, so any payment below ₹2,400 never reduces the balance. Compare two payments, assuming you make no new purchases.
| Monthly payment | NPER result | Months to clear | Total interest, about |
|---|---|---|---|
| ₹4,000 | 31.0 | 31 | ₹44,000 |
| ₹6,000 | 17.3 | 18 | ₹23,700 |
For ₹4,000, 4,000 x 31 = 1,24,000 paid, minus the 80,000 balance, leaves about ₹44,000 of interest. For ₹6,000, the result is 17.28 months, so 6,000 x 17.28 = 1,03,690 less 80,000 gives about ₹23,700. The extra ₹2,000 a month shortens the repayment by about 13 months and saves roughly ₹20,300 of interest on these numbers.
The formula to compute total interest is =B3*B4-B1, where B4 is the unrounded NPER result. It is an estimate, because the final payment is usually smaller than the others.
Why does NPER return #NUM!?
The payment is too small. When the payment is equal to or less than the interest charged each month, the balance never falls, and Excel cannot find a number of periods. In the example, a payment of ₹2,400 or less returns #NUM!. The fix is a bigger payment. As a quick test, compare B3 with =B1*B2.
Does it work for other loans?
Yes, anywhere the payment is fixed. For a personal loan where you decide to pay a fixed amount above the EMI, use the outstanding balance as B1, the loan's monthly rate and your new payment. NPER then shows the months left. A common use is the home loan, where B4 is the revised EMI after a part payment.
NPER has two optional arguments, future value and type. Leave them out for a balance paid to zero with payments at the end of each month.
How do I turn this into a payoff table?
Add a small grid with payments in the first column, say 3,000, 4,000, 5,000, 6,000, 8,000. In the next column use =IFERROR(ROUNDUP(NPER($B$2,-A8,$B$1),0),"Never") and in the third =IFERROR(A8*NPER($B$2,-A8,$B$1)-$B$1,""). The grid shows how the time and interest change with each payment, and "Never" marks payments that are too small.
Where do I find my real balance and rate?
Read the latest card statement for the outstanding amount, the rate and the minimum due. If you upload card statements to SpendixAI, the account keeps its own transactions, which helps you see what you are spending and paying each month.
Next steps
- Find the balance and the rate on your statement.
- Enter them in the sheet and try three payment amounts.
- Choose a payment you can keep up every month and set a reminder.
- Check the balance after three months against the sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


