sheetsmith

Templates › Household money

Debt Payoff Tracker

Made for people clearing cards and loans.

A workbook for clearing debts: what you owe on each one, every payment you make, and a summary showing the total left, the interest it is costing you and which debt to attack first. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
23
Fill themselves in
28

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 640 rows.

Debts

One row for each debt you are clearing. Fill in the opening balance, the rate and the minimum payment, and the rest keeps itself up to date from the Payments tab. Room for 40 rows.

ColumnTypeWorked out as
Debt Nametext
Lendertext
Debt Typechoice (list)
Opening Balancecurrency
Interest Ratepercent
Minimum Paymentcurrency
Paid To DatecurrencyIF(ISBLANK([debt_name]), "", SUMIF([Payments!debt], [debt_name], [Payments!amount]))
Interest PaidcurrencyIF(ISBLANK([debt_name]), "", SUMIF([Payments!debt], [debt_name], [Payments!interest_portion]))
Balance NowcurrencyIF(ISBLANK([debt_name]), "", ROUND(MAX([opening_balance] - ([paid_to_date] - [interest_paid]), 0), 2))
Interest Per MonthcurrencyIF(ISBLANK([debt_name]), "", ROUND([current_balance] * [interest_rate] / 12, 2))
Percent ClearedpercentIF(ISBLANK([debt_name]), "", IFERROR(([opening_balance] - [current_balance]) / [opening_balance], 0))
Months To ClearnumberIF(ISBLANK([debt_name]), "", IF([current_balance] <= 0, 0, IFERROR(ROUNDUP([current_balance] / MAX([min_payment] - [monthly_interest], 0), 0), "")))
Rate While OpenpercentIF(ISBLANK([debt_name]), "", IF([current_balance] > 0, [interest_rate], ""))
Balance While OpencurrencyIF(ISBLANK([debt_name]), "", IF([current_balance] > 0, [current_balance], ""))
Notestext

Payments

One row for every payment you make. Pick the debt from the list, and put in the interest part from your statement when you have it. Room for 600 rows.

ColumnTypeWorked out as
Date Paiddate
Debtchoice (from Debts › debt_name)
Amount Paidcurrency
Interest Partcurrency
Off The BalancecurrencyIF(ISBLANK([amount]), "", ROUND([amount] - IF(ISBLANK([interest_portion]), 0, [interest_portion]), 2))
Payment Typechoice (list)
Methodchoice (list)
Notetext

Summary

Where you stand today: what is left, what the interest is costing you, and the debt to put every spare pound against.

MeasureWorked out as
Total Still OwedSUM([Debts!current_balance])
Total You Started WithSUM([Debts!opening_balance])
Knocked Off The BalancesSUM([Debts!opening_balance]) - SUM([Debts!current_balance])
Percent Of The Debt ClearedIFERROR((SUM([Debts!opening_balance]) - SUM([Debts!current_balance])) / SUM([Debts!opening_balance]), 0)
Total Paid In So FarSUM([Payments!amount])
Interest Paid So FarSUM([Payments!interest_portion])
Interest Building Up Each MonthSUM([Debts!monthly_interest])
Interest As Part Of Everything PaidIFERROR(SUM([Payments!interest_portion]) / SUM([Payments!amount]), 0)
Minimum Payments Due Each MonthSUM([Debts!min_payment])
Paid This MonthIFERROR(SUMIFS([Payments!amount], [Payments!payment_date], ">=" & (EOMONTH(TODAY(), -1) + 1), [Payments!payment_date], "<=" & EOMONTH(TODAY(), 0)), 0)
Paid This YearIFERROR(SUMIFS([Payments!amount], [Payments!payment_date], ">=" & DATE(YEAR(TODAY()), 1, 1), [Payments!payment_date], "<=" & TODAY()), 0)
Debts Still OpenCOUNTIF([Debts!current_balance], ">0")
Debts ClearedCOUNTA([Debts!debt_name]) - COUNTIF([Debts!current_balance], ">0")
Attack This Debt FirstIFERROR(IF(MAX([Debts!active_rate]) <= 0, "Nothing outstanding", INDEX([Debts!debt_name], MATCH(MAX([Debts!active_rate]), [Debts!active_rate], 0))), "Add your debts to begin")
Its Interest RateIFERROR(MAX([Debts!active_rate]), 0)
Quickest Win To CloseIFERROR(IF(COUNT([Debts!active_balance]) = 0, "Nothing outstanding", INDEX([Debts!debt_name], MATCH(MIN([Debts!active_balance]), [Debts!active_balance], 0))), "Add your debts to begin")
Balance On That OneIFERROR(MIN([Debts!active_balance]), 0)
Months Left At Minimum PaymentsIFERROR(MAX([Debts!months_to_clear]), 0)
Debt Free AroundIFERROR(EDATE(TODAY(), MAX([Debts!months_to_clear])), TODAY())