Project Future Account Balances Using Manual Transaction and Statements Features

What is the goal of your workflow? What problem does it solve, or how does it help you?

I used to use YNAB 4 mostly because it was really easy to model account balances based on future transactions. I knew I needed to find a way to duplicate this functionality in Tiller and was able to edit the “Statements” sheets to meet my needs.

My goal is to see what my account balance will be on future dates based on the automatic deductions I expect. I mostly do day-to-day spending on credit cards, so this is to monitor paychecks vs big transactions like credit card payments and mortgage payments coming out of my checking account. You can also use it to model loan balances into the future.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

The workflow uses an altered version of the “Statement Details” tab and the normal version of the “Statements” tab. I also use the tags column in the Transactions tab to indicate pending transactions. There are no custom scripts and it is very easy to set up.

First, you want to add your statements as usual for the account you want to track and assign the correct transactions to the statement. Then add a few manual entries for future dates. Select the right account on the “Account Projections” tab and you have a running balance and chart for the future dates. Use the tags “pending” or “placeholder” to highlight manual transactions in blue background/italics.

The running balance is green when it matches the current balance from the “Balance History”, red when less than zero, and yellow when less than 300 (but you can change that based on what you want your minimum desired balance to be). The Current Balance and Minimum Balance are at the top of the page.

Is it ok for others to copy, use, and modify your workflow?

Yes, please do! And let me know if you make it even better!

If you already use the “Statements” sheet from Tiller Labs, just copy in the “Account Projections” sheet.

If you do not already use the “Statements” sheet from Tiller Labs, I recommend adding the “Statements” and “Statement Details” sheets from the Labs add-on before copying in my “Account Projections” sheet.

If you said yes above, please make a copy of your workflow and share the copy’s URL:

https://docs.google.com/spreadsheets/d/13x-lGXbaiK4KwtQDcOZSaqZA7XueFwD6yEQdr2wKcoI/template/preview

Here is a screenshot of the “Account Projections” sheet:

This is fantastic! I love the idea of projecting what account balances based on automatic deductions. Very clever. I’m going to add this to my Foundation Template.

1 Like

Great! Let me know if you run into any issues.

1 Like

So far so good! I’m including this in the Tiller Money Memo this Thursday.

Awesome! Thanks for sharing it in the memo!

I have a similar tool in Excel. Since I am projecting years in advance, I keep my historical transactions on 1 tab and then my projections on a second tab. I even include functionality that will project future interest and credit card payments. They are not perfect but they do a great job of building projections of debt reductions with the ability of adding additional payments. It has become an excellent source of budgeting paycheck to paycheck verses the standard monthly templates seen in most financial tools including many Tiller templates.

1 Like

Are you willing to share the excel version? Would appreciate if you can share it.

A. Need to clean it up to remove personal data
B. I am working to more automation to it. I am new to Tiller so trying to figure some details out.

Hi @charlotte,
Bravo. That is very clever what you did to revise the Statement Details sheet to work for projecting future account balances.

I’m the one who proposed and built the Statement Details sheet so I’m even more impressed by how you are using it. I must admit, I didn’t realize this application for it, but I’m glad you figured it out and it works for you and hopefully others.

Can you share, briefly, what changes you made to the Statement Details sheet to make it work?

I see you add the placeholder Transactions directly to the Transactions sheet. When they are assigned to a Statement, they will automatically go to that Statement Details sheet.

I assume when the actual transaction happens, you just delete the placeholder, which will be near the actual transaction since Transactions are sorted by date.

You could add a conditional format to the range B2:D with the custom formula =$K2="placeholder" to highlight placeholder tagged transactions in the Transaction sheet.

Do you have a trick to entering these placeholder transactions? I guess you just copy and paste them several times and change the dates.

It looks like you could move the chart in Column M over to Column H and then hide the calculation columns all on the right. That way there aren’t hidden columns in the middle of the sheet. I tested it and none of the formulas broken for me when I moved the chart over.

Again, kudos for doing this.
Jon

1 Like

Thanks Jon! I appreciate the kind words.

I did add the placeholder transactions directly to the transaction sheet. I typically just copied whichever transaction I wanted to repeat multiple times and changed the dates and added the tag. I’ve since started using the “Manual Transaction Reconcile” option to remove the duplicates, that wasn’t a feature when I originally built this.

I agree, you can use conditional formatting on the transaction sheets instead of my manual highlighting. I use different colors for different types of transactions, so I just adding the colors I wanted manually.

Good idea on moving the chart over, I’ve updated that.

I tried to edit as little as possible on the sheet. My basic edits were to use the “Unreconciled Transactions” view and make a few edits from there.

  • Added Tags and Month as columns by editing cell A11
  • Removed the code from A11 for Reconciled Accounts since I didn’t need it (and the info in B3/B4)
  • Added a filter by date in A11 too, since I originally only wanted the next two months (and still do for some of my accounts, other accounts I use all future transactions)
  • Added the Current and Minimum Balances to the top of the sheet
  • Added the conditional formatting
  • Added the chart
  • Edited A9 to include count of pending transactions

Charlotte

Thanks for sharing the details @charlotte.
What you did makes a lot of sense.
We enjoy seeing how people modify and personalize their own sheets for their individual needs.

That’s one of the great thing about spreadsheets.

Jon

1 Like