How to Set Up a Multi-Debt Payoff Schedule in a Spreadsheet

If you are the kind of person who would rather build a model than trust a black box, this is how you set up a debt payoff schedule in a spreadsheet so you understand exactly what the math is doing.

The end state is a sheet where you enter your debts, an APR, a minimum, a total monthly payment, and a strategy, and you get back the month-by-month schedule that shows when each debt clears.

This walkthrough uses Google Sheets, but every formula works identically in Excel or LibreOffice Calc.

Photo by Tima Miroshnichenko on Pexels

The data layout

Start with a small table at the top of the sheet for your inputs. Five columns:

Debt	Balance	APR	Minimum	Strategy Rank
Card A	4200	24.99	95	1
Card B	1800	19.99	45	2
Car loan	8400	6.49	285	3

The "Strategy Rank" column is what makes this flexible. For avalanche, you rank by APR descending (highest APR is rank 1). For snowball, you rank by balance ascending (smallest balance is rank 1). You can re-rank in 30 seconds to compare strategies.

Add one cell somewhere obvious for total monthly payment. Call it TotalPayment. Add another for extra payment above minimums; call it ExtraPayment. These two are the levers you'll play with.

The monthly schedule

Below the input table, build a schedule. Each row is a month. Each debt gets three columns: starting balance, payment made, ending balance.

For month 1, the starting balance of each debt is the input balance. The payment for the rank-1 debt is its minimum plus the entire ExtraPayment. The payment for every other debt is just its minimum.

Interest for the month accrues on the starting balance. The formula is:

interest = starting_balance * (APR / 12 / 100)

The ending balance is:

ending_balance = starting_balance + interest - payment

If ending_balance goes negative or hits zero, the debt is paid off this month. The "leftover" payment (the amount you tried to pay over what was owed) needs to roll onto the next debt.

For month 2, the starting balance of each debt equals the prior month's ending balance. The payment logic is the same as month 1, but: if any debt cleared in month 1, the rank-1 debt this month is the next-ranked surviving debt, and its payment now equals its minimum plus ExtraPayment plus the cleared debt's old minimum.

The roll-up formula that does the work

The roll-up is the entire reason a payoff strategy beats paying minimums forever. The formula in spreadsheet form is:

target_payment_this_month =
  this_debt_minimum
  + ExtraPayment
  + SUM(minimums of all cleared lower-ranked debts)

The SUM(minimums of all cleared...) term is what most homebrew spreadsheets get wrong. They handle the first debt clearing fine, then forget to roll up when the second debt clears. You can implement it with a SUMIFS that checks which debts have ending_balance = 0 in the prior month.

A safer pattern: maintain a small helper column per month showing which debt is currently being targeted, and compute the target payment from that. It's more verbose but easier to debug when month 17 looks weird.

Sanity checks before you trust the output

Three checks I run on every schedule I build:

Total of all payments across all months should equal total of all original balances plus total interest paid. If it doesn't, money is being created or destroyed somewhere.

The month any debt clears should match the month the running balance hits zero on the per-debt running total. If the schedule says debt A clears in month 14 but the per-debt running balance is still $230 in month 14, the roll-up trigger is off by a month.

Switching strategy ranks should change the total interest but not the total principal paid. Principal is principal regardless of order. If switching from avalanche to snowball changes the total principal paid, there's a formula bug.

These three checks catch about 90% of the mistakes I see in homebrew payoff sheets.

Handling variable APRs

If you want to model a future APR change (you expect the Fed to hike, or you're modeling what a balance transfer does), add an APR column per debt per month rather than a single APR per debt. The interest formula then reads the row's APR rather than the input table's APR.

This is overkill for most plans, but useful if you want to stress-test how much rate sensitivity your schedule actually has. The Federal Reserve publishes quarterly consumer credit summaries that are a decent benchmark for what "realistic" rate movement looks like quarter to quarter.

Adding lump sums and snowflakes

Two extra columns make this model substantially more useful for real life.

Lump-sum column. A monthly column where you can enter any one-off windfalls (tax refund, bonus, gift). The schedule adds the lump-sum amount to that month's target-debt payment. Suddenly you can model "what if I throw the $2,400 tax refund at the highest-APR card in April."

Snowflake column. Smaller irregular payments that don't deserve to be a "lump sum" but should still be tracked. Reselling something for $40, a small side gig payday, a rebate check. These add up; without modeling them, your schedule is pessimistic versus what you'd actually achieve.

Both are simple SUM additions to the target-debt payment line. The schedule recomputes downstream months automatically.

Where the model is honest about its limits

A few things a spreadsheet (or any planner) cannot model precisely.

Missed payments. The schedule assumes every payment lands. If you miss one, the late fee and the interest catch-up create a downstream effect the model won't show until you re-enter the actual current balance.

Credit limit decreases. If you have a credit card that's part of the plan and the issuer drops your limit such that your balance is now over-limit, you might have an over-limit fee or a credit-utilization hit. The model doesn't know about either.

APR resets. Promotional APR ends (a balance transfer's 0% period rolls off), or a deferred-interest medical financing plan crosses the deferral boundary. The model treats APRs as flat unless you build the change in explicitly.

These are the moments where you re-pull current balances and re-run from scratch.

Validating against a battle-tested tool

Before you trust your spreadsheet, validate the numbers against an established calculator. The free debt payoff planner by EvvyTools takes the same inputs (balances, APRs, minimums, total payment, strategy) and outputs the same schedule shape. If your sheet's debt-free date and total interest match the planner's output to within a few dollars, your formulas are right. If they're off by hundreds, hunt the bug.

For the broader walkthrough of how to think about debt payoff planning generally, the longer guide is at How to Build a Debt Payoff Plan That Hits a Real Date.

Engineers who want even more rigor can build the same model in Python with pandas and a small loop. The math is identical; the speed is irrelevant for a 60-month schedule with 5 debts. Use whichever tool you'll actually look at every quarter.

The Consumer Financial Protection Bureau publishes reference rules for how credit card minimums are calculated by issuer category, which is useful if you want your minimum formulas to be more realistic than a flat percentage. For the underlying interest math, Khan Academy's compound interest section is the cleanest free walkthrough I know.

Why bother building it yourself

Three reasons engineers I know give for going the spreadsheet route over a hosted calculator:

1. You learn what assumptions the calculator is making, which makes you a better consumer of the calculator.
2. You can model your specific scenario (lumpy income, planned balance transfers, expected raises) more flexibly than most online tools allow.
3. You actually look at it again because you built it. The schedule becomes a thing you maintain, not a printout you lose.

For most people, a hosted planner is the better answer because they will never re-open the spreadsheet. Pick whichever you'll actually use in 90 days.

You can browse the rest of the EvvyTools finance tools if you want a parallel one already built for adjacent questions (emergency fund sizing, mortgage affordability, retirement contribution impact). They use the same model logic this spreadsheet would.