Visualize Your Daily Projected Balances in a Google Sheet
Curious what the balance of your bank accounts might be on any given day of the month? We’ve built a simple sheet to give you an estimate. Here we share the steps to get this Projected Balances Google Sheet up and running in your Tiller spreadsheets.
Understanding where you are financially at any given day during the month can provide peace of mind. Knowing what bills are coming up and having an idea of where that leaves your bank account balance can dramatically decrease your financial stress. At least once a month, if not more, our support team is asked whether we have tools to help you project your bank account balance. Now we do.
We’re excited to share our Projected Balances Google Sheet. It’s a simple sheet you can copy into any of your existing Tiller financial spreadsheets and start building a daily projected balance chart and list.
If we receive a lot of feedback that this capability is useful, we may work to build out more automated and dynamic balance projection tools in the future, but it’s a start. It’s also a great way to start learning how powerful Google Sheets really are for visualizing your financial picture. All the magic is there in the sheet itself so you can create as many copies as you’d like to tinker and play.
What is the Projected Balance sheet?
First, a few things to understand about what this sheet is and what it isn’t. The Projected Balance sheet is somewhat manually driven. It’s not dynamically pulling data from your automated balances or transactions and it doesn’t factor in your existing budget amounts. If you copy it into a Tiller spreadsheet it will pull in your custom categories from your Categories sheet, if you have one, but that is the only linked data from other sheets in your Tiller spreadsheet.
It’s meant to give you an estimate of your daily projected balance based on fixed recurring monthly and yearly income and expenses. It’s simply a tool to help guide you, but be aware that other discretionary variable expenses or surprise income throughout the month that aren’t accounted for in this sheet will affect your daily projected balance.
Let us know what you think. If you love it, hate it, customize it further, or don’t understand it, send us a message to let us know by emailing firstname.lastname@example.org or use the chat window on the Tiller Console.
Getting started with this Google Sheets balance projection tool
- Open one of your Tiller spreadsheets.
- Copy the URL of your Tiller spreadsheet.
- Open the Reports Master sheet by clicking here.
- Right click the tab called “Projected Balances” at the bottom.
- Click “copy to” from the menu.
- Paste the URL of your Tiller spreadsheet into the bottom of the window that opens.
- Click “Select” to copy the Projected Balances sheet into your Tiller spreadsheet as a new sheet.
- Navigate back to your Tiller spreadsheet and find the new tab along the bottom called “Copy of Projected Balances” and rename the sheet by double clicking the sheet name.
Setting up the Projected Balances sheet
The great thing about this sheet is that you can use it run projections anytime, and then run a different scenario later based on the configuration settings at the top of the sheet. Duplicate the sheet as many times as you’d like to run concurrent scenarios.
- Choose a start date. You can modify this at any time you want to run a different projection scenario.
- Choose a period interval such as days, weeks, or months.
- Choose the number of periods for your selected interval by typing in a number. The sheet currently can only run one year of daily projected balances from the start date.
- Enter a starting balance if you have one. This is the starting balance for all accounts for which the recurring transactions configured starting on row 9 in this sheet have an affect. For example, if your Netflix subscription fee is automatically deducted from your credit card each month, and you include Netflix as a monthly recurring transaction, you’d want to include your current credit card balance in this total starting balance.
Setting up your recurring transactions tables
Your recurring transactions should include both income and expenses. Income transactions should be entered as positive amounts. Expense transactions should be entered as negative amounts.
- Select a category from the dropdown in column A for fixed monthly recurring categories in the Monthly Recurring Transactions section. If you don’t have a Categories sheet you can still type in a category here, but you’ll see a red triangle in the upper right of the cell.
- Choose the day of the month on which you expect the transaction to post to the account. If a transaction posts on the last day of the month choose “last day.”
- Enter the amount of the recurring transaction. If it’s an expense be sure to enter it as a negative number (-400) and if it’s income be sure to enter it as a positive number (1000).
- Repeat these steps for all recurring monthly transactions you want to track in your Projected Balance sheet.
That’s all you really need to do to start seeing the Projected Balance chart update, but you can continue customizing this sheet for your unique financial picture.
Accounting for Yearly Recurring Transactions in Projected Balances
Your daily projected balances will be more accurate if you also include infrequent yearly recurring transactions like car insurance, personal property taxes, an annual bonus, or car tags. You can configure this in the Yearly Recurring Transactions section.
- Type in a description for the transaction.
- Choose the month and day the transaction will post to your account. You can type in the month and day using two formats. Use either Mar 5 or 3/5 format.
- Enter the amount for the yearly recurring transaction. The same rules apply as the monthly recurring ones. Expenses are negative and income is positive.
Checking your daily projected balance and making adjustments
If you want to see the exact balance amount you can click on the chart and hover across the line to see the projected balance for any given day during the configured period.
You can also scroll a bit further to the right to see the daily projected balances for the period as a list.
If you want to see more or less data in the chart or list play around with the configuration settings at the top of the sheet by changing the start date, period interval, number of periods, and starting balance data.
What about variable expenses throughout the month?
Of course your daily projected balance would be even more accurate if it accounts for things that are variable and don’t occur on the same day throughout the month like groceries, gas, and restaurants.
Including this type of data in the chart is possible, but it’s considerably more complex to build, and we wanted to keep it simple in this first version of the tool. Let us know we have your vote for continuing to build out projected balance capability, or how you’ve accomplished this already in your own sheet, by emailing email@example.com or using the chat window on the Tiller Console.
Customizing the Projected Balances sheet
If you’re comfortable with queries, arrayformulas, and other Google Sheets tricks you might have learned from Ben Collins’ website, or you just want to check out what’s under the hood, you can unhide columns Q through AE.
Each column header has an explanation note that you can view by hovering over the cells in row 1 in columns Q through AE to learn more about how these columns power the chart and daily balance list.
If you mess around with what’s in the hidden columns, and something breaks, just copy in a fresh version and start new. We can’t support fixing broken copies of this sheet due to editing the hidden columns so please just start fresh with a new copy and have fun!