# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-debt-payoff-tracker
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 12 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the debt payoff calculator](https://dothecalculation.com/calculators/debt-payoff-calculator) — Compare snowball and avalanche on your own debts before deciding whether to build the sheet.

## The debts

**The input table, rows 2 to 6**
| 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

**Two rankings, one cell each**

```
Avalanche: =RANK(C2, $C$2:$C$6)    ·    Snowball: =RANK(B2, $B$2:$B$6, 1)
```
- Avalanche ranks by APR descending, so rank 1 is the most expensive debt.
- Snowball ranks by balance ascending, so rank 1 is the smallest debt.
- Put a method selector in a cell and switch with =IF(Method="Avalanche", RANK(C2,$C$2:$C$6), RANK(B2,$B$2:$B$6,1)).

**How the two rankings differ**
| 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.

**The four columns for one debt, month 1 in row 10**
| 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.

**The pool, and the target**

```
Pool = Budget − SUMIF(balances, ">0", minimums)    ·    Target rank = MINIFS(RankRange, OpeningBalances, ">0")
```
- The pool is whatever the budget has left after every surviving debt takes its minimum.
- MINIFS finds the best rank among debts that still have a balance, which is the target.
- As debts clear, their minimums stop being subtracted, so the pool grows automatically. That is the rollover.

**The payment for one debt**

```
=MIN(B10+C10, IF(B10<=0, 0, Min_StoreCard + IF(Rank_StoreCard=TargetRank, Pool, 0)))
```
- MIN against opening plus interest stops the final payment overshooting the payoff amount.
- The IF on B10 stops a cleared debt drawing a payment.
- Any overshoot left in the pool is a rounding artefact of a few cents; a cleaner version subtracts the actual payment and reallocates, which matters only in the final month.

> **Use MINIFS, not a sort** — The instinct is to reorder the debt table so the target is always first. Do not. Sorting breaks every formula that references a specific debt by position, and it makes the sheet impossible to audit. Leave the rows fixed and let MINIFS find the target.

## What the sheet actually shows

**The same five debts, three scenarios**
| 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 |

> **The finding most articles bury** — Avalanche beats snowball by $104 and finishes in the same month. The extra $400 a month beats doing nothing by $6,992 and 27 months. The method argument is worth about 1.5% of what the extra payment is worth, and it is the argument that gets all the attention.

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.

**Payoff month by debt**
| 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 |

Tool: [Try the credit card payoff calculator](https://dothecalculation.com/calculators/credit-card-payoff-calculator) — Model a single card in isolation to see what a change in payment does to the payoff date.

## The summary block

**Put these above the grid**
| 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-debt-payoff-tracker). Quote freely with attribution and a link to this page._