Debt Payoff Calculator in Excel (Snowball Method)

A debt payoff calculator in Excel shows, month by month, how each balance shrinks as you pay it down, and when you will be debt-free. You need only a few ideas: monthly interest with ROUND, a balance that carries from row to row, locked input cells, and the snowball rule for which debt to pay first. This guide builds the calculator step by step with three example debts, and every number shown is one you can reproduce.

How a debt payoff calculator works#

Every month, each debt does the same three things: interest is added, a payment is taken off, and what is left carries over to the next month.

End balance = Start balance + Interest - Payment

The snowball method decides where the money goes. You pay the minimum on every debt, then put every extra amount on the smallest balance. When that debt hits zero, its whole payment moves to the next smallest, so the payment grows each time a debt is cleared.

This is a calculation tool, not financial advice. Lenders work out interest in different ways (some use daily balances), so your statements may differ by a few cents.

Step 1: List your debts and budget#

Start with one row per debt and one cell for the total you can pay each month.

APR is the yearly interest rate. Type it as 24% so Excel stores 0.24. The extra in B7 is what is left after the minimums:

=B6-SUM(D2:D4)

That is 400 - 265 = 135. If this number is negative, the budget doesn't cover the minimums, and the calculator can't work until it does. A monthly budget in Excel helps you find a realistic figure for B6.

Step 2: Put the debts in snowball order#

The snowball order is smallest balance first. Sort the table by Balance (Data > Sort, smallest to largest), or show the order with a formula in Excel 2021 or Microsoft 365:

=SORTBY(A2:A4,B2:B4,1)

This spills Store card, Credit card, Car loan. The avalanche method orders by the highest APR instead: =SORTBY(A2:A4,C2:C4,-1). In this example the order happens to be the same, because the smallest debt also has the highest rate. With real debts the two orders often differ, and avalanche usually costs less interest in total. The rest of the calculator works the same either way.

Step 3: Build the monthly schedule for the first debt#

Put the schedule to the right of the inputs, in F1:J5, with one row per month. Here are the formulas for the first row:

Fill rows 3 onwards down (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep C2, D2 and B7 fixed while the balance references move down one row at a time. Press F4 (Mac: ⌘T) after clicking a reference to add them. The absolute references lesson practises exactly this.

The store card pays 25 + 135 = 160 a month:

In month 4, only 150.00 is owed, so MIN pays 150.00 instead of 160. Without MIN, the balance would go to -10.00.

Step 4: Round interest correctly#

Interest should be rounded each month, because that is how a statement shows it. In month 2, 452 × 24% ÷ 12 is exactly 9.04, but in month 3, 301.04 × 24% ÷ 12 is 6.0208. ROUND turns that into 6.02:

=ROUND(G4*$C$2/12,2)

The 2 means two decimal places. If you only format the cell to show two decimals, Excel still stores 6.0208 and carries the hidden fractions into every later month, so your final balance drifts away from the statement by a few cents. The ROUND function guide and the rounding lesson explain the difference between rounding a value and formatting it.

Step 5: Roll the snowball onto the next debt#

When a debt is paid off, its payment moves to the next one. A simple way to build that is to give each debt a payment column with this rule:

Payment to a debt = the whole budget, minus what the other debts are being paid that month, but never more than is owed.

• The debt you are focusing on gets everything left after the minimums on the others.
• The other debts get only their minimums.
• Once a debt's balance is zero, its payment is zero, so the whole budget flows to the remaining debts.

Run through the example and you get these results:

In month 4, the store card needs only 150.00, so the spare 10.00 goes to the credit card that month (70.00 instead of 60). The total interest is 1,028.92. To see when month 27 falls, add the months to the start date with EDATE: if month 1 is January 2027, =EDATE(DATE(2027,1,1),26) returns 1 March 2029.

How to check your calculator#

A few checks catch most mistakes:

• Month 1 by hand. The car loan's first month is 6,500 + 37.92 - 180 = 6,357.92. If your sheet says something else, check the /12 and the $ signs.
• Total payments. Starting balances plus total interest should equal everything you paid. Here, 9,500 + 1,028.92 = 10,528.92.
• No negative balances. If a balance goes below zero, a MIN is missing.
• Budget always used. Each month's payments should add up to 400, except the very last month.

For a single loan with a fixed payment, an amortization schedule is the better tool, and the PMT function works out the payment that clears a loan in a set number of months. Once the debts are gone, the same row-by-row idea runs in reverse in a savings goal calculator.

Practise it#

Try the exercise on this page: it is the car loan's month-1 balance, and ROUND makes the answer match a real statement. Then practise absolute references in a live spreadsheet, so your schedules fill down without broken references, and work through the budget math practice lesson to get fast at the arithmetic behind any money sheet.