Suggestions on balancing transfers?

Google Sheets
1

I’m not sure if this is just not how people use Tiller, but one thing I thought could be interesting was to have a way of checking that transfers balance. Currently my solution is that I’ve added a tickbox column to the Transactions sheet, which is conditionally formatted to highlight a row that is a transfer category that is not ticked. If I verify that e.g. a bank debit and a credit card payment match, I tick them both and they are no longer highlighted. However, this does not keep track of which payments match, which doesn’t matter most of the time but can matter if there are larger gaps between the two sides of the transfer, or if the matching is not 1:1.

My idea is to do something like keep track of a “transfer group ID”, and then I could have a sheet that filters based on transfer group ID and verifies that their totals add up to 0. It could also highlight transfers that are not matched. I’m not totally sure how useful this is likely to be though, and I’m wondering if anyone else has decided to roll with anything like this. Very open to suggestions for ways in which to implement this in a template: just using numbers doesn’t seem ideal since you won’t easily know which is the next unused number. I could have a formula to tell me what the next number is, I suppose…

I realize it is not strictly necessary to balance transfers like this, and if you are already doing some kind of reconciliation it’s probably not necessary, but it gives some peace of mind that things actually add up?

2

Give :trophy: Linked Transfers - Google Sheets / Show & Tell - Tiller Community (tillerhq.com) a try.

3

Oh, this is very cool. I don’t know how I missed it in the templates. I’m going to see if I can edit it to work with any of the Transfer categories that I have, rather than just an In or Out, which I think should just be modifying the query? It also doesn’t work super well for 1:many transfers, which are kind of a separate thing certainly but come up for me with e.g. marking investments as transfers.

4

The documentation at the top of the post should cover it all, but you should be able to open the settings section and select which of your categories to use. I’m not familiar with how a transfer could be split, seems it would then be considered multiple transfers, but yea, it’s not built to handle that.

5

Ok, I got my 1:1 transfers set up with the sheet, super helpful. I added a comment over there with the changes I made. I think the main reason I wanted multiple linked transfers that is not covered easily by doing a bunch of split transactions is for covering investments. I had been intending to set things up using this method. So for a simple brokerage with one index fund giving dividends that are reinvested, it would look something like this:

Account Description Category Amount
Bank Withdrawal to brokerage Transfer Out -100
Brokerage Deposit from bank Transfer In 100
Brokerage Stock Purchase Transfer Out -100
Brokerage Dividend Income 5
Brokerage Reinvestment Transfer Out -5
Brokerage Dividend Income 5
Brokerage Reinvestment Transfer Out -5
Brokerage Stock Sale (split 1) Transfer In 110
Brokerage Stock Sale (split 2, capital gain if any) Income 10
Brokerage Withdrawal from brokerage Transfer Out -120
Bank Deposit to bank Transfer In 120

So this set up at the point of a stock sale generates a transfer recording the total of all investments made into the stock, and a split for any gain or loss. It neatly separates dividend/interest income from capital gains, and accurately notes that the real income is $20, and records it at the time that it happens. However, this setup does not play nicely with the linked transfers because I am not sure that I want to have to split the 110 transfer out into dozens of splits, so that each reinvestment transfer can be linked. You are also sitting with unlinked transfers out for a long time since they can’t be relinked until you sell.

One alternative (I think) is do something like this, which ensures that transfers always happen next to each other and are 1:1, but is somewhat abusing the notion of what a transfer is, since I’m not sure it’s fair to say that the dividend and the reinvestment are actually “linked”:

Account Description Category Amount
Bank Withdrawal to brokerage Transfer Out -100
Brokerage Deposit from bank Transfer In 100
Brokerage Stock Purchase Expense -100
Brokerage Dividend Transfer In 5
Brokerage Reinvestment Transfer Out -5
Brokerage Dividend Transfer In 5
Brokerage Reinvestment Transfer Out -5
Brokerage Stock Sale (split 1) Income 100
Brokerage Stock Sale (split 2, dividends) Income 10
Brokerage Stock Sale (split 3, capital gain if any) Income 10
Brokerage Withdrawal from brokerage Transfer Out -120
Bank Deposit to bank Transfer In 120

Or even something like this that opts to completely defers thinking about everything inside the brokerage:

Account Description Category Amount
Bank Withdrawal to brokerage Expense -100
Brokerage Deposit from bank Transfer In 100
Brokerage Stock Purchase Transfer Out -100
Brokerage Dividend Transfer In 5
Brokerage Reinvestment Transfer Out -5
Brokerage Dividend Transfer In 5
Brokerage Reinvestment Transfer Out -5
Brokerage Stock Sale Transfer In 120
Brokerage Withdrawal from brokerage Transfer Out -120
Bank Deposit to bank (split 1) Income 100
Bank Deposit to bank (dividends) Income 10
Bank Deposit to bank (capital gain) Income 10

This kinda got off topic but hopefully it explains why I wanted to use transfers in a less 1:1 or time-separated way. There are other small use cases, e.g. with a splitwise account where I make one actual payment that is registered as multiple transfers, but those are easier/less of a pain to solve with splits.

1 Like