October 2025 Update (v2.0)
Overview
This is very similar to my Previous Account Balance Forecast here.
The difference is that it now provides a further look-ahead- 54 entries vs 27. I had to remove the Transaction Query data to create room.
Bottom line is I created this and thought others may want this as well so I’m sharing.
This forecasts the total near term balances for the accounts I pay bills from based on current Balance and upcoming Income and Expenses from those accounts.
I keep the bare amount of cash in my checking accounts to cover bills. To make sure I have enough cash in these accounts to cover upcoming bills, I wanted a way to forecast my account balance based on current balance and future bills and deposits.
Installation
Copy the sheet found here.
How it Works
It works similar to Jono’s Bill Payment Tracker that I based my original sheet on. Plagiarizing his description, but updating the terminology, etc…
In the Transaction Setup section, you need to enter the details for the transactions you want to track that affect your chosen accounts balance.
Using this information, the sheet forecasts the total balance of your chosen accounts using today’s balance and the next 52 transactions you choose to include. Transactions that are overdue are listed in red. Ones that are due in the next 7 days are listed in yellow.
When the transaction is downloaded, select the checkbox in the correct month column to indicate it’s been downloaded. This will remove the transaction from the Account Balance Forecast section above.
For each Transaction, the Last Occurrence and Last Amount columns will automatically show the latest Transaction data where the Category matches.
How to use it
Expand the hidden columns to the right and in column BU and BV, choose the account names in drop-downs and check the “Include” box for accounts you want to include in the starting balance…
At the top, you enter the Year and Month you want to start tracking…
In the Transaction Setup section, choose first expected date, frequency of transaction, which categories you want to track, and estimated amount of next transaction. You do not need to put negative number for estimated expenses. The sheet handles that by knowing if the category is an income or expense.
As transactions come in, check the box for that month. This will remove it from the balance forecast above.
The same Category can be used multiple times with different frequencies. This will will allow you to set up different amounts for the same category with different timeframes.
One use case for this would be- you want to know what your account balance will be if you make a larger payment than your monthly payment for a month (or more). You can now keep the “monthly” payment in the sheet and simply add a One-Time amount with the same category and it will include both entries in your account balance forecast.
Transaction setup would look something like this…
Account Balance then shows…
Permissions
It is ok for others to copy, use, and modify your workflow.
Notes
Because I get paid twice a month and the highest frequency the sheet supported is once per month, I started to update this sheet to allow twice a month tracking; but it got way more complicated than it was worth. Instead, I created 2 categories for my 2 separate paychecks each month- Paycheck 10 and Paycheck 25.
In the Transaction Setup section, you can update the Est Amount as often as you want. For example, I pay off my credit card bills every month, but the amount changes each month. I update the Est Amount for these bills each month after the previous month bill is cleared, which is when you check the box as being completed.
Enjoy.
