Building a Debt Payoff Tracker in Excel That Handles the Rollover
The hard part is not the interest. It is routing a freed-up minimum payment to the next debt automatically. Here is the sheet that does it, run on five real debts, where switching methods saves $104 and the extra $400 a month saves $6,992.
The Part That Is Actually Hard
Computing monthly interest on a balance is one multiplication. What makes a debt payoff tracker awkward is the rollover: when one debt clears, its minimum payment must join the pool attacking the next one, and the sheet has to work out which debt that is, every month, without you touching it.
Once that works, switching between the avalanche and snowball methods is a change to one ranking column. This article builds the sheet and then runs both methods on the same five debts.
Try the debt payoff calculatorCompare snowball and avalanche on your own debts before deciding whether to build the sheet.The debts
Swipe sideways to compare columns.
| Debt | Balance | APR | Minimum |
|---|---|---|---|
| Store card | $1,850 | 26.99% | $55 |
| Credit card A | $6,400 | 22.49% | $160 |
| Credit card B | $3,200 | 18.99% | $80 |
| Car loan | $11,500 | 7.25% | $342 |
| Student loan | $18,000 | 5.30% | $195 |
| Total | $40,950 | — | $832 |
Total minimums are $832. The budget is $1,232, so there is $400 a month of extra beyond the minimums. That $400 is the number the whole exercise turns on, as the results will show.
The ranking column, which is the whole method choice
Swipe sideways to compare columns.
| Debt | Avalanche rank | Snowball rank |
|---|---|---|
| Store card | 1 | 1 |
| Credit card A | 2 | 3 |
| Credit card B | 3 | 2 |
| Car loan | 4 | 4 |
| Student loan | 5 | 5 |
The monthly grid
Lay out one row per month and five blocks of four columns, one block per debt: opening balance, interest, payment, closing balance. Row 10 is month 1.
Swipe sideways to compare columns.
| Column | Formula |
|---|---|
| Opening | =Balance_StoreCard |
| Interest | =B10*APR_StoreCard/12 |
| Payment | the rollover formula below |
| Closing | =MAX(0, B10+C10-D10) |
Row 11 onward changes only the opening balance, which becomes the previous row's closing balance. The MAX in the closing column is what keeps a cleared debt at zero rather than going negative and quietly generating negative interest.
The rollover formula
A debt receives its minimum if it still has a balance, and it additionally receives the whole extra pool if it is the lowest-ranked debt still outstanding. Two helper cells per row make this readable.
What the sheet actually shows
Swipe sideways to compare columns.
| Scenario | Months to debt free | Total interest |
|---|---|---|
| Minimum payments only | 65 | $12,674 |
| Snowball, $400 extra | 38 | $5,786 |
| Avalanche, $400 extra | 38 | $5,682 |
What the methods do differ on is the order of small victories. Snowball clears the second debt in month 11; avalanche does not clear its second until month 20. If seeing an account close is what keeps the $400 going, snowball's $104 is a cheap price for that.
Swipe sideways to compare columns.
| Debt | Avalanche | Snowball |
|---|---|---|
| Store card | 5 | 5 |
| Credit card B | 20 | 11 |
| Credit card A | 16 | 20 |
| Car loan | 26 | 26 |
| Student loan | 38 | 38 |
The summary block
Swipe sideways to compare columns.
| Figure | Formula |
|---|---|
| Debt free in month | =MATCH(0, TotalClosingColumn, 0) |
| Total interest | =SUM(all interest columns) |
| Debt free date | =EDATE(StartDate, DebtFreeMonth) |
| Interest vs minimums only | =MinimumsOnlyInterest − TotalInterest |
| Next debt to attack | =INDEX(Names, MATCH(TargetRank, RankRange, 0)) |
The last row is the one that makes the sheet useful monthly rather than once. It names the debt to send the extra money to this month, which is the only decision the tracker exists to support.
Where these sheets go wrong
- Charging interest on the closing balance instead of the opening one. Interest accrues on what you owed at the start of the month.
- Forgetting the MAX on the closing balance. A negative balance generates negative interest, which looks like a bug elsewhere entirely.
- Not releasing the minimum when a debt clears. Without that, the pool stays flat, the payoff is months later than it should be, and the sheet quietly understates the plan.
- Hard-coding the target debt. It changes as debts clear; MINIFS on the rank column handles it and a typed name does not.
- Modelling a credit card minimum as fixed. Real minimums are usually a percentage of the balance with a floor, which falls as the balance does. A fixed minimum makes minimum-only scenarios look far better than they are.
- Ignoring new spending. A tracker that assumes no new charges on a card you are still using is describing a different plan from the one you are following.
What the tracker does not tell you
- Whether paying down debt is the right use of the money. A 5.30% student loan against an employer match you are not taking is not close; take the match first.
- Whether to consolidate or transfer a balance. A 0% transfer offer with a 3% fee changes the whole ranking, and the sheet has no way to know it exists.
- What happens if income stops. A plan with no emergency fund behind it converts one bad month into new debt at the highest rate available.
- Rate changes. Variable card rates move, and the sheet assumes the APR you typed holds for 38 months.
- The behavioural question, which is the one that decides most outcomes. A plan that saves $104 more and is abandoned in month nine is worse than one that finishes.
- Anything about credit scores. Utilisation, account age, and closures all respond to a payoff plan in ways no interest calculation captures.
How do I make the extra payment roll to the next debt automatically?
Compute the pool as the budget minus the sum of minimums for debts that still have a balance. When a debt clears, its minimum stops being subtracted, so the pool grows by exactly that amount. Then send the pool to whichever surviving debt has the best rank, found with MINIFS.
Is avalanche always better than snowball?
On interest, yes, by definition, since it always attacks the highest rate first. On these five debts the advantage is $104 across 38 months. Whether that outweighs the motivational value of clearing an account nine months sooner is a question about you, not about the arithmetic.
How do I switch between the two methods without rebuilding the sheet?
Put the method name in one cell, add data validation with the two options, and make the rank column an IF on that cell. Everything downstream reads the rank column, so the whole schedule recalculates from a dropdown.
Why does my final month leave a few cents outstanding?
Because the pool is allocated to one debt at a time and the last payment is capped at the balance. The leftover cents are real. Either add a final reallocation step or round the closing balance to two decimals and accept a rounding difference under a dollar.
Should I model minimum payments as fixed or as a percentage?
Percentage, if you want the minimum-only scenario to be honest. Most card minimums are around 1% to 2% of the balance plus interest, with a floor of $25 to $35. A declining minimum is exactly why minimum-only payoff takes so long, and a fixed minimum hides that.
How many months should the grid cover?
Build it long enough for the minimums-only case, which here is 65 months. Round up to 120 rows so the sheet still works if a rate rises or the extra payment stops. The guard formulas keep cleared rows at zero, so extra rows cost nothing but height.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team