The payoff-time formula and Excel's NPER on one card, checked month by month, with the payment that clears it in one, two or three years.
Months to repay = −ln(1 − r × balance ÷ payment) ÷ ln(1 + r), where r is the monthly interest rate. A £3,200 card at 22.9 per cent APR paid at a fixed £100 a month takes 50 months and costs £1,790.27 of interest. Dividing the balance by the payment says 32 months; interest adds a year and a half.
Worked in full in Personal Finance for Beginners by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
This is the question to answer before choosing a payment, because the answer is far from linear. Over three-fifths of the first £100 goes to interest, and the share falls only slowly. Small changes to the payment move the finish line by months. The card here is card A from the book's debt workbook, held at a fixed payment rather than the shrinking minimum.
| Input | Value |
|---|---|
| Balance | £3,200 |
| APR, interest charged monthly at APR ÷ 12 | 22.9% |
| Fixed payment each month | £100 |
| New spending on the card | none |
Monthly rate: r = 22.9% ÷ 12 = 1.9083% (0.019083)
First month's interest: £3,200 × 0.019083 = £61.07
Reaching the balance: £100 − £61.07 = £38.93
Only £38.93 of the first £100 reduces the debt. Each month the balance is a little lower, so the interest is a little lower and a little more of the same £100 reaches the debt. That acceleration is what the formula captures.
n = −ln(1 − r × B ÷ P) ÷ ln(1 + r)
r × B ÷ P = 61.07 ÷ 100 = 0.6107, so 1 − 0.6107 = 0.3893
ln(0.3893) = −0.94332; ln(1.019083) = 0.018904
n = 0.94332 ÷ 0.018904 = 49.90 months, so 50 payments, the last one £90.27
Excel: =NPER(22.9%/12, -100, 3200) returns 49.90
The formula is the loan payment formula turned round to solve for time instead of payment. Run month by month, with each month's interest rounded to the penny, the card clears in 50 months: 49 payments of £100 and a final £90.27. Total paid is £4,990.27, of which £1,790.27 is interest, 56 per cent of what was borrowed. That is 4.2 years.
| Monthly payment | NPER | Months to clear | Total interest | Interest as % of balance |
|---|---|---|---|---|
| £81.53 | 73.1 | 74 | £2,762.08 | 86% |
| £100 | 49.9 | 50 | £1,790.27 | 56% |
| £150 | 27.7 | 28 | £948.34 | 30% |
| £200 | 19.3 | 20 | £654.91 | 20% |
| £300 | 12.0 | 13 | £412.11 | 13% |
The first row is the card's first minimum payment, £81.53, held level. Paying that every month clears the debt in 74 months; letting the minimum fall with the balance, as card issuers do, takes far longer, as worked in what a minimum payment actually does. At £300 the twelfth payment leaves a few pounds, cleared by a thirteenth of £12.11.
Moving from £100 to £150, an extra £50 a month, saves 22 months and £841.93 of interest. The first extra pounds do the most: adding just £20, to £120 a month, clears the card in 38 months instead of 50 and saves £475.99.
If the aim is a date rather than a payment, solve for the payment instead with =PMT(22.9%/12, months, -3200).
| Clear in | Monthly payment | Total interest |
|---|---|---|
| 12 months | £300.89 | £410.68 |
| 24 months | £167.44 | £818.46 |
| 36 months | £123.70 | £1,253.36 |
Below £61.07 a month the balance never falls, and the formula returns an error because it asks for the logarithm of a negative number. A payment just above that line takes many years. If your payment barely covers the interest, the problem is the rate, and a cheaper rate through a balance transfer may do more than any budget cut.
Payoff time is −ln(1 − rB ÷ P) ÷ ln(1 + r), or NPER in Excel: 50 months and £1,790.27 for £3,200 at 22.9 per cent and £100 a month. Run it for your own card, then run it again with £20 more; the difference is usually larger than expected. The debt workbook, with a blank sheet for one debt of your own that answers exactly these questions, is on the free companion page for this book.
Use =NPER(rate/12, -payment, balance). For £3,200 at 22.9 per cent paid at £100 a month, =NPER(22.9%/12, -100, 3200) returns 49.90: 49 full payments and a smaller fiftieth of £90.27. Round up, because a part-payment is still a month. Total interest is about £1,790.27.
Use the loan payment formula, or =PMT(rate/12, months, -balance). For £3,200 at 22.9 per cent over 24 months, the payment is £167.44 and the interest £818.46. Over 12 months it is £300.89 a month with £410.68 of interest; over 36 months, £123.70 a month and £1,253.36 of interest.
The balance never falls. At 22.9 per cent a £3,200 balance accrues £61.07 of interest in a month, so any fixed payment at or below £61.07 leaves the debt the same or growing. The formula breaks for the same reason: the logarithm of a number at or below zero has no answer.
This article is one calculation from Personal Finance for Beginners. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: personal finance and investing for beginners → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.