Overview
The 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.
This Excel template mirrors the original created in Sheets.
This bill tracker is only meant 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.
How to Install the Bill Payment Tracker
- Download the Bill Payment Tracker workbook .
- The query section pulls in transaction data from the
Notecolumn in theTransactionsworksheet (which may not exist in your workbook). If the column is not present, you can either add aNotecolumn to yourTransactionsworksheet or, if you do not want to add aNotecolumn, you can delete the formulas inQ7:Q21(which try to fetch data from that column). If you do not address this, you will see a “There’s a problem with this formula” error in the next step. - Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
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, mark the cell for the bill with a ● 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
Clear Sample Data
Clear out the sample data in the “Bill Setup” section from A25:D32.
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/23 (if you want it to start in Oct 2023.)
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.
- 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. It does not need to be an exact match.
Some people may prefer to match using the Category field instead of the Description field. See the FAQ below for more information about that option.
- 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 mark the cells with a ● 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:
-
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.
-
If any bills have been paid already from the new starting month/year forward, make a note of them. You will need to delete the
●character from those cells after changing the date. -
Re-check any bills from Step 2.
Frequently Asked Questions (FAQ)
What if I want to use Categories instead of Descriptions?
This sheet was built 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 V2 from Description to Category.
If you have bills from the same Payee but they are listed with a slightly different Description, Category matching might be worth trying.
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.
Shoutout
Many thanks to @alan.heatherley for his work porting the original Google Sheets version to Excel.
