How to Build a Debt Payoff Planner in a Spreadsheet

Disclosure: Seller Sheets Lab sells the paid workbook linked at the end. This tutorial is a standalone illustrative example, not a reproduction or test of that workbook.

A debt payoff planner answers three practical questions. Which balance do you attack first? How much interest accrues this month? When does a fixed payment clear a given balance in this simplified model?

You can answer those with a small spreadsheet and a handful of formulas. The layout below uses IF, COUNTIFS, SUM, ROUND, NPER, ROUNDUP and basic arithmetic. Syntax may vary by spreadsheet locale. The numbers are clearly marked sample data so you can check every formula against the arithmetic. This post does not claim a full product test inside Excel or Google Sheets, and it is not personalized financial advice.

Snowball vs avalanche (what the labels mean)

Two common ordering rules:

1. Snowball — smallest balance first. The idea is an early “cleared” row, then rolling that payment onto the next balance.
2. Avalanche — highest interest rate (APR) first. The idea is to reduce the costliest interest earlier in the plan.

Neither rule is universally “best” for every person or every set of debts. Fees, hardship programs, psychological fit, and lender terms can matter as much as APR. This tutorial only shows how to encode each rule in a sheet and how the sample arithmetic differs.

What you will build

One tab with:

1. A Debts log in columns A:F (name, balance, APR, minimum payment, payoff order, opening monthly interest)
2. A Controls block in H2:I4 (method, extra payment, first payment month label)
3. A Summary block in H6:I9 (total debt, sum of minimums, monthly debt budget, focused payment for the demo schedule)
4. A Focused schedule in columns K:O for one sample debt (month, opening balance, interest, payment, ending balance)

Keep the schedule away from the debts log so ranges never overlap.

Layout (so ranges do not overlap)

Sample data (examples only — replace with your numbers)

Use these rows only to verify the formulas. Delete them when you start your own plan.

Controls

Debts log (sample) — columns A:F

Enter APR as a decimal (0.229 for 22.9%), or enter 22.9% and format the cell as a percentage — both are 0.229 numerically. Type balances and minimums as positive numbers. Leave unused debt rows blank (not 0) if you want the order and interest cells to stay blank.

Step 1: Payoff order (snowball or avalanche)

In E2 (Credit card row):

=IF(N(B2)<=0,"",IF($I$2="Snowball",COUNTIFS($B$2:$B$4,"<"&B2,$B$2:$B$4,">0")+COUNTIFS($B$2:B2,B2),COUNTIFS($C$2:$C$4,">"&N(C2),$B$2:$B$4,">0")+COUNTIFS($C$2:C2,N(C2),$B$2:B2,">0"))) 

Copy down through E4.

Reading the formula:

• N(B2)<=0 leaves the cell blank when there is no positive balance
• Snowball counts how many smaller positive balances exist, then breaks ties by row order
• Avalanche counts how many higher APRs with a positive balance exist, then breaks ties by row order
• Order 1 is the first balance the rule would target

Expected order with the sample:

Check: snowball starts with the 1150 store card; avalanche starts with the 22.9% credit card.

Step 2: Opening monthly interest

Interest for one month in this simplified model is balance × APR ÷ 12.

In F2:

=IF(N(B2)<=0,"",ROUND(B2*C2/12,2)) 

Copy down through F4.

Expected results with the sample:

Check: 3200 × 0.229 ÷ 12 = 61.066… → 61.07 after ROUND(...,2).

Important: Column F is the interest on the opening balance for illustration. A real statement may use a different daily balance method, fees or promotional APR. Do not treat F as a lender quote.

Step 3: Summary totals

Put labels in H6:H9 and formulas in I6:I9:

I9 is only for the Store card demo schedule below (minimum 40 + extra). Change it if you focus a different row.

Expected results with the sample:

Empty debt ranges sum to 0. That is expected after you delete the sample rows and before you enter your own balances.

Step 4: Focused amortization schedule (one debt)

This schedule shows the Store card alone, paid with 190.00 per month (minimum 40 + extra 150). It demonstrates interest → payment → new balance. It does not by itself roll payments onto the other two debts; that multi-debt waterfall is summarized as verified sample arithmetic in Step 6.

Headers in K1:O1: Month #, Opening balance, Interest, Payment, Ending balance.

Starting opening balance in L2:

=1150 

(Or link =B3 if you keep the sample Store card in B3.)

Interest in M2:

=IF(N(L2)<=0,"",ROUND(L2*$C$3/12,2)) 

Payment in N2 (never pay more than balance + interest):

=IF(N(L2)<=0,"",MIN($I$9,L2+M2)) 

Ending balance in O2:

=IF(N(L2)<=0,"",ROUND(L2+M2-N2,2)) 

Month number in K2 = 1. For the next row, set:

K3 = IF(N(O2)<=0,"",K2+1) L3 = IF(N(O2)<=0,"",O2) 

Copy the M/N/O formulas down. Stop when ending balance is 0.

Expected schedule with the sample (payment 190.00, APR 18.5%):

Total interest in this focused demo: 66.88. Month 7 pays only what remains (76.88), not the full 190.00.

Step 5: Optional months estimate with NPER

For a single debt with a fixed payment that covers interest, spreadsheet NPER estimates the number of periods:

=IF(OR(N(B3)<=0,N($I$9)<=0),"",IF($I$9<=B3*$C$3/12,"never",ROUNDUP(NPER($C$3/12,$I$9,-B3),0))) 

Expected result with the sample Store card and payment 190: 7 (matches the schedule length).

If the payment is less than or equal to the first month's interest, the balance does not shrink in this model — the formula shows never instead of a fake payoff date. That is a signal to raise the payment or revisit the terms, not a prediction about any specific lender.

NPER assumes a constant rate and payment. It ignores fees, variable APRs, deferments and early-payoff rules.

Step 6: Worked sample — snowball vs avalanche on all three debts

The focused schedule above clears the Store card in isolation. For a full plan, each month you typically:

1. Accrue interest on every open balance
2. Pay every minimum
3. Send the extra (plus any freed minimums after a balance hits zero) to the debt with order 1, then 2, and so on

That multi-debt waterfall needs a longer schedule (or a dedicated planner). For this article, the sample was simulated with that exact rule set and rounding to cents each month with spreadsheet ROUND (half away from zero). Results for this sample only:

In this sample, both methods finish in 33 months; avalanche accrues 115.95 less interest. That difference is an arithmetic outcome of these inputs, not a guarantee for other balances, rates, fees or payment changes. If you change one minimum or the extra payment, re-run the schedule — do not reuse these totals.

A simple checklist

1. List every balance, APR and minimum as positive numbers.
2. Pick Snowball or Avalanche in I2 and confirm order 1 matches the rule you intended.
3. Set an extra payment you can actually sustain in your budget — this tutorial does not choose that number for you.
4. Build a focused schedule for the order-1 debt; when it hits zero, roll its payment into the next row's schedule.
5. Recalculate when a rate, fee or minimum changes.
6. Treat every payoff month as a model output, not a contractual due date.

Common mistakes

• Entering APR as 22.9 instead of 0.229 or 22.9% (interest becomes ~100× too large).
• Typing minimums as negative numbers while also subtracting them in a payment formula.
• Putting the amortization schedule inside columns A:F so SUM and COUNTIFS ranges break.
• Using NPER when the payment does not cover monthly interest (balance grows; use the never guard).
• Calling opening-balance interest in column F the same thing as a lender's statement interest.
• Reusing the Step 6 sample totals after changing inputs.
• Mixing several currencies in one balance column without a conversion step.

Want the workbook already built?

You can recreate everything above in a blank sheet.

If you want a ready-made annual setup instead, our 2027 Finance Planner includes a Debt Payoff tracker (up to 10 debts, snowball or avalanche, extra payment, payoff month and interest totals) plus monthly budget, paycheck planning, sinking funds, savings goals and net worth. One-time purchase, no subscription. This tutorial is not a test of that workbook. It is a separate product from this DIY example; use whichever fits your workflow.

This article is for personal finance education only. It is not financial, tax, legal, accounting or investment advice. Sample figures are illustrative and may not match any lender's interest method, fees or payoff quote. Review decisions that affect your debts with a qualified professional. Seller Sheets Lab is not affiliated with or endorsed by Google LLC, Microsoft Corporation, or any bank, card issuer or loan servicer.