DEV Community

Financely Templates
Financely Templates

Posted on

Build a Debt Snowball Tracker in Google Sheets (With a Payoff Date Formula)

#googlesheets #spreadsheets #tutorial #productivity

The debt snowball is simple: pay minimums on everything, then throw every extra dollar at the smallest balance. When it's gone, roll that payment into the next one. A spreadsheet makes it easy to see the finish line, and here's how to build one in about ten minutes.

Step 1: List your debts

Make a tab called Debts with these columns:

A: Debt B: Balance C: APR % D: Minimum E: Extra
Store card 420 24.99 25 150
Credit card 1,900 21.5 55 0
Car loan 6,300 6.9 210 0

Sort by balance, smallest first: select the rows and use Data > Sort range > Column B, A to Z. That order is your snowball.

Step 2: Months to pay off each debt

Google Sheets has NPER, which returns how many payments it takes to clear a balance at a given rate. In F2:

=IFERROR(NPER(C2/100/12, -(D2+E2), B2), "Payment too low")
Enter fullscreen mode Exit fullscreen mode

If the payment doesn't cover the monthly interest, you'll see "Payment too low" instead of a nonsense number, which is a useful warning on its own.

Step 3: A payoff date

In G2, turn months into a calendar date:

=IF(ISNUMBER(F2), EDATE(TODAY(), ROUNDUP(F2, 0)), "")
Enter fullscreen mode Exit fullscreen mode

Format column G as a date (Format > Number > Date).

Step 4: Roll the snowball

When the first debt is paid off, its whole payment moves to the next one. In E3, add the freed-up payment from the row above once that debt is cleared:

=IF(B2<=0, D2+E2, 0)
Enter fullscreen mode Exit fullscreen mode

Update balances once a month and the extra payment moves down the list by itself.

Step 5: Total interest you'll pay

In H2, estimate the total interest on each debt:

=IF(ISNUMBER(F2), ROUND(F2*(D2+E2) - B2, 2), "")
Enter fullscreen mode Exit fullscreen mode

Then =SUM(H2:H) at the bottom. Try bumping the extra payment by $25 and watch that total drop. That's usually the most motivating number in the whole sheet.

Step 6: A progress bar

Add your starting total debt in J1 and current total in J2 (=SUM(B2:B)), then:

=SPARKLINE(1-J2/J1, {"charttype","bar";"max",1;"color1","#2e7d32"})
Enter fullscreen mode Exit fullscreen mode

Watching that green bar grow helps a lot on the slow months.

Done

That's a working snowball tracker: payoff months, real dates, total interest, and a progress bar.

If you'd rather start from a finished sheet, I make templates as Financely Templates (disclosure: these are my products):

Top comments (0)