Someone asked for a more detailed breakdown of my workflow to solve this.
Part of the trouble that I ran into for a while was actually defining the problem mathematically. It turned out after much tinkering that I really needed a bunch of different numbers sort of in order.
Step 1: what’s my emergency fund amount?
The first number I needed was how much reserve I wanted to set aside in case. I added an additional header and made a note about the cell. This is money I don’t want to touch.
Step 2: what’s supposed to be in my envelopes right now?
The second thing I needed was to figure out how much money was already allocated to envelopes. The math problem that I ran into here was that envelopes that had deficits (because we’d already overspent them) were offsetting the amounts in other envelopes if I used the “Available” cells that Savings Budget calculates on its own for income and expenses, resulting in a value that wasn’t helpful to me.
Sumif became my friend here. I set aside some cells, made a quick header, and grabbed my group names. I then used sumif([range of each group],“>0”,[range again]). The range I used here was the Available column for each category group. This only calculates in stuff if the available envelope is positive.
I probably could have done this more expansively with a single formula, but I needed to exclude the group headers and this was the easier way to do that.
Step 3: how much money do I have really available?
I didn’t want to just use the overall Net Worth because really, we use a main joint checking and main high-yield savings (money market) account for the bulk of our day-to-day transactions, and that’s really what I wanted to account for here. Our balances in Tiller also account for a specialized Minnesota credit union savings account where you can actually win significant cash prizes for saving (we’ve won $1,000 twice now!), some personal checking accounts for my wife, myself, and our kids (we deposit things like birthday money and gifts in these for personal use), and some other specialty stuff. I didn’t want to include those in our day-to-day budgeting.
So, I just added together joint checking, main savings, and then our credit card balance together here. That gives me a total of essentially available liquid assets. That got put under another header.
That gave me initially the number I really thought I wanted: what amount over the budget do we really have available to spend on something not in our very tight budget? If my wife wanted some new earbuds, could we actually afford to go get some?
That available, unallocated amount is money that we could either allocate to the savings in an envelope, or put into other accounts. That’s the number that isn’t already “spoken for” out of our total liquid available assets right now.
So, as of today, if I wanted to or needed to go spend $750 on something we were not expecting in the budget, I could theoretically do that.
Step 4: how much money am I expecting to make this month?
My first experiments just compared that effective liquid net worth to my current liabilities and reserved funds. That worked pretty decently in the last few days of the month when I had already basically received all expected income and was just worried about what was left in any envelopes.
When I rolled over to the next month for the first time, I realized that my first attempts at just comparing that effective liquid net worth to my envelopes meant I very suddenly dropped to about -$4,000 in unallocated funds. >.< The problem was that my calculation required me to pre-fund the entire month. That’s not helpful when the budget relies on expected income, and we need to be able to make decisions about surplus funds on more that just the last three days of a month.
So, I had to figure out how much income I was expecting to bring in, and include that, but in a way that also accounted for income that we did bring in. My paycheck is pretty consistent, but my wife’s is highly variable because she works part time and whatever shifts she’s actually given. We set a number together that she could consistently bring in as a floor and added up my expected paycheck.
I used an if statement to figure out what our income surplus is (or 0 if we haven’t reached what we expected yet.)
=IF(L76>L73,L76-L73,0)
If what we have already received this month is less than what we’re still expecting to receive, it uses the balance of what we’ve taken in compared to what we’re expecting. If we get a windfall from something like a tax refund or my wife picked up a bunch of extra shifts, once we go over the expected income for the month, the offset for the month will go to 0, and the surplus amount will start to increase.
If we’re early in the month and our income actually received is less than what we’re expecting, the offset gets calculated against the amount of our effective liquid net worth and the total reserved funds. If we’re later in the month, and we’ve exceeded the offset in expected income, then I don’t calculate the surplus in, because it’s already actually in my bank account and I don’t want to count it twice. If we got some surplus and we moved it to an account we don’t want in the effective liquid net worth, I don’t want to count it here, either.
This counting expected income might be somewhat financially dangerous if say either of us suddenly lost our job and we just didn’t have those expected paychecks. We’re fortunate enough to be in pretty stable positions, so I feel comfortable enough calculating this expected in. Someone who is an at-will employee or in unstable work probably should NOT do this.
Step 6: Have we overspent any categories?
This doesn’t actually figure into our available unallocated money, and this could be skipped. But I used the same sumif formula for all of my category groups to also add another calculation: are we within budget, or did we overspend somewhere? If the money is already spent and we didn’t allocate some more money to a category, I wanted that highlighted, because this started to become a consistent problem. We would see some surplus, and then instead of just asking “is it in a budget?” we’d decide to get something we wanted, or have a night out for once.
What I wanted to flag in particular was where my wife would just say “Oh, I know I worked some extra shifts this month and we’ll have extra money, I’m going to go treat myself to a new book and some coffee.” (Story may possibly based on actual events in the last few days.) But she would do this repeatedly and didn’t realize how much it all added up to, and next thing we knew, she’d brought in $300 surplus, and spent $400 excess.
Using essentially the reverse sumif formula for totaling up earmarked funds, I also made the same list for any groups with unauthorized overspending. (It’s authorized if we upped the savings in the envelope.)
This is money that has been spent, but is out of budget.
If we don’t have any of these unauthorized overspending, I used a conditional statement to say we’re all within budget, hooray!
=IF(L70>=0,“Completely Under Budget”,“Unauthorized Overspending”)
=IF(L70<0,L70,“
”)
We can choose to do one of three things.
-
If we just overspent an envelope that we expect will be funded next month with a budget rollover because we borrowed a little from the future (maybe we needed to buy some extra diapers this month because they were on a good sale even though we didn’t really have the money in the budget this month), then we can just leave it knowing that we’ll be back within budget soon. If our “fiscal year” started in the winter, I would expect that our utility rollover budget will look negative for a few months and then get back to the black in the spring and summer. Good to be aware of, but not something to really worry about.
-
We paid for something out of joint checking/savings, but we really were buying it from another account like personal funds. My wife wanted a book that just came out, and she intended to use her personal account to pay for it, but she forgot her card for that account and paid for it using Apple Pay. She transferred the money from her account to joint checking to make up the difference, but Savings Budget and the above calculations don’t really know that. I can just add in the $10 or so to savings and update the envelopes.
- We either ratify/authorize the spending by updating the savings for the category, or we return the purchase (if possible). It’s already been taken from available funds because we already spent it and it’s out of our effective liquid net worth. It doesn’t really change the calculations. It just flags for us that we overspent and we didn’t pre-plan for it. If it’s non-refundable (like we went out to a restaurant), then we update the savings, make a note that we overspent unintentionally, and remind ourselves to be more careful.
We try to do this sparingly, but there’s essentially nothing stopping one us here from spending what we shouldn’t and then “hiding” it by increasing the envelope to match. But eventually, we’re going to run out of unallocated surplus over the budget and earmarked funds, and that’s going to be bad.
So, that’s basically how I came up with that number.
It is by no means perfect. It’s very, very brittle because it relies on a bunch of absolute references, so if anything dynamically shifts because of vlookups or tables, etc., it breaks in a hurry. If your categories are not entirely tracked to savings or hidden in the Categories sheet, it absolutely WILL throw your references off month to month because it will move rows up and down when you switch months. If you go back into prior months, it’ll show you unauthorized overspending accurately enough, but the available unallocated amount will become wildly off (because it will start to pull from old data that is not current while comparing it against data that is.)
It’s working for us, at least right now, but if I figure out anything better, I will let you know.
Caveat emptor, your mileage may vary, costs may be higher west of the Mississippi River, no refunds or exchanges.
, 
