Bill Payment Tracker

Overview

The Tiller Money Labs Bill Payment Tracker is designed to keep you informed about your upcoming bill payments.

Some of the benefits of using this sheet include:

  • Easy setup
  • A chronological list of your next 15 bills to be paid
  • A way to check which bills have been already paid
  • Detailed bill history data for a selected payee
  • It’s not required to use Tiller to use the sheet, but integrating Tiller’s Transaction sheet with this tracker will provide additional information
  • This sheet works with bills that have different frequencies, including Monthly, Every 2 Months, Quarterly, Twice a Year and One-Time only. (Version 1.02 adds Yearly option as well.)

This bill tracker is only mean to be a tool to help you keep track of your upcoming bills. Be careful to read all the instructions here and use care to make sure you check the correct boxes when you pay a bill. Tiller is not responsible if you miss your bill payment while using this tool.

The sheet was designed for people who write checks or manually use a bank’s bill payment system to pay their bills.

If you have all your bills set to autopay each month, this sheet is probably not necessary or that useful.

If you have any suggestions on how to improve the sheet or other ideas about Bill Tracking, add your comment below.

Installation

  1. Open either your Tiller spreadsheet or any spreadsheet.
  2. Install the Tiller Money Labs add-on
  3. Open the add-on
  4. Choose “Add a Solution” in the sidebar
  5. Click on “Bill Payment Tracker”
  6. Choose “Add to Spreadsheet”

How It Works

You need to enter all your manually paid bills in the Bill Setup section. You also enter the Year and Month you want to start bill tracking.

Using this information, the sheet calculates your next 15 Upcoming Bill Payments in the top left section. Bills that are overdue are listed in red. Bills that are due in the next 7 days are listed in yellow.

When you manually pay the bill, select the checkbox for the bill and the correct Month column. This will remove the bill from the Upcoming Bill Payments list.

For each Bill, the Last Paid and Last Amount columns will automatically show the latest Transaction data where the Description name in the Bill Setup row is contained in the Description field of the Transactions sheet.

How to Use It

Year and Month setup

Select the Starting Year and Month for the sheet. Any bills due prior to this starting month and year won’t be included.

Bill Setup

Add your bills to the Bill Setup section, one per row. Bills do NOT need to be entered in order.

For each bill, include:

  • 1st payment Due

This can be a date before or after the starting year and month. If you are entering a monthly bill that is due on the 15th of the month, enter 10/15/20 (if you want it to start in Oct 2020.)

If you want to give yourself some pad and enter the due date a few days before it’s due, that’s fine.

If a bill is due at the end of the month, you can enter the 31st if the month has 31 days. It will still appear on the last day of the month if the month has less than 31 days.

  • Frequency

Options include Monthly, Every 2 Months, Quarterly, Twice a Year and One-Time only. (Version 1.02 adds Yearly option as well.)

  • Description

This is usually the payee. The formulas will use the text entered here to see if that text is contained in the Description field of the Transactions sheet. If does not need to be an exact match.

  • Est Amount

Some bills might have varying amounts so this field is optional. If you enter an amount here, it will show up as the Estimated Amount in the Upcoming Bill Payments above. This information is only provided as an estimate. You should use the actual amount due when paying your bill.

You will see your next bills listed under Upcoming Bill Payments. When you pay the bill, click the checkbox for that bill and the correct month in the Bill Setup section.

If a bill is not due in a particular month, no checkbox will appear.

By checking the box, the bill will disappear from the Upcoming Bill Payments list for that month.

Bills that are overdue are listed in red. Bills that are due in the next 7 days are listed in yellow.

Bill History Query setup

If you select a Description, you will see the most recent transactions, if there are any, that contain the Description name in the Transactions sheet.

Changing the Starting Year and/or Starting Month

Since you manually check the boxes when bills are paid, if you change the sheet’s Starting Year or Starting Month, the checkboxes will also change and lead to incorrect results. Before adjusting these cells, following these instructions:

  1. Make sure all bills are paid up to the new month/year. Overdue bills PRIOR to the new start month/year will NOT be shown.

  2. If any bills have been paid already from the new starting month/year forward, make a note of them. You will need to check off those bills after changing the date.

  3. After changing the month and/or year, Select all the Checkbox cells then hit the space bar twice to clear all checkboxes. You may see a pop-up saying “Heads up! You may have clicked on a checkbox that is not visible. Toogle anyway?” You can select “Cancel”.

  4. Re-check any bills from Step 2.

Frequently Asked Questions (FAQ)

What if I want to use Categories instead of Descriptions?

This sheet was build using Descriptions since some people might have multiple bills in one category. But if you want to switch to Categories, just open the hidden section of the sheet and switch cell W2 from Description to Category

What if I’m not seeing the correct Last Paid and Last Amount or transactions are missing from the Bill History Query?

The transaction Description in the Transactions sheet must contain the Description in the Bill Tracker for there to be a match.

What if I have a bill scheduled more than once a month?

You could setup 2 monthly bills and call them Payee 1 and Payee 2. You would need to adjust your Transactions sheet to match these names in order for the Last Paid, Last Amount and Bill History Query to work.

Developer Notes

If you don’t want to understand how the formulas work in this sheet, you don’t need to read this section.

We encourage you to use this sheet as a base and ,if desired, customize it to your own requirements. Here’s a little insight into how the hidden sheet formulas work. The key part is 13 columns of hidden data. Each of these columns, AU to BG, use an ARRAYFORMULA() in the top row so all the columns can be filled to the bottom without any other formulas.

  • Bills: This column is a list of all the Bill descriptions repeated 12 times, once for each month.

  • Month: This loops thru each of the 12 months for each Bill.

  • 1st Payment: This uses VLOOKUP() to get the user entered 1st Payment date.

  • Due Date: This calculates the Due Date for the individual month in the row. Due dates might not be the first date of the month. The formula also deals with months of different sizes.

  • Days to Due Date: This calculates how many dates from TODAY() until the due date.

  • Days Text: This turns the Days to Due Date into a better text version for the Upcoming Bills section.

  • 1st of the month 1st payment: This calculates the first day of the month for the 1st payment. It’s used in the next column.

  • Months Difference: This calculates the difference between the current month and the first payment date. If the 1st payment month is after the bill month for the row, it will display Prior.

  • Frequency: This uses VLOOKUP to get the manually entered Frequence for the bill.

  • Due: This determines whether the bill is due for the row month. It depends on bill frequency and 1st first date.

  • Paid: This determines if the checkbox for the bill and the month have been checked.

  • Due & Not Paid: This determines if the bill is both due and not paid based on the prior two columns.

  • Bill and Month: This joints the bill name and the month of the row. It is used in the formula in U24 to create the Bills Due checkbox grid in U24 to AF and the Days Until Due grid in AH. Those grids help create the conditional formatting rules for the checkbox section.

5 Likes

We are updating to Version 1.02 which adds the option for a Yearly bill (every year on the same date) in addition to One-Time bill (just once.)

1 Like

Version 1.03, which released a few days ago fixed an issue with the Frequency calculations when Quarterly was selected. Quarterly now works properly, showing a bill due every 3 months or 4 times a year.