How should investments be recognized on a balance sheet?

RESOLVED

I had a long Zoom call today with Jack and got all of his questions answered, plus some.

Blake

2 Likes

Blake goes above and beyond.

2 Likes

I’d love if there were a concise recap here for others who might come across this thread and have the same question :slight_smile: that’s the benefit of the community - the knowledge sharing - just so you don’t have to get in the habit of doing long zoom calls with every person who reads this. :wink:

Forever grateful for you @Blake!

4 Likes

@heather @jack

A written post and a written recap would take way too long to write up. That’s why we agreed to Zoom. I anticipated it would become a free flowing conversation which it did. We discussed many other topics not included in the post which I suspected would happen. I was not worried about my time investment. It took what it took. Tiller now has a new excited user.

Cheers,

Blake

The main thing that’s relevant to this thread is I wasn’t aware that when you purchase or sell an investment within a brokerage or retirement account that two entries are recorded. One with the cash leaving the account, one with the stock (or whatever) entering the account, both with equal and opposing values. I thought investments would litter my Transfers bucket with transactions that didn’t net to $0, so I got to thinking about different ways to categorize these transactions. No need.

Categorize the purchase of assets within brokerage or retirement accounts as Transfers.

(I’m building my setup now. I’ll post it in Show and Tell when it’s ready, and link it back here.)

1 Like

That is how I categorize my purchase of a stock - a transfer.

Category : Stock purchase
Group : Investments
Type : Transfer

3 Likes

Thank you for sharing :slight_smile:

I’m not seeing the equal and opposite values show up; just the inflow (for a sale) or outflow (for a purchase). Anything you did to ensure these both showed up automatically? I was about to go through and start adding (manually) all of the necessary offsetting transactions to ensure transfers do in fact net to zero, but then saw your post and would prefer to save the (significant) amount of time that would entail.

This might be a better question for @Blake as he’s the one who showed me how it’s done. Any thoughts?

@Blake thoughts would be greatly appreciated. I only see the outflow (for a purchase) or inflow (for a sale), not both. So unless there’s a way to start getting both automatically, or unless I add a manual transaction every time an automatically downloaded one occurs, I’ll end up with out-of-balance transfers in any period where an investment account transaction occurs. Wouldn’t be a big deal except for dividends/re-investments which occur frequently (I almost never trade, so it’s not actual buy/sell transactions I’m focused on).

It may be that that one side of the transaction doesn’t show up automatically due to the data feed and your specific institution. This could vary by institution, I don’t have enough data to say one way or another. I think if Jack is receiving them automatically it’s a feed issue, not a configuration of the sheet necessarily.

Blake has decided to move on from Tiller Money and is no longer actively participating here in the community.

No, still around, less engaged. Blake

2 Likes

I agree with Heather. If you’re not seeing the other side of the transaction it may be a feed issue.

I personally use Coda to track my finances. (Still not presentable yet, but I’ll post it when it is.) Not sure you might hack your way around this problem in Sheets without manually entering values. But in Coda you could create a view of the master table and run an automation that reads something like “every time a new row is created in this table, duplicate it and multiply the transaction value by -1.” Maybe there’s a similar formula you could run in Sheets?

I’m not familiar with Coda. But I don’t think it would be easy to create an automation in Sheets; too many conditions have to be met in order for it to be appropriate to add a row for the transfer offset.

Seems like the manual process is the only viable option, unless @heather you’ve got a better suggestion?

i suggest having many transfer categories, one called transfer - sales, one called transfer - purchases, one called transfer - dividends received, and one called transfer - reinvestment. neither each nor all transfer categories coded to type = transfer need to zero out. regarding the last two, this is what you will get when you have auto-reinvest turned on in your brokerage account. one will be positive and the other will be negative. you can use other transfer categories if you want to track gain/loss in these retirement accounts. you can do something similar to track gain/loss in taxable accounts which you will want to do because you will need this gain/loss for tax return purposes.

3 Likes

Thanks, @Blake. The idea of many transfer categories makes good sense to me and I like the ones you’ve suggested. I have a lot of others for things like real estate investments, private equity investments, etc., but all for accounts that don’t have automatic data feeds. Getting my public equity investment accounts (which do have data feeds) set up is what’s raising my questions/concerns on this topic.

I understand that neither each transfer category nor all need to zero out, but if they don’t then when I’m reviewing the transaction activity for a given time period it’s hard to know for sure that there aren’t any data errors in that period. You’re suggesting that I just let the imbalanced transfer categories remain imbalanced?

I was a Quicken user many years ago and always liked that it was basically accounting software with double-entry bookkeeping. I’ve always struggled with systems like Tiller or Mint that don’t try to do any form of reconciliation. Thoughts?

1 Like

an imbalance, as you call it, does not connote errors. your public equity accounts are no doubt unique. i am willing to discuss offline as we would need to get much more specific for me to advise further.

The imbalance itself is not an error, of course. But the benefit of double-entry bookkeeping in which transfers net to zero is that it makes it easier to identify when an error has occurred because an imbalance in transfers would imply that a transaction was missing or incorrect.

Connecting offline sounds good. Please let me know when and how you’re most easily available.

1 Like

This link appears to no longer exist but I’m very interested in it. Is there a newer version?

@jwvanderbeck, ah yes, it’s on the community now:

Using Google Sheets to Track Your Investment Portfolio