Debt avalanche vs snowball: months to payoff with NPER, worked

The avalanche method (extra money goes to the highest interest rate first) always costs the least interest. The snowball method (extra money goes to the smallest balance first) clears your first debt sooner. In the worked example below, with three debts totaling $18,700 and a fixed $700 a month, both methods finish in month 32. Avalanche costs $3,293.89 in interest and snowball $3,395.62, a difference of $101.73. Snowball pays off its first debt in month 5; avalanche does not close any account until month 20.

What makes either method work is the same thing: a fixed total payment, with every freed-up minimum rolled into the next debt. To find how long a single debt will take, use =NPER(apr/12, -payment, balance).

What is the difference between the avalanche and snowball methods?

Both methods start the same way. You pay the minimum on every debt each month, decide on a fixed total you can pay, and send everything above the minimums to one target debt. When the target is paid off, its minimum and the extra both move to the next target. The total payment never drops until you are debt-free.

The only difference is the order:

Avalanche is mathematically optimal for minimizing interest when payments are fixed. How much it saves depends on the gap between the rates and on how large your extra payment is. The avalanche vs snowball question page gives the short version.

How do you calculate months to payoff for one debt with NPER?

NPER returns the number of periods needed to pay off a balance at a fixed rate and payment. For a monthly payment on a card with an annual percentage rate (APR):

=NPER(apr/12, -payment, balance)

The payment is negative because it flows the other way from the balance. Take a $6,500 credit card at 24.99% APR with a $160 minimum:

=NPER(24.99%/12, -160, 6500) returns 90.77

That is 91 payments, the last one smaller than the rest: seven and a half years. An estimate of total interest is the number of payments times the payment minus the balance:

=NPER(24.99%/12, -160, 6500)*160 - 6500 returns $8,023.45

A month-by-month schedule with interest rounded to the cent gives $8,023.82; the small gap comes from the final partial payment and rounding. Either way, you would pay more in interest than you borrowed.

Two things to watch:

• If the payment is not larger than the first month's interest, the balance never falls and NPER returns #NUM!. Here the first month's interest is $6,500 × 24.99% / 12 = $135.36, so any payment of $135 or less never pays the card off.
• Real credit card minimums are usually a percentage of the balance, so they shrink as you pay. NPER assumes a fixed payment. Holding your payment at the starting amount is exactly what makes the payoff time finite and predictable.

Raising the payment to $405 changes the answer to =NPER(24.99%/12, -405, 6500) = 19.74, so the card is gone in month 20. That is the avalanche plan below.

How do the two methods compare on three debts at $700 a month?

The budget is $700 a month, so $245 is available above the minimums. Each month the model charges interest at APR/12 on the opening balance, rounded to the cent, then pays the minimums, then sends whatever is left to the target debt. The minimums are held fixed.

Paying only the minimums

For comparison, here is what happens if you pay only the minimums and never roll anything over:

Avalanche versus snowball

Avalanche targets the credit card (24.99%), then the store card (19.99%), then the car loan (7.50%). Snowball targets the store card ($1,200), then the credit card ($6,500), then the car loan ($11,000). The orders differ only in the first two debts, because the car loan has both the largest balance and the lowest rate.

Compared with paying minimums only, either plan cuts total interest by more than two-thirds and finishes about five years sooner.

Total remaining balance, month by month

Month 1 is identical because both plans pay the same $700 and are charged the same interest; they only differ in which balance the $245 lands on. The gap then widens slowly as the snowball plan keeps more money sitting on the 24.99% card. Once both plans are down to the car loan alone, in month 22, the gap grows only by the 7.50% interest on the snowball plan's extra $96 of car loan, which is why it creeps from $95.57 to $101.10 between months 22 and 31.

How the budget changes the gap

Every $100 added to the monthly budget saves far more than the choice of method. Going from $700 to $800 saves $595.31 in avalanche interest, almost six times the avalanche advantage at $700. The method matters most when rates differ widely and the balances are large relative to the extra payment; with a small, high-rate debt sitting behind a large one, the snowball penalty can grow well beyond the figures here.

What does behavioral research say about the snowball method?

The case for the snowball method is motivational, and there is some evidence behind it. David Gal and Blakeley McShane, then at Northwestern University's Kellogg School of Management, analyzed data from a debt settlement firm in "Can Small Victories Help Win the War? Evidence from Consumer Debt Management" (Journal of Marketing Research, 2012). They found that closing individual debt accounts predicted whether clients eventually eliminated their debt, regardless of the dollar balances of the accounts closed, while the dollar balance of closed accounts did not predict success once the fraction of accounts closed was taken into account.

Two cautions apply. The study is observational and its subjects were debt settlement clients, so it shows an association, not proof that switching to snowball will make any given person succeed. And it does not change the arithmetic: avalanche still costs less if you follow it.

A reasonable reading is this. If you trust yourself to keep paying the fixed amount for the full term, use avalanche. If you have abandoned payoff plans before, the extra interest from snowball may be a fair price for early wins, and the table above lets you see exactly what that price is. A hybrid is also common: clear one very small balance first for the quick win, then switch to avalanche.

When does debt consolidation make sense for payoff?

A consolidation loan replaces several debts with one loan, ideally at a lower rate. It helps only if the new rate, including fees, is below the rates of the debts it replaces, and if you keep paying the same total.

Using the same example, suppose a lender offers 11.99% APR with a 3% origination fee added to the loan, and you keep paying $700 a month:

Folding the 7.50% car loan into an 11.99% loan makes things worse by $665.96. Consolidating only the two cards saves $811.07 against avalanche and finishes a month earlier. The rule: consolidate only debts whose rates are above the new loan's effective rate, and compare the total cost, not the monthly payment. A lower monthly payment over a longer term can cost more in total. Check also whether a card balance transfer offer has a transfer fee and what rate applies when the promotional period ends.

How do you set up a debt payoff comparison in a spreadsheet?

1. List each debt on its own row with balance, APR and minimum payment, and put the fixed monthly budget in one input cell.
2. Check each debt with =NPER(apr/12, -minimum, balance). A #NUM! means the minimum does not cover the interest.
3. Add a priority column: rank by APR for avalanche (=RANK(C2, $C$2:$C$4)), or by balance in ascending order for snowball (=RANK(B2, $B$2:$B$4, 1)).
4. Build a monthly schedule with one block per debt: opening balance, interest =ROUND(opening*apr/12, 2), payment, closing balance. The payment to the current target is its minimum plus whatever the budget has left after all other minimums, capped at the balance plus interest.
5. Sum interest across all months and note the month each balance reaches zero.
6. Change the budget cell and watch the debt-free month. That one number is the main thing you control.

The debt payoff planner has both orders built in and shows them side by side. For how a single fixed-rate loan splits each payment into interest and principal, see loan amortization with PMT, IPMT and PPMT.