Project Future Account Balances Using Manual Transaction and Statements Features

Google Sheets Show & Tell
,
1

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:

image
image1703×710 143 KB

2

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
3

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

1 Like
4

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

5

Awesome! Thanks for sharing it in the memo!

6

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.

2 Likes
7

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

2 Likes
8

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.

1 Like
9

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
10

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

11

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
12

Hello Charlotte. I found your solution today and pasted the sheet into my Foundation Template. Unfortunately the formula in cell A11 gives me the following error :

Error Function ARRAY_ROW parameter 7 has mismatched row size. Expected: 84. Actual: 1

I have both Statements and Statement Details installed. Do you have any suggestions?
Thank you
Nelson

13

Ah I think that would happen if you don’t have a “Tags” column on your transactions sheet. You can either add a “Tags” column or copy over the new tab from my template called “Account Projections (no tags)”.

Thanks for trying it out!
Charlotte

14

Thank you Charlotte!

1 Like
15

Hi Charlotte. You helped me with your sheet about a month ago. I still had some issues but let the 30 or so days pass to see if they continued.

I have an account that closes in the middle of each month. It appears that the Projected Balances sheet drops unreconciled transactions from the past month after I have reconciled that months statement for the account. Specifically, I saw transactions for Nov that were unreconciled (correctly), but after I reconciled the Nov statement this morning those remaining unreconciled transactions disappeared. The only ones I currently see in the Projected Balances sheet are from Dec of this year.

Any ideas?
Thank you
Nelson

16

Hi Nelson,

Thanks for pointing that out. I wasn’t using it for any accounts that ended mid-month, so it wasn’t set up for that correctly. I’ve corrected the template or you can make an update directly in the affected cells:

On “Account Projections”, change K30 to =text(date(year(K29),month(K29),1),“yyyy-MM-DD”)

Or on “Account Projections (no tags)”, change J30 to =text(date(year(J29),month(J29),1),“yyyy-MM-DD”)

Thanks!
Charlotte

17

Hi Charlotte

That didn’t work for me either, but since you gave me a pointer to the appropriate cell I played around a little bit and came up with this:

=text(if(day(J29)=1,date(year(J29),month(J29),1),date(year(J29+1),month(J29+1),1)),“yyyy-MM-DD”)

It seems to work. Hope this makes sense to you.
Thank you for your help
Nelson

18

Hi @charlotte I’ve just come across this - and really like the look of it.

I’ve already got the Statements and Statement details sheets, so copied the Accounts projections sheet in.

However I’m just seeing a #N/A in cell A11 - ’ Query completed with an empty output’

Any ideas? were there changes I need to do to the statement details sheet to make it work?

Thanks !

19

Hey @Edward, maybe you can help me? really wanting to get this working - but all i’m getting is an error

@jono - maybe you might be able to help too ?

20

@annaisakiwi I believe the statements sheet has an additional column on it now that wasn’t there when I created this. I’ll take a look and see if that’s the issue.