December 2025 Update (v1.2)
Overview
This is similar to my Previous Account Balance Forecast here.
The difference is that it now provides a further look-ahead- 54 entries vs 27, allows you to choose an end date for the transactions which allow you to have different amounts and frequencies for the same category.
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
I started with Jono’s Bill Payment Tracker format and rewrote it for this function. Plagiarizing some of his description, but updating the terminology, etc…
In the Transaction Setup section, you set up your transactions.
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 BV and BW, 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, end 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 and start and end dates. This will will allow you to set up different amounts for the same category with different timeframes and frequencies.
One use case for this would be that 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.
Another use case is if you set up monthly credit card payments but know that this will be ending. Just set the End Date and this will stop forecasting it after that end date.
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. If you have a particular month where you have a much larger or smaller payment amount, you can keep the monthly payment and create a 1 time payment with different amount.
Enjoy.
