Proper Spreadsheets

Templates › 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.

Of the 20 columns, you fill in 13 and the other 7 work themselves out, and all 11 figures on the summary do too. There is room for 980 rows of your own.

£4.99one 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.

Already have a spreadsheet of your own? Check it for mistakes, free.

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. What the film shows, in order:
  1. A new row is typed into the Bills tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the This Month tab change to include the new row.
  4. The chart, Monthly outgoings by category, redraws with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

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. Open any tab to see its columns.

Bills

11 columns

One row per regular bill: who it goes to, how much, the day of the month it is due and how it is paid. Ready for 80 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
BillUse a short name. The payment log picks bills from this list.Type any text.
Paid ToType any text.
AmountType an amount in £.
Due DayDay of the month the money goes out, 1 to 31.Type a number.
How It Is PaidPick one: Direct Debit, Standing Order, Card, Bank Transfer, Cash or Cheque.
CategoryPick one: Housing, Utilities, Council Tax, Insurance, Communications, Transport, Subscriptions, Loans and Credit or Other.
StatusPick one: Active or Ended.
Next DueFills itself in.

Worked out from this row's Due Day.

The formulaIF(ISBLANK([due_day]), "", IF(DATE(YEAR(TODAY()), MONTH(TODAY()), MIN([due_day], DAY(EOMONTH(TODAY(), 0)))) < TODAY(), DATE(YEAR(EOMONTH(TODAY(), 1)), MONTH(EOMONTH(TODAY(), 1)), MIN([due_day], DAY(EOMONTH(TODAY(), 1)))), DATE(YEAR(TODAY()), MONTH(TODAY()), MIN([due_day], DAY(EOMONTH(TODAY(), 0))))))
This MonthFilled in automatically from the Payments tab.Fills itself in.

Worked out from this row's Bill and Status, using Bill, Month and Status on Payments.

The formulaIF(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 PayFills itself in.

Worked out from this row's Bill, Amount and Status, using Bill, Month and Status on Payments.

The formulaIF(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 YearFills itself in.

The Amount times 12.

The formulaIF(ISBLANK([amount]), "", ROUND([amount] * 12, 2))

Payments

9 columns

Tick off each bill as it goes out. Add one row per bill per month. Ready for 900 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
Due DateThe date this month's payment was due.Type a date.
BillPick one of the Bill entries on Bills.
AmountType an amount in £.
StatusPick one: Paid, Due or Missed.
Date PaidType a date.
NotesType any text.
MonthUsed by the summary to work out which month a payment belongs to.Fills itself in.

Worked out from this row's Due Date.

The formulaIF(ISBLANK([due_date]), "", TEXT([due_date], "YYYY-MM"))
Days LateFills itself in.

Worked out from this row's Due Date and Date Paid.

The formulaIF(OR(ISBLANK([due_date]), ISBLANK([date_paid])), "", MAX(0, [date_paid] - [due_date]))
Vs ExpectedHow much this payment differed from the usual amount on the Bills tab.Fills itself in.

Worked out from this row's Bill and Amount, using Amount and Bill on Bills.

The formulaIF(OR(ISBLANK([bill]), ISBLANK([amount])), "", ROUND([amount] - IFERROR(INDEX([Bills!amount], MATCH([bill], [Bills!bill_name], 0)), 0), 2))

This Month

11 figures

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

FigureWorked out as
Total monthly outgoingsEvery bill marked Active on the Bills tab.

The total of Amount on Bills where Status is “Active”.

The formulaSUMIF([Bills!active], "Active", [Bills!amount])
Still to pay this month

The total of Still To Pay on Bills.

The formulaSUM([Bills!outstanding_this_month])
Bills still to pay

The number of rows on Bills where This Month is “Still to pay”.

The formulaCOUNTIF([Bills!paid_this_month], "Still to pay")
Paid so far this month

Worked out from Amount, Month and Status on Payments.

The formulaSUMIFS([Payments!amount], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid")
Share of the month paid

Worked out from Amount, Month and Status on Payments and Status and Amount on Bills.

The formulaIFERROR(SUMIFS([Payments!amount], [Payments!month_key], TEXT(TODAY(), "YYYY-MM"), [Payments!status], "Paid") / SUMIF([Bills!active], "Active", [Bills!amount]), 0)
Bills counted as missedAnything you marked Missed on the Payments tab, in any month.

The number of rows on Payments where Status is “Missed”.

The formulaCOUNTIF([Payments!status], "Missed")
Next bill due

The smallest Next Due on Bills.

The formulaIF(COUNT([Bills!next_due])=0, "", MIN([Bills!next_due]))
Largest single bill

The largest Amount on Bills.

The formulaIFERROR(MAX([Bills!amount]), 0)
Active bills on the list

The number of rows on Bills where Status is “Active”.

The formulaCOUNTIF([Bills!active], "Active")
Cost over a full year

Worked out from Status and Amount on Bills.

The formulaSUMIF([Bills!active], "Active", [Bills!amount]) * 12
Paid last month

Worked out from Amount, Month and Status on Payments.

The formulaSUMIFS([Payments!amount], [Payments!month_key], TEXT(EDATE(TODAY(), -1), "YYYY-MM"), [Payments!status], "Paid")