Most debt payoff advice assumes you already know exactly what you owe. In reality, a lot of people have a rough idea but no clean summary in one place. NerdWallet’s debt payoff guide emphasizes that building a complete debt inventory is the critical first step before any strategy can work. They have a credit card balance they check occasionally, a student loan login they dread opening, and maybe a personal loan statement buried in their email. Getting it all in one view is step zero, and it is also where most people give up before they start.
Microsoft Copilot, built into Excel and Microsoft 365, can build a working debt tracker for you in minutes using plain-language prompts. You do not need to know formulas. You do not need to design anything from scratch. You describe what you want, Copilot builds it, and you fill in the numbers.
This guide walks through exactly how to do that, including the prompts that work, the columns your tracker needs, and how to use Copilot to add payoff projections once your data is in place.
What You Need Before You Start
You will need access to Microsoft Copilot inside Excel or via Microsoft 365. Copilot is available on Microsoft 365 Personal, Family, and business plans. If you have an Office subscription, check whether Copilot is included in your plan at microsoft.com/copilot.
You will also want to gather your debt information before you sit down to build the tracker. For each debt you carry, you need: the creditor name, current balance, interest rate (APR), minimum monthly payment, and payment due date. That information is available on your most recent statement or by logging into each account online. It takes 15 to 20 minutes to collect if you have not done it before, and it is worth doing once and keeping the spreadsheet current going forward.
Step 1: Open a New Excel Workbook and Launch Copilot
Open Excel and create a new blank workbook. In the Home tab, click the Copilot button in the upper right of the ribbon. A sidebar panel opens where you can type prompts in plain English.
If you are using Excel Online inside a browser, the Copilot button appears in the same location once you have signed into your Microsoft account. The experience is nearly identical to the desktop version for this use case.
Step 2: Prompt Copilot to Build Your Tracker Structure
Start with a structural prompt that describes what you want the spreadsheet to contain. Be specific about the columns so Copilot does not guess.
Create a debt tracker table with the following columns: Creditor Name, Debt Type (credit card / student loan / auto / personal loan / medical), Current Balance, Interest Rate (APR), Minimum Monthly Payment, Extra Payment, Total Monthly Payment, Due Date, and a Notes column. Format it as an Excel table with alternating row colors and bold headers. Leave 10 blank rows for data entry.
Copilot will generate the table and insert it into your worksheet. The Total Monthly Payment column should automatically sum Minimum Monthly Payment and Extra Payment for each row. If it does not, you can ask: “Add a formula to the Total Monthly Payment column that sums Minimum Monthly Payment and Extra Payment for each row.”
Step 3: Add a Summary Section
After your data table is set up and populated with your debts, use Copilot to add a summary section that gives you the key numbers at a glance.
Add a summary section above the table that shows: Total Debt Balance (sum of all current balances), Total Minimum Payments Per Month (sum of minimum payments), Total Extra Payments Per Month (sum of extra payments), Total Monthly Payment Committed, and Highest Interest Rate Debt (name and APR of the debt with the highest interest rate). Format these as labeled cells with bold labels.
The summary section turns your raw list into a dashboard. The “Highest Interest Rate Debt” row is particularly useful: it shows you at a glance which debt to attack first if you are using the debt avalanche method, where you direct extra payments toward the highest-rate balance first to minimize total interest paid.
Step 4: Add a Payoff Timeline Projection
Once your balances and payments are in the tracker, Copilot can build a simple payoff projection for each debt based on your current payment amounts. This is where the spreadsheet goes from informational to genuinely useful for planning.
Add a new column called “Months to Payoff” that estimates how many months it will take to pay off each debt based on the Current Balance, Interest Rate, and Total Monthly Payment using the standard loan payoff formula. Round to the nearest whole month.
Copilot will insert an NPER formula or equivalent calculation. The NPER function in Excel calculates the number of periods needed to pay off a loan: =NPER(rate/12, -payment, balance). If the formula is wrong, you can ask Copilot to correct it: “The months to payoff formula shows an error for rows where the monthly payment equals the minimum. Fix the formula to handle cases where extra payment is zero.”
You can also ask Copilot to add a “Payoff Date” column that converts months to an actual calendar date: “Add a Payoff Date column that calculates the estimated payoff date by adding Months to Payoff to today’s date.”
Step 5: Add Conditional Formatting to Flag High-Risk Debts
Ask Copilot to highlight debts that need the most attention using conditional formatting rules.
Add conditional formatting to the Interest Rate column: highlight any rate above 20% in red, rates between 15% and 20% in orange, and rates below 15% in green. Also highlight the Current Balance cell in red for any debt over $5,000.
Color-coding high-interest and high-balance debts makes it immediately obvious where the financial damage is concentrated. This matters because many people have a dozen debts and the visual weight of seeing them all listed equally can make it hard to know where to focus. The color coding forces a priority order without requiring you to analyze the numbers every time you open the file.
This connects directly to what our guide on how to prioritize which debts to pay first covers: the decision framework for ordering your payoff sequence once you have the full picture in front of you.
Step 6: Track Monthly Progress
A debt tracker is only valuable if you update it. Ask Copilot to add a second sheet for monthly snapshots so you can see progress over time.
Add a second sheet called Monthly Progress. Create a table with columns: Month, Total Debt Balance, Total Minimum Payments, Total Extra Payments, and Net Debt Reduction That Month. Leave 24 rows for two years of tracking. Add a simple line chart below the table that shows Total Debt Balance over time.
The line chart is motivational in a way that raw numbers are not. Watching the curve trend downward over 12 to 18 months makes the payoff feel real and concrete rather than abstract. Updating one row per month takes about five minutes.
The CFPB’s money coach directory can connect you with a free or low-cost credit counselor who can review your tracker and help you build a realistic monthly extra-payment plan based on your budget.
Copilot’s Limits: What to Watch For
Copilot is strong at structure and formulas; it is less reliable when your prompts are ambiguous. If a formula returns wrong numbers, describe the expected behavior to Copilot and ask it to fix the logic. Most formula errors can be corrected in one or two follow-up prompts.
Also: Copilot works on the data you enter. If your balances or rates are wrong, your projections will be wrong. Pull fresh statements before building the tracker rather than going from memory. Even small errors in a balance or rate compound significantly over a multi-year payoff timeline.
Disclaimer: AI tools are not licensed financial advisors. Use these prompts as a starting point and verify important information with a certified credit counselor or attorney.
A debt tracker built with Copilot does not pay off your debt for you. But it eliminates the most common excuse for not starting: not knowing where you stand. Once everything is visible and the math is right in front of you, the next step becomes obvious. You know the total, you know the worst offenders, and you know how long it takes if you hold the current course versus what happens if you add $100 or $200 extra each month. That clarity is the whole point.
For a complete playbook on where to go next once your tracker is built, our zero-based budgeting guide shows you how to free up the extra payment money inside your current income without cutting everything you enjoy.