Add a Projected Balances sheet

Overview

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.

Installing the Projected Balances sheet

  1. Install the Tiller Labs Solutions add-on
  2. Open the add-on and choose “Add a Solution”
  3. Click on Projected Balances
  4. Choose “Add to spreadsheet”
  5. Start customizing

How to use 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.

  1. Choose a start date. You can modify this at any time you want to run a different projection scenario.
  2. Choose a period interval such as days, weeks, or months.
  3. 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.
  4. 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.

  1. 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.
  2. 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.”
  3. 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).
  4. 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

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.

  1. Type in a description for the transaction.
  2. 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.
  3. 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 support@tillerhq.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, use the Tiller Labs add-on to restore the sheet and start new.

I have transactions in the same category that occur more than once a month - for example, payments for category student loan occur on the 1st and 20th - but it would be helpful to be able to identify the account and/or add a note to this sheet in order to track where the recurring payment is going. Is it possible to add an accounts selection column and/or a notes column in this sheet?

Hi @CSH,

I think you could add multiple of the same category and specify a different date for the second instance, per your example with student loans. Just create two entries for Student Loans.

As for adding notes, I wouldn’t insert any extra columns there as I think it would break the sheet’s formulas, though @jonorlin would know best.

Heather

Hi @CSH,
@Heather is correct that adding extra columns sometimes breaks the sheet formulas.

But I noticed in the Projected Balance sheet, Column D is blank and just used as a spacer column. If you wanted, you could put Notes or Account info into Column D, right next to your monthly recurring transactions. You should also make that column a big wider so you can better see what you enter there.

Any information in that column would not impact the Projected Balances results.

Would that solution work for you?

Hi there -

Thanks for getting back to me! I ended up creating a new sheet using a simple google template for the running balance and then added on tiller labs to import the sheets I need from tiller. I will post a copy without data so you can see what I did.

@CSH,
I’m glad you were able to figure out a better solution.
Please share it so others might benefit as well.