Remove account from Balances, but retain data in Net Worth

Spreadsheets Google Sheets
, ,
1

Good morning!

I recently refinanced my Mortgage, and did so shortly after my original Mortgage company sold my loan to a new servicer.

So I now have 3 Mortgages in Tiller, 2 of which have a zero balance on my balances tab. I’d like to remove them from there, but retain the transaction data, particularly for my net worth calculation over time.

If I hide them on the Accounts tab, it removes the data from everywhere, including net worth.

Is there another way to do that?

Thanks!

2

Hi @crachau,

There isn’t a way to hide it from one place, but not every place.

I wonder if @randy has ideas on how to customize the formulas in the Balances sheet to not show accounts that have a $0 balance and could share a revised formula here.

It’s a good use case, but without adding some extra column to the Accounts sheet, I’m not sure how else to pull it off.

3

Well that was easier than I thought…

Just open up the hidden area on the right of the Balances template and add INDIRECT(Y16)<>0 into the filters for the Asset and Liability queries.

Final formulas in K3 and R3 should look like:

=iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Asset",indirect(Y18)<>"Hide",indirect(Y16)<>0),2,true, 4, true, 3, true))

=iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Liability",indirect(Y18)<>"Hide",indirect(Y16)<>0),2,true, 4, true, 3, true))

Best,
Randy

4

Well that worked perfect, thank you so much!!

5

This is something that always bothered me but never got around to asking. Thanks for the solution!

6

Is there a way to get that behavior into the base sheet? I don’t like modifying sheets provided by Tiller.

7

This is really nice update – hope it can be added to the “out of the box” solution, or maybe configurable by in the Accounts sheet as I could see where some might want a zero balance to show (eg: current credit card – nice to see you have it and it is at zero)

8

This is probably the single greatest item I have updated my balance sheet with – to now hide zero balances but still have them tracked. As accounts would shift (either a replacement credit card, or change in investments) I was finding myself going into the Balance History and Transaction History to update the OLD account with the new account id, so they would show the same. I have to remember this change was made if there is ever an update to the balance sheet so I can re-implement it (unless maybe there will be a setup option to enable this new way for those of us that like it and leave existing functionality for those that don’t).

9

I’m having a similar problem. I recently changed my checking account in the same institution, closing out the old one and transferring the balance. The old checking account shows up twice in Ungrouped assets but not in Balances. Also I renamed a savings account. The old name also shows up in Ungrouped Assets.

I tried to find your solution above, but I don’t see anything like those formulae in the Balances sheet.

10

Same problem here. I see double in Net Worth. How can I remove account duplicates?

11

Here is our guide on removing duplicated accounts:

You can also just mark one as “hide” on the Accounts sheet in the Hide from Reports column

12

Good solution, with the caveat that INDIRECT is a volatile function and could slow things down.

13

Do you have a similar solution for the Excel version, which uses CHOOSE instead of INDIRECT?

Thanks.

14

This worked beautifully. Thank you.

15

Hello, I am confused how to turn off an account in Net Worth for the current mont, but still have it show up in the prior months. The account although closed still shows up with the amount. I can hide this account but it then hides it in the previous months and so the Net Worth month to month comparison is not correct. I am using excel.

16

I think you need to enter a new line on your balance history sheet with a zero. That is what I had to do on some of my accounts when they closed as the final balance didn’t come in from the bank when the account closed.