sheetsmith

Templates › Household money

Bill Payment Tracker

Made for households with a lot of direct debits.

A list of every regular bill, a monthly tick off log for each payment, and a summary showing what is still to pay this month. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
20
Fill themselves in
18

£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 980 rows.

Bills

One row per regular bill: who it goes to, how much, the day of the month it is due and how it is paid. Room for 80 rows.

ColumnTypeWorked out as
Billtext
Paid Totext
Amountcurrency
Due Daynumber
How It Is Paidchoice (list)
Categorychoice (list)
Statuschoice (list)
Next DuedateIF(ISBLANK([due_day]), "", IF(DATE(YEAR(TODAY()), MONTH(TODAY()), [due_day]) < TODAY(), EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()), [due_day]), 1), DATE(YEAR(TODAY()), MONTH(TODAY()), [due_day])))
This MonthtextIF(ISBLANK([bill_name]), "", IF([active] = "Ended", "Ended", IF(COUNTIFS([Payments!bill], [bill_name], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid") > 0, "Paid", "Still to pay")))
Still To PaycurrencyIF(OR(ISBLANK([bill_name]), ISBLANK([amount])), "", IF([active] = "Ended", 0, IF(COUNTIFS([Payments!bill], [bill_name], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid") > 0, 0, [amount])))
Cost A YearcurrencyIF(ISBLANK([amount]), "", ROUND([amount] * 12, 2))

Payments

Tick off each bill as it goes out. Add one row per bill per month. Room for 900 rows.

ColumnTypeWorked out as
Due Datedate
Billchoice (from Bills › bill_name)
Amountcurrency
Statuschoice (list)
Date Paiddate
Notestext
MonthtextIF(ISBLANK([due_date]), "", TEXT([due_date], "YYYY-MM"))
Days LatenumberIF(OR(ISBLANK([due_date]), ISBLANK([date_paid])), "", MAX(0, [date_paid] - [due_date]))
Vs ExpectedcurrencyIF(OR(ISBLANK([bill]), ISBLANK([amount])), "", ROUND([amount] - IFERROR(INDEX([Bills!amount], MATCH([bill], [Bills!bill_name], 0)), 0), 2))

This Month

Where the bills stand right now, and what a full month costs.

MeasureWorked out as
Total monthly outgoingsSUMIF([Bills!active], "Active", [Bills!amount])
Still to pay this monthSUM([Bills!outstanding_this_month])
Bills still to payCOUNTIF([Bills!paid_this_month], "Still to pay")
Paid so far this monthSUMIFS([Payments!amount], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid")
Share of the month paidIFERROR(SUMIFS([Payments!amount], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid") / SUMIF([Bills!active], "Active", [Bills!amount]), 0)
Bills counted as missedCOUNTIF([Payments!status], "Missed")
Next bill dueIFERROR(MIN([Bills!next_due]), "")
Largest single billIFERROR(MAX([Bills!amount]), 0)
Active bills on the listCOUNTIF([Bills!active], "Active")
Cost over a full yearSUMIF([Bills!active], "Active", [Bills!amount]) * 12
Paid last monthSUMIFS([Payments!amount], [Payments!month_key], TEXT(EDATE(TODAY(), -1), "YYYY-MM"), [Payments!status], "Paid")