Joint Accounts and Net Worth

[DarkWater]

Hi. I’ve been using Tiller for all of two days, so please forgive me if my questions are naive and uninformed. In any case, so far Tiller is awesome for me, modulo some nits.

My first nit is that I am the trustee of a trust in which I own 50%. I’d like to track transactions in the trust account using Tiller, but there are some worries about doing that. E.g., I only want half of the value of the trust to count towards my net worth.

I’m guessing that when I distribute money out of the trust to the beneficiaries of the trust (i.e., my brother and me), I can split the money transferred out so that the funds that are transferred to me are categorized as “Transfer” of type “Expense”, and that the funds that are transferred to my brother are categorized as “Distribution” of type “Transfer” (or some such).

I’m pretty sure that this will work just fine, so the only remaining question is how do I get my “Balances” sheet to only include half of the value of the trust account in my net worth?

Well, there is one more snafu: For tracking tax burdens, if I AutoCat income in the trust account into some category that is designed to denote income that I have to pay taxes on, I’d want to also auto-split the income into my share of the income and my brother’s share. Is something like that possible?

And, as a tangentially related question, I have a lot of “net worth” that is in IRAs and 401ks, etc. While I want this money to count towards my net worth, because I haven’t yet paid taxes on it, I want to discount it when applied to my net worth by like 35%, or what have you.

I.e., since when I actually take money out of the tax-deferred accounts, this transfer comes with a big tax burden, I don’t want those taxes that I’m going to have to pay to be included in my net worth, so I’d like to multiply the money in the tax deferred accounts by 0.65, or so, when calculating my net worth. (I understand that I don’t know what my precise tax burden will be until it comes, but I’m happy enough to get in what I feel is the right ballpark.)

Thanks in advance!

[Rebecca.S]

Hi @DarkWater,

Welcome to Tiller. I’m in my 13th month of using it and am very happy with its functionality.

Perhaps you could split the trust distributions into two transactions, yours and your brother’s (using Extensions>Tiller Money Feeds>Split Transaction). You could use a Category of Trust Fund, Group = Trust or Investments (or whatever you like,) Type = Transfer, and Hide from Reports for your brother’s share so those transactions are hidden from your Budget sheet. I don’t know if that will also hide them from your Balances sheet and exclude them from your net worth because I’m not in that situation; you might have to play around with it a bit or someone else here can answer.

You could utilize Tags and the Tags Report to further differentiate between your and your brother’s trust distributions. This document is useful: Docs: Tags Report for Transaction Tagging

I hope this is helpful. I don’t have the expertise to address your other concerns, but I’m sure that others here will be able to offer suggestions.

Good luck!
Rebecca

[DarkWater]

Hi Rebecca,

Thanks. Yes, I think that splitting distributions will work well you as you suggest. I’m still left, however, with how to solve the problem of wanting to multiply the value of the trust account by 0.5 in calculating my net worth, and also doing the same for pre-distribution taxable income in the account.

I suppose that tracking taxable income is difficult in other ways too. I would need several different categories. At least one for income that is taxed at normal rates and one that is taxed at capital gains rates, so adding more categories to solve this issue I guess is no biggie. Except that I’m not even sure how it could be done for capital gains, since I don’t see anything that I know of for dealing with capital gains on stock sales, etc.

I suppose that one could split a stock sale into two categories: one that represents the cost basis and one that represents the gain. But unless there is some standard way of dealing with this, tracking my tax burden using Tiller seems like an uphill battle.

[KyleT]

Hi @DarkWater - I can offer an idea for this part of your question-

@DarkWater

how do I get my “Balances” sheet to only include half of the value of the trust account in my net worth?

If you’re open to some modifications of your Balances sheet (not Balance History), you could display adjusted values for certain accounts in two ways.

By the way, it may be a good idea to start by making a copy of the Balances sheet to test the changes and keep the original untouched.

1. The simplest approach if you only care about seeing the final Net Worth number reflecting the reduced levels of one or two subaccounts would be to put an adjusted Net Worth figure in cell G5 as in the suggestion in this thread.

https://community.tillerhq.com/t/donor-advised-fund-on-balances-sheet-neither-assert-nor-liability-how-do-people-categorize-it/20559/2?u=kylet

2. A slightly more involved (but not really THAT involved) way that would allow you to see the changes at the individual account level and easily make future adjustments would be to add an adjustment table to the far right as in my example screenshot. Put the Account ID (like those in Column K) and your adjustment factor.

Then starting in cell B9, replace the formula with:

=LET(newbal, MAP($K$3:$K,$O$3:$O,LAMBDA(acctid,bal,bal*IFNA(INDEX($AB$3:$AB,MATCH(acctid,$AA$3:$AA,0),1),1))), newlookup, {$J$3:$J,$M$3:$M,$N$3:$N,newbal},
iferror(if(iserror(match(row($B9)-row($B$9),$J$3:$J,0)),if(iserror(match(row($B9)-row($B$9)+1,$J$3:$J,0)),{iferror(1/0),iferror(1/0),iferror(1/0)},{upper(vlookup(row(B9)-row(B$9)+1,$J$3:$L,3,false)),"Updated",sumproduct(($L$3:$L=vlookup(row($B9)-row($B$9)+1,$J$3:$L,3,false))*newbal)}),arrayformula(vlookup(row($B9)-row($B$9),newlookup,{2,3,4},false)))))

Drag the formula just entered in B9 the whole way down to the bottom of column B - or highlight B9 to B100 and use the keyboard command Ctrl-D for Windows, Cmd-D for Mac.

This just takes the values you entered into the table and makes a modified balance for the associated accounts that the original formula can use as it’s new lookup. I think this is a clean way to approach it because you don’t mess with any core sheets like Accounts or Balance History. You’re just telling it that for this report you want an adjustment to the Balances according to your adjustment factor table.

You could add a column to your table with the Account Name since the IDs won’t mean much at first glance. However I think using IDs as a primary reference is important because names can obviously change.

Make sure that the numbers look correct as I didn’t extensively test it. And something similar would need to be done on the Liability side from cell F9 down if you adjust any liability accounts.

If you need any help implementing it I’d be happy to assist.

[DarkWater]

Hi, @KyleT.

Thanks so much! That works! Kind of.

It only works for my “Main” asset group. I don’t know how to make it work for my “Retirement” asset group. And also, even though it looks as if it correctly adjusted the subtotal for “Main”, the total under “Assets” in D7 decreased, but by some mystery amount. It didn’t decrease the full amount that it should have.

I’m a software engineer, so I’m not a complete idiot, but I’ve never programmed a spreadsheet before, and it turns out that the formula you provided is Greek to me.

I figured that I might be able to also paste it into the cell that shows “Retirement” in blue, but that doesn’t work. It ends up just turning that cell into a copy of the blue “Main” asset subtotal cell.

Thanks in advance for any help you can provide this spreadsheet ignoramus with a more turnkey solution.

[KyleT]

Just to clarify, did you replace the formula in B9 and then drag that formula from B9 down to B10 and to the bottom to B100? I realized after reading my response that I wasn’t explicit about that and just want to make sure that you updated all of those formulas. This should take care of the asset side. I could come up with the liability formula if needed, but it would be good to verify the asset side works for you first.

[DarkWater]

No, I didn’t even know that that dragging thing was a thing. But now that I’ve done it, it seems to work! Awesome! Thanks!!!

I don’t personally need the same thing for any liabilities.

Thanks again!

P.S. I doesn’t really make sense to me that dragging the formula results in something completely different from copy-and-pasting the formula, but I guess it’s time for me to learn how to use spreadsheets properly…

[DarkWater]

Hi @KyleT,

Thanks again for your previous help. Perhaps you could offer some more assistance if it turns out to be a relatively easy problem. I found via this community link, a sheet that plots net worth over time:

Net Worth over time in streamgraph form?

Hi all, I’m looking at a streamgraph style plot for net worth, see this plot for a random example of a streamgraph: The net worth streamgraph would have time on the horizontal axis and show total net worth along with the various accounts that sum up to it, each in its own color. Has anyone done something like this? Thanks!

I copied the sheet provided in this thread to my Tiller spreadsheet, and it works! But unfortunately, it doesn’t use the adjustment multipliers that are now in my Balances sheet, thanks to you.

I have zero knowledge about how this net worth chart works, so I have no hope of solving this on my own. (Until I learn a LOT more about Google Sheets.) If it could be made to automatically pick up the adjustment multipliers from my new Balances sheet, that would be incredible. OTOH, if I have to duplicate the multipliers in this net worth chart sheet, that’s fine too.

Any help would be muchly appreciated! Thanks!

(Though perhaps it would be better to ask the person who implemented this net worth chart, since I’m sure that he must have intimate knowledge of how it works.)

[KyleT]

Hi @DarkWater - I spent a couple minutes taking a look and I’m not so sure it’ll be easy for me to give a simple guidance on that one. Something could likely be made to work but it would take a good bit more time to investigate. I saw that you inquired in the other post which is a great idea.

There are at least one or two things on first glance that I’m not sure about. The streamgraph is a new concept for me and at first glance looks a lot like a stacked area chart which is what exists in the regular Net Worth template - albeit that one may not have the daily timeframe granularity. The solution from the post doesn’t appear to reference account IDs (but rather names), or at least I can’t tell for sure, because the Balance History sheet columns used in the main formula are hardcoded and mine don’t seem to all match with the columns that are meant to be pulled. My version of Balance History might be older or I may have inserted something that causes that misalignment. There is a part of the main formula that assembles balances and seems like it should be possible to use the adjustment multipliers, but I couldn’t say for sure until getting my version running and testing it out.

[DarkWater]

Thanks @KyleT!

I didn’t know about the Net Worth sheet, but I just found it and added it to my Tiller spreadsheet. That would be good enough for me too, if not quite as nice as the Net Worth Chart sheet. But the Net Worth sheet also doesn’t use the adjustment multipliers from your mods to the Balances sheet.

[KyleT]

For the Net Worth sheet, you can use the adjustment multipliers by making one cell change and dragging the formula adjustment to the rest of the associated table.

Start at cell AJ7, which should be in the section called “Data for Trends Table”. It should be the first row and first column of the individual account balances (these are the same balances that get finally displayed on the report to the left). You’re going to insert an XLOOKUP at the end of this formula to locate your adjustment factor, if it exists, and multiply the balance by that factor. If it doesn’t exist, the balance simply gets multiplied by 1 and thus stays the same.

This change considers that your adjustment factors are in a table in the Balances sheet in columns AA (account ID) and AB (adjustment factor), starting in Row 3. If the table is located elsewhere, you’ll just need to change the XLOOKUP range references. However, the $ symbols that lock columns and rows need to be exactly as they are set-up in the formula so that when dragging the formula to other cells, the absolute references remain intact.

AJ7 Formula BEFORE:

=if(or(isblank($AE7),not(isdate(AJ$2)),today()<AI$2),iferror(1/0),iferror(index(filter(INDIRECT("'"&$AW$2&"'!$"&$AX$4&"$2:$"&$AX$4), INDIRECT("'"&$AW$2&"'!$"&$AX$5&"$2:$"&$AX$5) < AJ$2+1,INDIRECT("'"&$AW$2&"'!$"&$AX$7&"$2:$"&$AX$7) = $AE7),1),"-"))

Inserted part - you’ll copy and paste this into the existing formula right before the last comma:

*IFNA(XLOOKUP($AE7,Balances!$AA$3:$AA,Balances!$AB$3:$AB),1)

AJ7 Formula AFTER:

=if(or(isblank($AE7),not(isdate(AJ$2)),today()<AI$2),iferror(1/0),iferror(index(filter(INDIRECT("'"&$AW$2&"'!$"&$AX$4&"$2:$"&$AX$4), INDIRECT("'"&$AW$2&"'!$"&$AX$5&"$2:$"&$AX$5) < AJ$2+1,INDIRECT("'"&$AW$2&"'!$"&$AX$7&"$2:$"&$AX$7) = $AE7),1)*IFNA(XLOOKUP($AE7,Balances!$AA$3:$AA,Balances!$AB$3:$AB),1),"-"))

Finally you’ll drag the formula from AJ7 down to the bottom of column AJ7. Then drag it one cell to the right into AK7. Then drag the formula from AK7 down to the bottom of column AK7. Repeat this procedure across and down through column AU.

[DarkWater]

Thanks so much, @KyleT! That seems to work! Though I don’t have much data history yet, it worked for what little I have.

In order to save some work, can this be done with a single drag across all the columns (i.e., drag from AK7 all the way to AU7), followed by another single big drag of AK7-AU7 down all of the rows?

I did this, and spot checked the formulas in a number of cells. They look okay as far as I can tell. But that’s not saying much, so for all I know, what I did isn’t right at all.

Thanks again!

[KyleT]

Yes, good idea - that should be an acceptable alternative and faster way to do the dragging of the formulas!

[DarkWater]

Awesome. Thanks, @KyleT, for all your help!