Monthly Payment Tracker spreadsheet

Increasingly the various businesses that I pay monthly (rent, garage, credit cards, phone bill, newspaper bill, and utility bill) are only notifying me that I have a payment due by email or worse, not notifying me at all.  Combined with a shrinking window between when the bill is issued and when late charges start to accrue, there is increasing pressure on me to track my bills.

In the past I would accumulate unpaid bills on a designated spot on my desk at home.  The presence of paper bills there told me that I had bills to pay.  When a paycheck rendered me sufficiently liquid to write checks, I would pay them.

As the transition to email notification and non-notification has progressed, many of these bill issuers have offered to help me out with this scheduling problem.  All I had to do was give them the ability to directly take money from my bank account in whatever amount they desired and at whatever time they liked.

You’re kidding, right?

Not all of these institutions are crooked, and perhaps none of them are crooked today, but the documented behavior of some of their peers has convinced me that I should not trust any of them to take only what they are owed from my account.

So, how to keep on top of these due dates?

The first step was to create a spreadsheet.  Each column was a payee.  Each row was a date.  When I paid bills I would insert a row and put that day’s date in the leftmost column and put dollar amounts in each column corresponding to a bill I had paid that day.

It ended up looking something like this:

Date	Shady Bank	Weird Bank	Odd Bank	Rent	Parking	Pa Bell
2017-02-01				XXX	YYY
2017-02-13	ZZZ	WWW
2017-02-25						QQQ
2017-02-26			VVV

It helped me keep an eye on things, particularly after I set up some color coding so that the background of odd-numbered months was different from that of even-numbered months.

But I was still not happy, since the payment windows established by the banks were becoming smaller and smaller.

I decided to figure out a formula that I could put in the top of each column, right below the column headings, that would tell me how many days had elapsed between the most recent payment and today.

How to do that?

The top level structure of the formula was “=today()-(something)” where something translated to the date in column A of the row with the most recent payment.

How to find the row number of the most recent payment?

With some research, I finally came up with this formula - in this case for Column E:

=today()-index($A:$A,max((E3:E<>"")*row(E3:E)))

This is pretty weird … here’s how it works:

max((E3:E<>””)*row(E3:E)) finds the highest row number in column E on or after row 3 that contains a nonempty value.

index($A:$A, max(...)) finds the value in column A at the row identified in the max(...) formula above.

How did I figure out the idiom index(column, max(...))?  I’d like to claim that I’m a master of Google Sheets and read relevant documentation, but it ain’t so.  I did some searching and found this StackOverflow page that illustrated the idiom.  I had to adapt it significantly for it to work properly, but now I have a row of values at the top of the columns, just below the headings and in the frozen part of the sheet.  Each cell reports the number of days since the last payment in that column.  Here’s the complete formula for column G:

=today()-index($A:$A,max((G3:G<>"")*row(G3:G)))

I have also established conditional formatting for that row so that when more than 20 days has passed since that bill was paid the time is highlighted in red to draw my attention to it.

So now it looks something like this:

Date	Shady Bank	Weird Bank	Odd Bank	Rent	Parking	Pa Bell
	16	16	3	29	29	4
2017-02-01				XXX	YYY
2017-02-13	ZZZ	WWW
2017-02-25						QQQ
2017-02-26			VVV