How to Create a Debt Payoff Tracker in Excel or Google Sheets
Share
To create a debt payoff tracker in Excel or Google Sheets, list each debt with its balance, interest rate and monthly payment. Then use =NPER(rate/12,-payment,balance) to get the months to pay off, and payment × months − balance to get the total interest. Sort by smallest balance for the snowball method or highest rate for the avalanche method.
Want it ready-made? The budget spreadsheet with debt payoff tracker has these formulas built in and works in both Excel and Google Sheets.
How do you set up the spreadsheet?
Create these column headings in row 1:
- A: Debt name
- B: Balance
- C: Interest rate (APR), typed as a percentage such as 19.99%
- D: Monthly payment
- E: Months to pay off
- F: Total interest
- G: Payoff date
What formulas do you need?
Type these in row 2, then copy them down for each debt:
-
Months to pay off (E2):
=NPER(C2/12,-D2,B2) -
Total interest (F2):
=D2*E2-B2 -
Payoff date (G2):
=EDATE(TODAY(),ROUNDUP(E2,0)), formatted as a date
If you see an error in E2, the payment is too low to cover the monthly interest, so the debt would never clear. The same formulas work in Excel and Google Sheets.
What does a worked example look like?
Take a credit card with a balance of 5,000 at 19.99% APR:
- Paying 200 a month: about 33 months, with about 1,521 in interest.
- Paying 300 a month: about 20 months, with about 906 in interest.
That's an extra 100 a month, saving about 13 months and 615 in interest. These are estimates that assume a fixed rate, a fixed payment and no new charges. Your statement may differ.
Snowball or avalanche: which order should you use?
-
Debt snowball: pay the minimum on everything and put extra money on the smallest balance first. Each cleared debt can feel like a quick win. In Excel:
=RANK(B2,$B$2:$B$10,1) -
Debt avalanche: put extra money on the highest interest rate first. This usually costs less interest overall. In Excel:
=RANK(C2,$C$2:$C$10,0)
Pick the one you'll stick with. When a debt clears, roll its payment onto the next one.
How do you keep the tracker going?
- Pick a day each month to update your balances.
- Add any extra payments, and watch the payoff date move.
- Keep it next to your budget so you know what you can put towards debt. A printable budget planner works if you prefer paper.
Paying off student loans? The student budget spreadsheet has a student loan and debt payoff tab next to a semester budget. See also how to make a student budget.
This article is general information, not financial advice. Loan terms differ, so check with your lender.
Ready-made debt and budget tools
- Student Budget Spreadsheet — semester budget with a student loan payoff tab
- Budget Planner Spreadsheet — monthly budget, bills, debt payoff and savings goals
- Printable Budget & Money Planner — budget and debt pages on paper
- Money & Side Hustle Mega Bundle — money planners and spreadsheets in one download
FAQ
How do I track debt payoff in Excel?
List each debt with its balance, APR and payment, then use =NPER(APR/12,-payment,balance) for months to pay off and payment × months − balance for total interest.
Does this work in Google Sheets?
Yes. NPER, EDATE, RANK and ROUNDUP all work the same way in Google Sheets.
Why does my NPER formula show an error?
Usually the payment is lower than the monthly interest. Increase the payment, and check that the rate is typed as a percentage.
Is the snowball or avalanche method better?
Avalanche usually costs less interest, while snowball clears individual debts sooner. The best one is the one you'll keep doing.