I’ll start with specific question first:
I transfer 100 dollars from bank account to investment account.
Transaction 1 shows up “-100” (ie - 100 dollars transferred from bank account)
Transaction 2 shows up “+100” (ie - 100 dollars deposited into investment account)
Which transaction would be the “transfer”? How do should I view transaction 2?
General…
How do you think about “transfers” in the overall scope of managing your finances/budget?
Thanks.
Hi @dudeguyman Welcome to the community!
The way I handle that is to categorize the deduction as “Savings” or “Investments” …something like that, where that category is an Expense and you’re tracking that in your budget. Then, where the deposit may show up in the download from your Investment account, categorize that as an “Investment Transaction,” where the type is, “Transfer” in your Categories sheet, and “Hidden.”
This keeps a record of your transactions, but the impact is seen as a savings deduction from your budget and an increase in your investment account balance. Here is another post with that discussion.
The accounting folks will tell you that your transfers should always sum to zero, to be sure all is being kept straight. (They’re usually right! 
) Here’s another post that talks about that.
An exception to this method is a credit card payment. In this instance, because I have already categorized elsewhere the individual transactions downloaded from the credit card as they arrived, I have created a category in my Category sheet called, “Credit Card Payment.” This is marked as “Transfer” and “Hidden.” Both the checking account deductions for the payments and the deposit of the payment downloaded from the credit card account are marked with this category. There is no budget planned for this category. It is simply a place to monitor, record, but sequester the credit card transactions that are, in fact, just transfers.
Does that help?
They are both transfers. Two sides to one transfer.
I would categorize the actual purchase of a stock, mutual fund, or whatever, as either deducting from savings or whatever as @Brad.warren suggested. The reason for the difference in my eyes is that until you actually spend that money, it’s still cash sitting in an account. You haven’t actually spent it yet and could just as easily transfer it right back out. Really, it’s the same concept as to how you handle credit cards. Transfers for the money moving between accounts, Expenses for the actual purchases.
Yes, this is great information. My question is answered.
For transfers like credit card payments and transfers between bank or investment accounts, I add some additional features to give me full visibility into these transfers. This way i’m able to make sure they match and cancel each other out.
I have an autogenerated column in my Transactions sheet that lists the Type of the transaction, such as Expense, Income or Transfer:
This formula is at the top of column R:
={"Type";ARRAYFORMULA(IF(ISBLANK(A2:A),IFERROR(1/0),VLOOKUP(C2:C,{Categories!A2:A,Categories!E2:E},2,FALSE)))}
In my Transaction sheet, column A = Date and in my Categories sheet, column A= Category and E = Type. You might need to adjust the formula if your columns are different.
When I see two matching transfer transactions, I add a “matched” keyword to the Tag column.
Then, in the entire sheet, I add a Conditional Format rule to the range:
A2:I. (I is my last column)
I use a Custom formula for the rule:
=AND($R2="Transfer",$H2<>"matched")
And I add a text background of yellow.
On the Transactions sheet, column R is my new Type column and H is the Tags column.
Now, any transfer transaction that doesn’t have a “matched” in the Tag column will show up in yellow.
It may take a few days for both sides of a transaction to appear. But if I see a row in yellow that’s a week old or so, I know I need to investigate if there is a problem.
Jon
I take a simpler approach, which might suit some folks better. I have a separate sheet with this formula:
=SUMIF(Transactions!E:E, “Transfer”, Transactions!F:F)
I have only one “Transfer” category, which I call “Transfer”, and the above sums the amounts for all transfers in my Transactions sheet.
If this value is ever not zero I can usually easily spot why in the most recent page of transactions. If not, I’ll filter the Transactions sheet down to just “Transfer” lines and look deeper.
I think it also depends on what you’re tracking and how you want to view it.
For example, I don’t view cash in my investment accounts as “spending money” available for regular cash flow purposes. For this reason I don’t track investments and regular cash flow in Tiller at all. From the point of view of my cash flow sheet, transfers in and out of investment accounts are income/expense events.
I think @Brad.warren’s approach also works, but if I were to use Tiller to track investment accounts I’d probably just use an entirely different spreadsheet for the investment accounts.
@matta,
That’s a good, simple and quick solution. Thanks for sharing it.
Plus the SUMIF value will give you the amount that it is off by. That should be it easier to identify the potential issue.
Thanks, true, but your solution is fully general and a little bit nicer for that reason. Sometimes it is nice to see a simple approach that might be imperfect, and the full solution that is more complete, side by side.
Full disclosure, I’m a software engineer of 30 years that still can’t wrap my head around spreadsheet programming. I am so used to striving to make my software easy for other programmers to understand that I have an almost physical aversion to most spreadsheet formulas, which I find almost incomprehensible without a serious time investment. (I wish Google Sheets had more complete support for named ranges, which I think might help a lot. That, and a much easier way to put comments and multi-line formulas within cells.)
@matta, I completely agree. Recognizing that Tiller doesn’t yet offer a robust investment management tool, I am minimizing the work of reconciling the investment account transactions.
When an investment tool comes along, it makes so much sense to address these with a more sophisticated method.
Awesome insights! Thanks,
Transaction1 is a transfer out and Transaction 2 is a transfer in. Both net to zero. You have moved money from your right pocket to your left pocket. Transfers result in no change to your wealth. Income and expenses change wealth. Any meaningful investment tracking should occur outside Tiller. Cheers.
Hi @Blake:
From a global, whole-system, wealth perspective this makes total sense, and I love your right/left pocket analogy: very clear.
Wondering, however, from a cash flow perspective of monthly cash into my plan and monthly cash out of my plan, if I categorize my savings or investments (cash flow out) as a transfer, it seems to overstate my available resources when those transfers are not categorized as an expense.
For example, in my Categories sheet, if I change my Savings category from Expense to Transfer, the budgeted, available cash flow for the time period showing up in the Yearly Budget sheet is increased by the sum of my planned savings in the Savings Category.
In my case, this could lead me to believe I could wrongly (and sadly!) allocate those same funds at least twice, once in a transfer to savings and then again on my new Apple Watch when I see the funds transferred to Savings remaining in my annual plan for other purposes.
I could be way off. What do you think?
(And be careful…my Apple Watch is riding on this!)
I do not use budgets so do not know how those would be impacted in Tiller.
What I do is tag the positive number ($100 in this case) as the transfer in the transaction ledger and the negative number (-$100) tagged as Savings or whatever other category applies in the transaction ledger. I also choose the Hide Transfer so it doesn’t show up on reports.
Works for me.
I have been thinking more about this.
If both the source of the savings (checking account) and retirement or taxable account are linked in Tiller and both are coded as a transfer, won’t this solve your yearly budget problems. Call one category (savings out) and the other category (savings in). If the Tiller budget tools require an expense classification to the savings out, then the saving in will be an income classification. All you are doing is moving money from the left pocket to the right pocket, right? Both are transfers. But if one needs to be expense, make the other income.
Thoughts?
Ha! @Blake… You’ve discovered my secret…
Sometimes the balance in my checking account will drop to a point below my comfort level, and so to make sure I don’t bounce checks or have debits refused, I will temporarily “borrow” money from my savings account and move it to my checking account to prop up the checking balance temporarily.
When the balance stabilizes, I will then repay the loan from by savings account by moving the borrowed funds back to savings.
I have two categories that manage these transactions: Savings Cash Flow In (+) (Type=Income) and Savings Cash Flow Out (-) (Type =Expense). For appropriate fiscal discipline I make sure that at the end of the year, the sum of activity in these two categories equals zero. (I create the budget for cash flow in after the fact. I then immediately add a budget item in the future to repay the loan, Cash Flow out.
Some may say that those two categories could easily be flagged as “Transfer,” and that would be effective, too.
I don’t flag these with the Type “Transfer,” because I need the discipline to see these funds as a temporary supplement to my annual spending plan. I need to be reminded that the plan was made to float with money that is tagged for other purposes (so…I have to put it back!)
That’s how I handle actual transfers from among the “pockets” not associated with any transactions.
But when I actually work to save money each month, I use a different category called, “Savings” with the Type, “Expense.” I do this because to me saying, “Yes” to savings means saying, “No” to spending: I need to see savings as an expense in my plan that limits my buying behavior elsewhere. That’s why when preparing our budget, I start with our top, lifetime priorities. Charity and Savings are first and second, and with those accounted for, we plan for and live on what’s left.
Thanks for your kind dialogue. It has refined my thinking on this.
@Brad.warren your approach makes perfect sense to me. I think the “best” way to categorize transactions depends entirely on the kinds of queries/reports you want on the data.
Just an FYI, if you copy paste your formula, you need to fix the quotes, it’s the wrong type of double quotes.
What I ended up doing was the sumif in A1 and then brought in the transaction sheet via arrayformula and then hid the columns I don’t want and did a filter on only transfers. (I did it this way, rather than customize every column because I think it’s faster and less resource intensive to have one column with arrayformula and then hide those I don’t want rather than have an arrayformula in every column I want, skipping those I don’t want.)
I also have the Google Scripts installed that was mentioned elsewhere that allows me to have a little dashboard, and have the sumif as one of my boxes showing red/green and the dollar amount of the transfers.
Saw this thread and got inspired to do this type of matching in my setup. I used AutoCat to re-categorize some of my transfers to “Transfer In” and “Transfer Out”. I know I could stick with Transfer and look for + and - values, but I’ve used “Transfer” for a few other types of transactions that don’t always have a clear match (need to clean some of that up), so this is a way of calling out the ones that always should have a match. I then used my Tag column to add a “Match” tag to any matches. Finally, I used Conditional Formatting to call out any of those that were older than a week and didn’t have a match. Here’s the formula I used that allows it to work with multiple tags:
A = Date, C=Category, E=Tags
=AND($A2<(Today()-7),OR(AND($C2=“Transfer In”,NOT(REGEXMATCH($E2,“Match”))),AND($C2=“Transfer Out”,NOT(REGEXMATCH($E2,“Match”)))))
Thanks for the inspiration!