Project & plan checking balances over the year?

[soitmake]

Can Tiller be used to help maintain positive checking account balances?

Tiller seems to be really good at helping aggregate all the complexities of lots of accounts into one headline system, to help with analysis and budgeting and to plan for expenditure over time. Tiller helps with cashflow and budgeting at the aggregate level. However what I am not seeing is how to go back to the account level where necessary.

For example: I can see how to plan for a large expense in a given month of the year, which will be different from my baseline budget. If I use a credit card to make that purchase then at the aggregate level the money will have flowed from me to a vendor, matching my Tiller budget for that month. However that’s not the end of my financial concern regarding that transaction. Since I used a credit card, it could be 4-9 weeks before the cost of that payment needs to go from my checking account to pay off my credit card bill.

Of course these payments are automated, but the question is how to plan for them in advance within a Tiller budgeting workflow. With one transaction it may be easy to figure without any support from a tool, but when you have many different sized one-off payments to make on one or more cards over many months, it would be useful to be able to see the projected impact on the balance of my checking account and to plan for specific account transfers or estimate income requirements in order to maintain a positive checking account balance across the year.

I know I can look at the Balances sheet for current balances across accounts, and I know I can use the Accounts sheet to customize the Balances sheet. That is one part of the jigsaw, the “current” balances. However it doesn’t help with projecting the need I am describing across the year.

[soitmake]

I found this post which asks a similar question

[soitmake]

I found this sheet which seems to do something conceptually similar but runs off it’s own numbers rather than your budget numbers from your Categories sheet

[soitmake]

Something similar is being requested here

[heather]

@soitmake this is a good question. The projected balances sheet you found below is the closest thing available as of now.

[peter]

Thanks for writing in @soitmake Presuming you’re paying off those credit card balances each month, would it help to create a cash remaining calculation on your balances or net worth sheet, or elsewhere in your spreadsheet.

It could look something like this:

Primary checking account balance: $3,252 (from Balances sheet)
Credit card balance A: -$1,000 (from Balances sheet)
Credit card balance B: -$200 (from Balances sheet)
Cash remaining: $2,052 (calculated using above three rows)

This last number is basically the checking account balance minus the latest balances on each credit cards.

[1Email2RuleThemAll]

@soitmake, I’m currently working on a solution in Google Sheets that might be interesting to you, but it’s pretty involved and not quite ready for release just yet.

My solution takes a list of recurring multi-grain expenses and plots them against a series of paychecks (past, present, or future), to show when those expenses are due. It shows an expected (avg.) paycheck amount, budgeted expense amount, and the amount you should have left over from your check once those expenses are paid. There’s a whole other slew of cool stuff it does, though!

There is also an optional parameter to add in credit card totals to the equation. When selected, the current amount due on your credit card is split into (1) what is due on your next due date (prior statement expenses), and (2) what is due on the due date after that(current statement expenses). Those totals are then added to the appropriate pay period “lane” and calculation (shown in blue).

But that looks at projected paychecks, not checking account totals. In addition to the above, I created a ‘Home Page’ sheet that displays my current checking account totals, along with the amount that is available after taking into account all recurring expenses and credit card totals that are due by my next paycheck.

I’m not sure if it’ll be 100% what you’re looking for, but it’s been helping me with planning upcoming expense/credit card payments and keeping on track. There’s a few more things I have to do before my eventual “Show & Tell” of it, but I’m excited to eventually get there, release it, and hear from the community!

[morgan]

, @soitmake

Did any of these suggestions help? If so, please mark one as the solution.

[soitmake]

Thanks @Email2RuleThemAll

Reading your reply I can see that there are two goals embedded in my question. One is to ensure I have enough current account balance for anticipated expenses, including credit cards, which is useful to avoid cash crunches and potential fees etc. The other is to project current account balance across the course of a year, and this is useful for budgeting and scenario planning.

Your solution appears to deal with both of these at once, but does so in the context of a pay/spend cycle. My year is complex this year with varied shapes and sizes of income and expense, and so that cyclical pay/spend approach doesn’t seem to fit, although I imagine it will be useful for many people

I am curious how you are getting projected credit card balance projections? Are you copying them manually from statements or calculating them ahead of time from budget numbers? The latter is more useful for projecting across the year, whereas the former is more useful for near term accuracy.

[soitmake]

@peter thanks for the suggestion. I did make something like this but I quickly realized the shortcomings. Credit card statements are staggered so that you pay off the balance you accrued one month prior. So the current balance is no good for projecting the upcoming payment, you would have to go back to the relevant account period, and either get the value manually from a statement or calculate the value from your transactions. That’s doable.

And, as mentioned in my previous post, that only deals with near term. I’m now also thinking about projecting current account balance over several months or a year based on my budget. I guess you would have to allocate certain spending categories to specific cards and then keep an index of the cards statement period and due dates, and then you could project future balances from that. It wouldn’t be so hard to set that up, but it might be imprecise and maybe wouldn’t be valuable.

[1Email2RuleThemAll]

@soitmake: The solution that I posted is really like 5 solutions in one. But the great part about it is that each component is pretty modular, and can be used independently. I’m currently in the process of posting them all as separate solutions. You might not be able to use the Paycheck report, but based on what you replied to @peter, I believe two of the underlying mechanics that build that report could potentially be of use to you.

For credit cards, I’m dynamically calculating what is due on the next due date, and what will be due on the due date after that. I don’t project any further into the future (though I’m sure there’s a way using parts of my solution). I do this using my Credit Card Settings sheet. This sheet is stand-alone, easy to use, available now, and has complete instructions in the Show & Tell.

I calculate the Upcoming Total Due and total due next time (Statement Purchases) by using the prior statement close day and total Balance. You enter the statement close day for each card on this sheet, and I generate the Prior Close Date from that. Any transactions after that date will not be due on your next due date, but the due date after that. This will be the Statement Purchases total. I then calculate what you owe on your next due date by subtracting that total from the overall total of the card.

• Unfortunately, a credit card’s close day can change from month to month by a few days. For this to be accurate, you have to make sure the close day is correct each time a statement closes. And it would only be accurate for the current cycle. Some credit cards have the same close day every month (a big THANK YOU to those cards…), but a lot fluctuate this date.

My Recurring Expenses and Generated Expenses sheets/solution, which I will be releasing sometime in the near future, would allow you to project future expenses out over any period that you desire, and might be helpful for getting those future projection numbers you are wanting.

You would enter your expenses, their frequency (from a list of 10), and requested information on the first sheet (above). One of the requested metrics is the credit or checking account typically used to pay the expense, which would be good to calculate what you are asking. A bonus is that with the Credit Card Settings sheet from above installed: when you select a credit card as the payment method, the Master Pay Source will pull in the checking account name that is set to pay that credit card.

On the second sheet, Generated Expenses, you feed dates to the View Start and View End cells, and a dynamic list of expenses that are due between those dates will generate, with the correct due dates that correspond to the frequency chosen for that expense.

• This list is generated a certain way, and cannot be ordered/filtered, so viewing this sheet directly is not the best experience.
• The best thing to do is to set up a report sheet (like I did with the paycheck report above) that queries this sheet for the expense list, ordered by date.
• You can set the dates you want to look at directly into the View Start and View End cells, or (more preferably) in cells on the report sheet you build. You can then reference those cells in the View Start and View End cells. This is the preferable way to do it.

I will be including a report example when I release this solution. It’s incredibly easy to use.

This would give you a full list of expenses for whatever period you want (the entire year if you choose). Then, you can pull in your pay sources into the report, and do any number of things to compare that data.

[soitmake]

Thank you @1Email2RuleThemAll for sharing your solutions and for being willing to let others use and modify them. I started building something last night that checks off some of my requirements, and looking at your posts it seems we are treading some of the same ground.

I looked at your solution and you are doing a lot - right now I think I need to solve some problems that are more targeted and so I think it makes sense for me to continue making my own. My household income and expenses this year are unusually varied, so the need is to accurately project current account balance over time through all the flux, to predict high and low points and adjust plans to flatten those out, which is different from a monthly paycheck and burn down, as you have noted.

What I have done so far is modify the Accounts foundation sheet by adding two columns, statement period and due date. Then I created a new sheet called “Credit Due” which figures out for each card a) the current period dates b) the prior period dates. Next I plan to use these to pull appropriate sums from the Transactions sheet for each due date which will affect my current account balance.

I already created a row in the Yearly Budget sheet which pulls the balance from the current account row in the Balances sheet and displays / modifies it over the year based on transaction amounts directly. The next step will be to modify it to use the Credit Due values mentioned above instead of transactions directly.

The next step will be to tag the Categories sheet to show which transaction categories will be paid for by which card. And to project the next 12 months of category spending based on budgeted amounts.

One issue I see with this is that statement periods don’t line up neatly against the Categories budget month periods. I will look at it again later but there seem to be ways around this, e.g. 1) much budgeted spending is averaged and it doesn’t matter where the statement period starts or ends 2) where there are large expenses they can be budgeted based on the expectation of which statement period they will be incurred and/or 3) it seems you can change statement periods on credit cards online or by calling them, so I could just make them match the monthly cycle. I’ll look at that later.

If anyone has any thoughts on the above or suggestions its great to chat about it - thanks!

[1Email2RuleThemAll]

@soitmake: Yeah, there is definitely more than one way to tackle this problem. It seems like you’re going to modify the Categories sheet in a similar way to my Recurring Expenses sheet to hold which card/account will pay which expense. I initially had the same idea to use Categories directly, but there were two problems with that route for me:

1. Categories is at a monthly time grain. You cannot tell what part of the budget balance is due at any given part of the month. I just know that I have $x amount expected for a category this entire month.
   – This makes plotting them against any timeframe that is not month-to-month difficult.

2. Categories is (of course) at a category level grain. I may have one category, like Utilities, that covers multiple expenses like Water, Gas/Electricity, etc… I might pay Water with account A, and Gas/Electricity with Account B.

It seems like problem 1 above is also a problem for you. I’m not sure how to tackle it with your solution, though. When thinking over this problem for myself, I came to the conclusion that tying my projected budget to Categories kind of locks me into that ambiguous monthly amount, which I did not want. That’s when I started building out my Recurring Expenses sheet. After developing this sheet, I found something kind of similar that you should look into called Budget Plan by @jpfieber. Honestly, If I were building your solution: I would install this template, then add a column to it instead of categories to show which card covers what expense. That would give you a lot more flexibility with things.

Changing your credit card due date to better align everything in order to solve problem #1 is possible. But I believe there is a limit to how many times you can request that per card, it would be impractical, and a pain.

I also have need for the same report you’re talking about to track current checking balances and project the expected balance over the year based on my budgeted amounts/incomes. I’ve been planning on building out this report using my base solution sometime soon. When I complete it, I’ll give you a link in case you can use it, or just to skim over for ideas.

[annaisakiwi]

@1Email2RuleThemAll

Recurring Expenses and Generated Expenses sheets/solution,

love the look of this! very similar set up to a ‘budget smarter’ template i have been using - but it’s not linked to tiller. Do you know when you’ll have this released?

[1Email2RuleThemAll]

For anybody else wondering, I anticipate getting this solution out for Google Sheets in the near future. I would say within the next 2 weeks, if all goes to plan!

[randy]

Looking forward to it, @1Email2RuleThemAll.

[1Email2RuleThemAll]

It took a little longer than expected (due to vacation and some unforeseen issues at work), but I have finally released the workflow I spoke of above. I apologize for the long delay! If anybody is interested, it can be found at the below link:

Generated Recurring Expenses Workflow