Creating a Debt Payoff Tracker with Milestones

What a Debt Payoff Tracker Does (and Why Milestones Matter)

A debt payoff tracker is a spreadsheet that shows, month by month, how your balances change as you make payments. Unlike a simple list of debts and minimum payments, a tracker focuses on progress: it calculates remaining balances, estimates payoff dates, and highlights key “milestones” so you can see meaningful wins along the way.

Milestones are pre-defined progress markers such as “10% paid off,” “first card under $1,000,” or “halfway to debt-free.” They turn a long payoff journey into smaller, trackable achievements. In Excel, milestones work best when they are computed from your data (balances and starting amounts) and then surfaced visually with status labels, conditional formatting, and a compact dashboard section.

This chapter walks you through building a debt payoff tracker that supports multiple debts, tracks actual payments, calculates progress, and automatically flags milestones. You will end with a sheet you can update in minutes each month.

Plan the Workbook Structure

Use one workbook with three sheets:

• Debts: one row per debt (starting balance, interest rate, minimum payment, and payoff strategy notes).
• Payments: a monthly log of what you actually paid to each debt.
• Tracker: calculated balances over time, progress percentages, milestone checks, and a small dashboard.

Keeping inputs (Debts, Payments) separate from calculations (Tracker) makes the file easier to maintain and reduces formula errors.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Sheet 1: Build the Debts Table (Inputs)

1) Create the table columns

On the Debts sheet, create a table with these columns (one debt per row):

• Debt_ID (short unique code like CC1, AUTO, STUDENT)
• Lender (name)
• Type (credit card, loan, etc.)
• Start_Date (the month you begin tracking)
• Starting_Balance
• APR (annual percentage rate as a percentage)
• Min_Payment
• Target_Payment (optional: your planned monthly payment)
• Notes (optional)

Convert the range to an Excel Table (so you can use structured references). Name the table tblDebts.

2) Decide how you will handle interest

You have two practical options:

• Option A (simpler, recommended for a personal tracker): Track balances based on your statements. Each month you enter the ending balance (or you enter payment and interest separately). This is the most accurate and requires the least modeling.
• Option B (modeled interest): Estimate interest monthly using APR and the prior balance. This is useful for forecasting, but it will differ from lender calculations due to compounding methods, daily balance, and timing of payments.

This chapter supports both: you can model interest for forecasting and still override with actual statement balances if you want. The milestone logic works either way.

Sheet 2: Create a Payments Log (Actuals)

1) Set up the payments table

On the Payments sheet, create a table with these columns:

• Month (use the first day of each month, e.g., 2026-01-01)
• Debt_ID
• Payment_Amount
• Extra_Payment (optional; can be included in Payment_Amount if you prefer)
• Interest_Charged (optional, if you want to reconcile to statements)
• Ending_Balance_Statement (optional override)

Convert it to a table named tblPayments.

2) Make Month entry consistent

Consistency matters because you will summarize by month. Use a single date per month (commonly the first day). Format it as mmm yyyy so it reads like “Jan 2026” while still being a real date.

3) Data validation for Debt_ID (recommended)

To reduce typos, apply Data Validation to the Debt_ID column in tblPayments so it must match an ID from tblDebts[Debt_ID]. This prevents “CC1” vs “CCl” issues that break summaries.

Sheet 3: Build the Tracker (Balances Over Time)

The Tracker sheet will create a month-by-month grid: rows are debts, columns are months. Each cell calculates the ending balance for that debt in that month.

1) Create a month timeline across columns

In Tracker, set up a header row:

• Cell A2: label Debt_ID
• Cell B2 onward: months (Jan 2026, Feb 2026, etc.)

Enter the first month in B2 as a date (e.g., 2026-01-01). In C2, add one month. You can use:

=EDATE(B2,1)

Copy across for as many months as you want to track (12–36 months is typical).

2) List debts down rows

In column A starting at A3, list each Debt_ID from tblDebts. If you have Excel 365, you can spill the list with:

=SORT(tblDebts[Debt_ID])

If not, copy/paste the IDs manually.

3) Pull starting balance for the first month

In B3 (first debt, first month), you need an initial balance. If your first month in the tracker equals the debt’s Start_Date, use Starting_Balance; otherwise show blank until tracking begins.

Assuming B2 is the month header and A3 is the Debt_ID, you can use a lookup for Starting_Balance and Start_Date. If you have XLOOKUP:

=LET(id,$A3, m,B$2, startDate, XLOOKUP(id,tblDebts[Debt_ID],tblDebts[Start_Date]), startBal, XLOOKUP(id,tblDebts[Debt_ID],tblDebts[Starting_Balance]), IF(m=startDate,startBal,IF(m<startDate,"",0)))

This sets the starting balance in the first tracked month, blanks before tracking starts, and puts 0 after (you will replace that 0 with the rolling calculation in the next step). If you prefer, you can simplify by ensuring all debts share the same Start_Date and just place Starting_Balance in the first month.

4) Calculate ending balance each month (two approaches)

You will compute each month’s ending balance from the prior month’s balance, plus interest, minus payments. The key is to pull total payments for that debt and month from tblPayments.

Approach A: Use statement ending balance when available (most accurate)

In each month cell, first check if you entered an Ending_Balance_Statement in the Payments table. If yes, use it. If not, fall back to a modeled balance.

For example, in C3 (second month), you can calculate:

• Prior balance: B3
• Total payment: sum of Payment_Amount for that debt and month
• Interest: either sum Interest_Charged for that debt and month (if you record it) or estimate it

To pull totals, use SUMIFS. Example formulas (adapt ranges to your table columns):

=SUMIFS(tblPayments[Payment_Amount], tblPayments[Debt_ID], $A3, tblPayments[Month], C$2)

=SUMIFS(tblPayments[Interest_Charged], tblPayments[Debt_ID], $A3, tblPayments[Month], C$2)

To pull a statement ending balance override (if you enter it once per debt per month):

=XLOOKUP(1,(tblPayments[Debt_ID]=$A3)*(tblPayments[Month]=C$2),tblPayments[Ending_Balance_Statement],"",0)

Then combine logic:

=LET(prior,B3, pay, SUMIFS(tblPayments[Payment_Amount],tblPayments[Debt_ID],$A3,tblPayments[Month],C$2), int, SUMIFS(tblPayments[Interest_Charged],tblPayments[Debt_ID],$A3,tblPayments[Month],C$2), stmt, XLOOKUP(1,(tblPayments[Debt_ID]=$A3)*(tblPayments[Month]=C$2),tblPayments[Ending_Balance_Statement],"",0), IF(stmt<>"", stmt, MAX(0, prior + int - pay)))

Copy this across and down. The MAX(0, …) prevents negative balances when you overpay in the final month.

Approach B: Model interest from APR (good for forecasting)

If you don’t record Interest_Charged, estimate monthly interest as:

Monthly rate = APR / 12

Then:

Ending balance = MAX(0, PriorBalance * (1 + MonthlyRate) - Payment)

Example (assuming APR is stored in tblDebts[APR]):

=LET(id,$A3, prior,B3, apr, XLOOKUP(id,tblDebts[Debt_ID],tblDebts[APR]), rate, apr/12, pay, SUMIFS(tblPayments[Payment_Amount],tblPayments[Debt_ID],id,tblPayments[Month],C$2), MAX(0, prior*(1+rate) - pay))

Copy across and down.

Add Progress Metrics (Percent Paid and Remaining)

Milestones become easy when you compute progress consistently. Create a small metrics area to the right of your month grid (or above it) that calculates current balance, starting balance, and percent paid.

1) Current balance (latest month)

If your tracker spans a fixed set of months, “current balance” can be the last column. Suppose the last month is in column N. Then for each debt row:

=N3

If you want it to automatically use the latest month that has data, you can use a lookup for the last numeric value in the row. One common pattern is:

=LOOKUP(9.99999999999999E+307, B3:N3)

This returns the last numeric cell in the range.

2) Starting balance

Pull from tblDebts:

=XLOOKUP($A3,tblDebts[Debt_ID],tblDebts[Starting_Balance])

3) Percent paid

Percent paid is:

(StartingBalance - CurrentBalance) / StartingBalance

Use:

=IFERROR((StartBal-CurrentBal)/StartBal,0)

Format as percentage with 0–1 decimals.

4) Total debt progress (portfolio view)

In a dashboard section, compute totals across all debts:

• Total starting debt: sum of Starting_Balance
• Total current debt: sum of current balances
• Total percent paid: (total start - total current) / total start

These totals are useful for milestones like “25% debt-free overall.”

Define Milestones You Want to Track

Milestones should be specific, measurable, and visible in the sheet. Use a mix of portfolio milestones and per-debt milestones.

Examples of milestone types

• Portfolio percent milestones: 10%, 25%, 50%, 75%, 100% paid off.
• Balance thresholds: “Total debt under $10,000,” “Any single debt under $500.”
• Debt count milestones: “First debt paid off,” “Half of debts paid off.”
• Time-based milestones: “3 consecutive months of on-time payments” (requires additional tracking fields).

In this chapter, you will implement percent milestones and debt-paid-off milestones because they can be calculated directly from balances.

Create a Milestones Table (and Make It Automatic)

1) Build a milestones table

On the Tracker sheet (or a separate sheet), create a table named tblMilestones with columns:

• Milestone (text label, e.g., “25% Paid Off”)
• Type (PortfolioPercent, PortfolioBalance, DebtCount, PerDebtPercent)
• Target_Value (e.g., 0.25 for 25%)
• Status (formula: Achieved / Not yet)
• Achieved_Month (formula: month when first achieved)

Start with portfolio percent milestones: 10%, 25%, 50%, 75%, 100%.

2) Calculate portfolio percent paid each month

To find when a milestone was achieved, you need portfolio percent paid by month. Create a row below your grid called Total Balance that sums balances across debts for each month column.

If your debt balances are in B3:N20 (adjust to your range), then in B21:

=SUM(B3:B20)

Copy across to N21.

Then compute portfolio percent paid by month in another row, using total starting debt. Put total starting debt in a single cell, e.g., P2:

=SUM(tblDebts[Starting_Balance])

In B22 (Portfolio % Paid):

=IFERROR(($P$2 - B21)/$P$2,0)

Copy across.

3) Milestone status (achieved or not)

For a portfolio percent milestone, status is achieved if the latest portfolio percent paid is greater than or equal to the target. If your latest portfolio percent is in N22:

=IF($N$22 >= [@Target_Value],"Achieved","Not yet")

This makes the milestones table update as you add payments.

4) Find the month a milestone was first achieved

To return the first month where portfolio percent paid meets/exceeds the target, use MATCH against the portfolio percent row. Suppose months are in B2:N2 and portfolio percent paid is in B22:N22. Then:

=IFERROR(INDEX($B$2:$N$2, MATCH([@Target_Value], $B$22:$N$22, 1)), "")

This assumes the portfolio percent paid increases over time (generally true). If your percent could decrease (e.g., you add new debt), you may need a different approach, such as scanning for the first TRUE in a logical test. A robust pattern is:

=LET(t,[@Target_Value], hits, --($B$22:$N$22>=t), IFERROR(INDEX($B$2:$N$2, XMATCH(1,hits,0,1)),""))

XMATCH is available in newer Excel versions.

Per-Debt Milestones (Like “50% Paid on CC1”)

Per-debt milestones help when one large debt dominates your totals. You can create a small milestone panel next to each debt row.

1) Add per-debt percent paid

Next to each debt row, compute percent paid for that debt:

=IFERROR((StartingBalance - CurrentBalance)/StartingBalance,0)

Format as percentage.

2) Add milestone flags: 25%, 50%, 75%, Paid Off

Create four columns: 25%, 50%, 75%, Paid Off. Each column returns a check indicator (or text) when achieved.

Example for 50%:

=IF([@PercentPaid]>=0.5,"Yes","")

For Paid Off (balance equals 0):

=IF([@CurrentBalance]<=0,"Yes","")

If you want the month achieved for each debt milestone, use the same idea as portfolio milestones but referencing that debt’s row across months. For example, to find the first month where the debt balance is less than or equal to half the starting balance:

=LET(start,[@StartingBalance], rowBal, B3:N3, hits, --(rowBal<=start*0.5), IFERROR(INDEX($B$2:$N$2, XMATCH(1,hits,0,1)),""))

Milestones That Trigger on “Number of Debts Paid Off”

Another motivating milestone is “first debt paid off” and “half of debts paid off.” This requires counting how many current balances are zero.

1) Count paid-off debts

If current balances are in a column (e.g., CurrentBalance in Q3:Q20), then:

=COUNTIF(Q3:Q20,0)

Also compute total number of debts:

=COUNTA($A$3:$A$20)

Then you can define milestones such as:

• First debt paid off: paidOffCount >= 1
• Half paid off: paidOffCount >= totalDebts/2

Add these to tblMilestones as Type = DebtCount and Target_Value = 1 or 0.5 (for half). For half, compare paidOffCount/totalDebts.

Make Milestones Visually Obvious (Conditional Formatting)

Milestones work best when they are hard to miss. Use conditional formatting to highlight achieved items.

1) Highlight achieved milestones in tblMilestones

Select the Status column in the milestones table and add a rule:

• Format cells that contain: Text = “Achieved”
• Choose a fill color and bold font

2) Progress bars for percent paid

Select the PercentPaid column (portfolio or per-debt) and apply Conditional Formatting → Data Bars. This gives an instant sense of momentum.

3) Highlight debts close to payoff

Create a rule on CurrentBalance such as “Cell value less than 500” to highlight debts nearing zero. This creates a natural milestone like “under $500” without extra formulas.

Add a Simple Milestone Dashboard Panel

Create a compact panel at the top of the Tracker sheet with:

• Total Starting Debt
• Total Current Debt
• Portfolio % Paid
• Next Milestone (the smallest target not yet achieved)
• Amount to Next Milestone (how much balance reduction needed)

1) Identify the next portfolio percent milestone

If your milestones table contains only portfolio percent milestones in one section, you can find the next target by taking the minimum Target_Value where Status is “Not yet.” With dynamic arrays:

=MIN(FILTER(tblMilestones[Target_Value], tblMilestones[Status]="Not yet"))

Then display it as a percentage and label it with a lookup to the Milestone text.

2) Compute amount remaining to reach next milestone

If total starting debt is StartTotal and next target percent is NextPct, then the target remaining balance is:

TargetRemaining = StartTotal * (1 - NextPct)

Amount to go:

AmountToGo = CurrentTotal - TargetRemaining

In Excel terms:

=MAX(0, CurrentTotal - (StartTotal*(1-NextPct)))

This tells you how much more principal reduction is needed (based on balances) to hit the next milestone.

Monthly Update Routine (So the Tracker Stays Easy)

The tracker is only helpful if updating it is quick. Use this routine once per month:

• Enter one row per debt in tblPayments for that month (Month, Debt_ID, Payment_Amount). Optionally enter Interest_Charged and Ending_Balance_Statement from your statements.
• Check the Tracker sheet: balances should update across the month column.
• Review the Milestones table: new achievements should flip to “Achieved” and show the Achieved_Month.
• Scan the “Next Milestone” panel to see what you are aiming for next month.

If you prefer fewer entries, you can log only payments and rely on modeled interest for forecasting; then once a quarter, enter statement ending balances to re-align the model.

Practical Example (Two Debts, One Extra Payment)

Assume you have:

• CC1: Starting_Balance $3,200, APR 22%, Min_Payment $80
• AUTO: Starting_Balance $9,500, APR 6%, Min_Payment $220

You decide to pay an extra $120 toward CC1 each month. In tblPayments for Jan 2026 you enter:

• Month: Jan 2026, Debt_ID: CC1, Payment_Amount: 200
• Month: Jan 2026, Debt_ID: AUTO, Payment_Amount: 220

Your Tracker calculates ending balances for Jan and Feb as you fill months. Your portfolio percent paid row increases each month. When it crosses 10%, the “10% Paid Off” milestone flips to Achieved and records the month it happened. Separately, CC1 might hit “50% paid” earlier than the portfolio does, giving you a quick win even while AUTO remains large.

By designing milestones at both the portfolio and per-debt level, you get frequent progress signals without needing to manually mark achievements.