Near-term Account Balance Forecast

***July 2023 Update (Rev 1.1)***
  • Minor tweaks and cleanup

Latest rev found here.


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.

I was checking out @Jono’s The Bill Payment Tracker and thought it would be a good starting point.


Copy the sheet found here into your workbook. It uses the Accounts, Categories, and Transactions foundation sheets.

How it Works

It works similar to Jono’s Bill Payment Tracker referenced above. 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 27 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 BO and BP, choose the account names in drop-downs and check the “Include” box for accounts you want to include in the starting balance…

FYI- “BH Balance” is taken from the Balance History sheet. “SB Balance” uses the Starting Balances sheet I created which can be found here. If you don’t use the Starting Balances sheet, it will just use the balance from the Balance History sheet.

After this, it’s the same philosophy as Jono’s Bill Payment Tracker.

At the top, you enter the Year and Month you want to start tracking…

Screen Shot 2022-04-23 at 1.19.31 PM

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.


It is ok for others to copy, use, and modify your workflow.


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 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.

Jono’s original Bill Payment Tracker sheet allowed you to match either Category or Description. I only allow Category.


Great work @Cowboy13 !

I’m delighted to hear how you were able to take my Bill Payment Tracker and extend it to better fit your needs. And then to share it with the community! Even better.

Whenever I create a new template, this is exactly what I hope will happen.


1 Like

Thank you, it was a great start, some very cool formulas.


I can’t get the accounts in column BM to fill and the dropdown in column BP. Could this be due to your use of “checking and savings in the formula” I also have the same issue with your starting balances.

Could be. The formula is looking for Account Type “Checking” or “Savings” on your Accounts sheet. On my Accounts sheet, this is column M. If you don’t have any Checking and Savings Account types, column BM will be blank.

If this is the issue, a quick fix; delete this restriction in the formula in cell BM1…

Let me know if this fixes it.

It fixes it but I’m unsure why it failed in the first instance because I have the “savings and Checking” type in my Account Sheet Column M. Can the same principle be applied to “Starting Balance” to make it work?

Column BP does not auto populate. You need to choose which accounts you want to include in column BP by using the dropdown and then check the box in column BO.

FYI- the reason you don’t want column BP to auto populate with the accounts is- if you add or delete or hide an account, it could rearrange the list of accounts you previously checked and the check box does not get rearranged. The sheet will then be using accounts you did not intend to use until you go back and recheck the correct accounts.

Hope this helps.

Also, no idea why column BM did not populate. What do your columns AI:AM show?


Sheet Column Name Column Lookup Start
Accounts Group P ‘Accounts’!P
Accounts Account H ‘Accounts’!H
Accounts Account # I ‘Accounts’!I
Accounts Last Update K ‘Accounts’!K
Accounts Last Balance J ‘Accounts’!J
Accounts Class O ‘Accounts’!O
Accounts Hide D ‘Accounts’!D
Accounts Type M ‘Accounts’!M

That looks correct. Not sure why BM didn’t populate.

Hopefully, you have you need now.

Thank you @Cowboy13 and @Jono for this sheet!

I’m trialing Tiller to see if it can replace Moneydance, which I’ve used for over 10 years to track expenses. One of my main uses of Moneydance is to ensure all my bills are paid while keeping my checking account balance above zero. That requires an accurate near term projection of my checking account balance.

When I saw that the Tiller Foundation Template lacked this functionality, I started thinking about how to create my own sheet to do what I needed. Then I found this Account Balance Forecast sheet, and after spending the time to set it up, so far it seems to be working perfectly. This is giving me confidence that Tiller will be able to replace Moneydance for me.

1 Like

So glad you find it useful. Because I keep a minimal balance in my checking account, I also needed something to do the near term balance forecasting. This does the job for me.

1 Like

Any chance that someone could convert this for use in Microsoft Excel?

I’m having an interesting issue when entering the account that I would like to set up. It and a few others cause BQ column and BT column to have #VALUE errors, however some of the other auto populated accounts work fine.

Any ideas what might be causing this @Cowboy13 ? I think I figured this out

Edit after original post
"After further investigation, the accounts all have Apostrophes in them.

I remember seeing something about this error in another thread but do not recall what the formula solution to fix this was?

In the meantime I’m going to rename my accounts so that I can use this sheet"

@bentyre1- thanks for the update. Let me know if that fixes the issue.

Yehp, that fixed it. Little bit of find replace work on my transactions so that some other things still work right but it’s all good

@Cowboy13 I’m curious with the following statement as to why only categories are allowed?

This may just be me personally but i like to be able to know that I’m going to pay x amount on the monthly credit cards i use for rewards and still have y amount left at the end of the month or before getting paid again, however all of these cards i categorize together as credit card transfers since i do not carry balances on them for the most part. Or im paying x amount on power bill on a certain date then what’s my balance?

It does work for the debt accounts that i do have because of the Debt Planner sheet that ties the desription of these specific debts to categories so it can find it in the monthly budget amounts.

I guess I’m not seeing the benefit of category level match only as an option because if i need to categorize each transaction I would like to track with this as a different category i loose the overall benefit of categorizing my transactions together in the first place. And those credit cards i use for rewards have all different due dates throughout the month.

I hope this explanation makes sense

@bentyre1 - I think what you are saying is that you edit the credit card payment (and other transactions) description each month to distinguish the month to month expenses. I do not do this, but see why this would work.

The way I handle this is I have a separate Category for each credit card account payment I send from my checking account- e.g. “Chase Freedom CC Payment” or “Capital One Venture CC Payment”. This allows different due dates for each credit card payment. Like you, I use “Transfer” type for all CC payments and pay off my credit cards in full each month. After each month is paid off/deposited, I update the expected credit card amount (or gas bill, variable rent income- basically any transaction that is a variable amount month to month) to the anticipated payment/income amount for the next month. This works well for me.

Candidly, I did the edits way back in April and I don’t remember how the Description option was implemented and it would take me a quite while to look through Jono’s original Bill Payment Tracker and put it back in; for a feature I don’t use.

However, this is what makes Tiller so awesome; you can personalize any sheet you want to tweak to your needs. Please feel free to edit my sheet.


I’ve installed balance history tracker so I can use account balance forecast. Balance history tracker seems correct, but account balance forecast is completely blank. What am I doing wrong?

Take a screenshot of Columns BO-BR. Their should be numbers in BQ and BR.