Auto Balance Forecast

[MinFarshaw]

Overview

This template forecasts the balance of any checking / savings account, fully automatically, without having to do anything except adjust the timeframe you’d like to see, to produce a simple, easy-to-read graph.

(ignore the artifact on the left, it works seamlessly with real data but I got sick of messing with the fake ones)

I’ve seen other manual versions posted, but I wanted something that just works, without the need to double-check transactions or check boxes. That leaves room for error, this does not, and is extremely easy to use.

I was heavily inspired by the templates @1Email2RuleThemAll has posted, they’ve been some of the most useful to me so far, especially the “Upcoming Recurring Expense / Account Health Dashboard”. My goal was something like that, but for a longer time period and more at-a-glance.

I saw in the comment of that post the “Check Expenses Forecast” he’s planning, and I am very excited to see it when it comes out, but I realized it wouldn’t work for me at all for one big reason.

Variable income
I work on commission. My income is highly variable, and with the plans to use “average net income” from “Income Settings” to drive paychecks in it, it won’t be useful to me.

So!
I used @jpfieber 's “Paycheck Deduction Generator” to drive paychecks in this template.

Not sure I understand most of the formulas I cobbled together, but I just kept messing with it until it stopped breaking and I’m pretty sure I got it right. Came over from Mint about a month ago and have never used spreadsheets like this before, so if it’s broken I apologize (advice and critique very much welcome).

Installation

Dependencies
Install in the order listed below. Again, huge shoutout to @1Email2RuleThemAll , your work is amazing and this entire thing relies mostly on your templates to function. You’ve made some incredibly useful stuff, and I’m a huge fan of your modular approach.

1. Paycheck Deductions Generator by @jpfieber - determines amount / date of all future paychecks
2. Income Settings by @1Email2RuleThemAll - determines date of most recent upcoming paycheck
3. Credit Card Settings by @1Email2RuleThemAll - determines due date / amount due from credit cards
4. Recurring Expense Entry by @1Email2RuleThemAll - Feeds “Generated Expenses”
5. Generared Expenses by @1Email2RuleThemAll - Feed all non-credit card recurring expenses

I know it’s a lot, but it’s worth it, I promise.
Once these are all in place, it’s extremely easy to use.

Finally, from the link below, copy “Auto Balance Forecast” to your Foundation Template.

https://docs.google.com/spreadsheets/d/1ufOtoJOlS9a6SuGP_4nN2L04z-qwqMlFV72h2j3HmSE/edit?usp=sharing

Setup

Incredibly simple.

1. Open the hidden columns to the left of column E, and select the account your paychecks are deposited into.

That is all.

Optional!
If you’d like your paychecks to show up with a name instead of blank in that list, put the name in Colomn C of the Paycheck Deductions Generator.

Usage

Simply select the account you would like to view, and the number of weeks into the future you would like to see.

Permissions

I’m sure this could be made better, I have no idea what I’m doing to be honest.
Use it, modify it, whatever you like, feel free to share.

Notes

Huge caveat.
This is really only accurate 4-6 weeks out at the moment, if your expenses include credit cards.
It only accounts for current statement due at the moment.

Hopefully, I’d like to figure out how to forecast upcoming card statements / balances due based on recurring expenses charged to credit cards (combined with total balance remaining after the statement), but it’s breaking my brain at the moment. Any ideas / tips welcome.

Either way, I personally find it useful enough as-is to share.

–
Now includes current statement charges, next due date, and adds in any recurring charges that will hit before the current (and next!) statement end.

Should be extremely accurate 12-16 weeks out now.

FAQ

Ask me below.
And hi! I’m new here and quite enjoying it, and thank you to everyone building amazing things here.

[1Email2RuleThemAll]

Hey, sorry! Work has been a little hectic lately (computer programmer), and I’ve been a bit burnt out on staring at code, lol. I’m glad to know you got so much value out of my solutions, and that my modular approach is appreciated

This is really cool! I have been meaning to make something along these lines for awhile now, but I’ve just lacked the energy to devote to it. I have the Check Expenses Forecast mostly done, but just have to get myself to push through the last bit of logic that needs worked out. On that note, though, I have been planning on adding alternative methods to pulling in the paycheck amount to this sheet before releasing it, so that it’s not fully dependent on Income Settings.

I’m going to dive into your solution more a little later and play around with it! Thank you for sharing!

[MinFarshaw]

Glad you like it!
It’s relatively simple, uses a heavily modified version of cell P3 in your Upcoming Expenses dashboard to make a list of expenses / dates to generate the graph from.

It’s over in cell A6, added a query for

*current statement (not yet closed) charges
*paychecks from the Paycheck Deduction Generator, filtering for ones after “next paydate” from the Income Settings sheet, and also filtering for what account they get put into
*a couple new tables generated in the far right of the sheet, that calculate recurring expenses that will hit credit cards, and when the due date from the master pay method will be

[MinFarshaw]

Version 2.0 !!

Adds considerably more functionality and cleans things up a bit.
Will require a reinstall of this sheet only.

Long story short, it allows you to work in budgets from your Category sheet, and should now be even more accurate, 3-6 months out easily!

• Allows you to override for “irregular expenses”, pulling selected budgets from the category sheet
• Creates new itemized charges based on this, and the payment method you use for them
• Choice of whether to have it generate for the first or the last day of the month
• Pulls from the transaction sheet to subtract already spent amount from this months budget amount
  .
• Combines generated expenses set to go on credit cards with the current statement charges, so they no longer appear as 2 separate transactions in the itemized list

Additional Setup

Columns S and T, as desired. Add in “irregular expenses” using budgets from your Category sheet, choose your usual pay method.

Expand using the plus mark over column U.
Default is to generate on the last of the month, check the box for any you would like to hit on the first instead.

That’s it!

I especially like to use this sheet to plan for moving money into savings / knowing when I’m “safe” to do so. This sheet provides an very simple, automatic, visual way to do that, and helps me plan much further in the future.

[MinFarshaw]

…Version 2.1

Honestly reworked it so much it should probably be called 3.0, but I just posted it a day ago and it’s mine so who’s counting.

Huge changes, 100% must reinstall (just this sheet) , but setup is super easy still.

Now usable up to 1 year out, including all calculations (credit cards, any budgets / recurring expenses you want to include).

Still completely, 100% automatic. No checking boxes, reconciling transactions, nothing. Just open the tab and go on with your day.

---

Now compare up to 3 accounts on the same graph.
Didn’t like first vs last day for budget generated expenses, changed it to be customizable to any day of the month.

---

Also added an option to show the budget going into the account, instead of an expense.
Take “Emergencies” as an example above. It’s a savings category for me. Have it coming out of the checking, going into the savings, and I can view both account balanced simultaneously at a glance.

Regular budgeting is great, but most of my expenses go on a dozen different credit cards (and therefore there’s a month or so lag until I actually see the cash coming out). This is a much easier way to plan savings goals / make sure you don’t overdraw yourself if you have a whole bunch of rewards credit cards you simply must use.

Added in an additional 6 months of budgets from the Categories sheet. This along with more credit card statement calculation tables is what makes it accurate up to a year out now.

Still able to view itemized if you want.
Little harder to read with the 3 accounts now, color coded text to make it easier.
Anything highlighted is something coming into the account, for ease of scanning the list quickly.

---

That’s about all!
Left little notes in cells all over the place, though there’s still not much setup. Any questions (or find something I missed!) just let me know.

[norsh]

@MinFarshaw This seems really cool and appreciate the time you put into this to stitch it together! I’ve been using Tiller for a few months now but wouldn’t characterize myself as a power user. Regarding the Installation steps to use your sheet, there are quite a few sheets involved. Is there a way to use your Auto Balance Forecast sheet without the first dependency, Paycheck Deductions Generator?

[MinFarshaw]

Hey! Hmm, yes, technically.

You could put in your paychecks as a negative amount in the “recurring expense entry”, that would do the same function.

I’ll add a way to pull it from income settings soon though and tag you, that’s a good point.

Thanks for the feedback!

[MinFarshaw]

Version 2.2

Paycheck Deduction Generator now optional, not required
New feature: choice of using Income Settings or Paycheck Deduction Generator to provide paycheck amount, reducing dependencies.
Thank you @norsh for the suggestion!

This makes install simpler for those with regular income, and still allows for higher accuracy for variable if you so choose.

Minor additional new feature in cell D3/D4, you can now enter the name you want for your paychecks directly in-sheet instead of elsewhere.

Bug fixes:

• found an error in how it was calculating credit card statements / new charges for chosen accounts 2 and 3 (cells Q4 and Q5). Now fixed
• Budget generated expenses / “other expenses” were not falling off if the day selected for them had passed. Now fixed

---

Updating to the new version

Reinstall just this sheet again. It’ll be way easier. I’m not a professional, I changed so many things back and forth and back at this point.

Keep your old version, copy this new one to your Foundation sheet, and then just copy your settings from the old one before deleting.

[bwaldman]

Thank you this is fantastic! However, I am getting an error where my last paycheck is showing up (i get paid Semi-Monthly so its showing for 9/30) and its adding it to the current balance.
B7 is showing the date for 9/30 and column G is showing the Balance of my account + Income (which was a little less than 2 weeks ago).
Trying to work my way through the formulas, but if you happen to have any ideas on how to fix that would be amazing.
Thanks!

[MinFarshaw]

Hey! Sorry, been a while.
Yes, you can use the Income settings sheet instead if you prefer, there’s a setting in the hidden columns on the top left.

[MinFarshaw]

Hmm. Not sure what’s going on, I can’t seem to replicate it.

The next paycheck date is pulled straight from the Income Settings sheet.
Is that sheet also showing a next paycheck date of 9/30?

IF(
                C3="Paycheck Deductions Generator", IF(Q3=C4, {QUERY(FILTER({INDIRECT($BU$3), -INDIRECT($BU$4)} , INDIRECT($BU$3) < U5, INDIRECT($BU$3) >= INDIRECT($BJ$21)), "SELECT Col1, '"&if(E3<>"",E3," ")&"', Col2, '  ', '   '")}, {"","","","",""}), IF(Q3=C4, {QUERY(FILTER({$BW$3:$BW100, $BX$3:$BX100} , $BW$3:$BW100 < U5), "SELECT Col1, '"&if(E3<>"",E3," ")&"', Col2, '  ', '   '")}, {"","","","",""})

This section is the one that handles the paychecks. Specifically…

INDIRECT($BU$3) >= INDIRECT($BJ$21)

This filters out any paychecks before “next paydate”

[bwaldman]

It shows that the Current Pay Date is 9/30

but the next is 10/15

I should also note that I get paid Semi-Monthly. I dont think that would affect it, but who knows