New Year, new spreadsheet for tracking joint and personal expenses

January 12, 2016 9:29 AM

My partner and I have decided to track more accurately our expenses and this task is on me. We are looking for an Excel spreadsheet template that will allow us to track all our expenses by category (groceries, eating out, etc), making a distinction between personal and joint expenses. On top of that, we would like to be able to see clearly who spent how much on what.

We have the data written down on a notebook; I just need a way to keep it organised and neat. Is there a spreadsheet that meets our requirements? I have looked around, but most spreadsheets assume combined finances (not the case here; we have separate mortgages, bank accounts, etc) or joint expenses to be split 50-50.

Help us, MeFites?

posted by lost_lettuce to Work & Money (12 answers total)

I don't think you want a spreadsheet. I think you want a tracking program/app.

You can do a simple tracker, and then do columns that drop down to indicate Category, Joint/Individual and a third for Account Paid Out. Then you can do pivot tables. That's easy enough to set up on your own.

But really, an app might be the way to go here.
posted by Ruthless Bunny at 9:42 AM on January 12, 2016 [2 favorites]

A Google spreadsheet might work here but an app that checks against your bank balance might be even better.
posted by k8t at 9:54 AM on January 12, 2016

I like ynab.
posted by k8t at 9:55 AM on January 12, 2016 [1 favorite]

YNAB!
posted by Brittanie at 9:59 AM on January 12, 2016

Best answer: See below for my long winded explanation of YNAB, but describing the way I used it to do what you're talking about makes me wonder if it might work for you to keep two individual finance spreadsheets instead of one?

----------------

I was holding my tongue because YNAB is not a spreadsheet, YNAB is not a spreadsheet even though the first version of the program was a spreadsheet, but two people have said it without much expounding so I'll do the expounding because I use the sucker. YNAB launched their new SaaS product 12/30 (referred to herein as nYNAB) and I've started using it to track joint/personal expenses between my partner and I (previously I used YNAB4 to track my personal expenses). So to recap reasons you might want or not want to use it in lieu of a spreadsheet:

* nYNAB has a monthly fee, it's not free, and I know that's a turn off. People recommending YNAB might be thinking of the desktop software version, but that's not free either, and while they're supporting it until 2016 one probably can't rely on it. If you make an account before the 31st, you'll get a 10% lifetime discount, so if you think you could possibly want to use it in the future, it might be worthwhile to do that. Then it'd be $45 a year or $4.50 a month.
* nYNAB is about more than tracking your joint/personal expenses, it is also about shaping and controlling them, it makes you proactively budget instead of retroactively observe. This might not be what you are looking for at all. It is not an agnostic data tracker program.
* nYNAB has direct import of transactions when set up that way, which a base spreadsheet would not, but manual entry is still supported. However, if your notebook keeping has been a bit hand wavey, that won't work as well here, the system wants you to track exactly every expense.
* nYNAB has phone apps, which let you view the amount you have left in each given category on the go and enter in any transactions that you are doing on the go. This might be useful if you want to keep your partner easily informed about their budget.
* nYNAB lets you split up a transaction into multiple categories. ($20 here, $30 there, $50 my partner owes me)
* nYNAB is pretty new and they really pushed it out the door to get it available for the New Year, so while I fully expect it to improve rapidly, it's not as polished as it should be.
* In a couple months when it actually starts mattering for me, nYNAB will have a reporting area that gives me charts/graphs about budgets and finances. However, I don't think this won't aggregate cross budget (after all, my spending category's are not all the same as my partner's spending categories), so if that's something you're hoping for nYNAB won't work for you.

So here is how I have been managing budget tracking in my relationship:

* We each have our own separate budgets (you can have multiple budgets on the same account)
* I have a category called "[Partner] Advances" and when I make purchases that are split between us, I split up the transaction and categorize the part of it they'll owe me into that category. Then, when I get the payment from them, that incoming payment gets categorized into that category. This doesn't work as well in the new version as it did before because I can't carry a negative balance between months but I think it will still work well enough. I color tag unpaid transactions for my partner with one color and when they are paid color them with a different color. Then on the other side, I can split up the lump sum payment transaction on my partner's budget into the different categories.
* Once a week, we sit down and get our budgets reconciled. That lets my partner make any corrections/fixes and lets me ask what category X and Y purchases actually were.

It could be possible in nYNAB, I think, to have one budget and then one major category for you and one major category for them, however. Or to have dual split named categories under the major categories (aka you have major category "Groceries" and then sub categories "X Groceries" and "Y Groceries", you have an income category and put "X Income" and "Y Income" so you can rearrange your incomes separately too instead of having one big bucket).

So now you'll know if the YNAB recommendations make any sense for you, because I feel like it's a bit of a stretch given what you describe wanting.
posted by foxfirefey at 10:32 AM on January 12, 2016 [5 favorites]

I use an excel spreadsheet and a google spreadsheet for this: the excel spreadsheet is my expenditure register and the google sheet is my accounting/balancing/historical record. It's just a spreadsheet that crush-spouse and I set up, using standard formulas available in spreadsheet programs. Spreadsheets are designed to handle this sort of thing and if you're accustomed to doing this as a paper record, the switch is pretty easy. That's what I did and it's so much nicer to have the spreadsheet doing all the maths and copying all the numbers from one place into another.

I keep my spending worksheets and reconciliation spreadsheet separate because it helps me visualize my money better, but it's apparently a little quirky. Anyway, for me, the expenditure register is three relevant tabs: monthly expenses; credit card 1 accounting; credit card 2 accounting. In monthly expenses, there is a column for every day and a row for my regular expenses (grocery, insurance, investment, mortgage, &c). There is a formula in the far right column that does the sums. On the day I do my accounting, I fill in everything that came out of my money (various bank accounts) for that day.

Each credit card gets a separate worksheet that allows me to account for the total bill and the spreadsheet formulas pull the totals off the credit card worksheet onto the expenditure worksheet.

Once every expenditure is entered into the excel worksheet, I update my bank account balances in the google spreadsheet. Then I copy the expenditure worksheet into the google spreadsheet which uses formulas to balance my accounts. It keeps a running total of the expenditures in one category to date and a snapshot of the expenditures in any given category in any given month.

For you, you might have an accounting tab/sheet that pulls entries from a You Spending tab/sheet, a Partner Spending Tab/sheet and a Joint Spending tab/sheet.
posted by crush-onastick at 10:38 AM on January 12, 2016 [1 favorite]

My wife and I have done this using a plain-old spreadsheet; if I were trying it today, I might use a Google Docs spreadsheet so we could collaborate more easily. It's really not that hard: you just need columns for date, amount, description, category, and person.

Another possibility is that your bank is doing this for you already, at least with debit-card purchases. My bank (USAA) categorizes expenses for me, and while it doesn't always guess correctly, it does a pretty good job, and I can edit/train it. I can also download transaction logs to play with in a spreadsheet program.
posted by adamrice at 10:52 AM on January 12, 2016

I live with my partner and we have some evenly split joint expenses (rent, agreed-upon expenses on joint credit card) but also individual expenses that may get paid for by the other person. I tried using the desktop version of YNAB, since I got it free through work, but I really prefer retrospective data analysis over YNAB's budget-to-$0 approach--plus I found it difficult to finagle YNAB to work with complex joint finances.

Anyway, I looked around the internet for a few templates but ended up just rolling my own solution in Google Sheets (Excel-equivalent in Google Docs). Here's a sanitized version of my spreadsheet if you want to use it as a template.
posted by serelliya at 3:18 PM on January 12, 2016 [1 favorite]

Also not a spreadsheet but we use mint, which is free, and I have custom categories set up (like clothing-ellebeejay and clothing-mr jay instead of the default category which I think is Shopping) so we can see how we're doing on more elective spending. You can look at transactions by account so if you have separate credit cards and bank accounts it's fast to see what purchases were on which account. You can also split transactions so when we go to Target and buy groceries and also DVDs those can be categorized appropriately. After a couple years of this we have some idiosyncratic rules about how to categorize stuff, like if he buys me clothing which may or may not have been a spending priority for me, it gets categorized in a separate category "Gifts for each other" so that it doesn't come up as my spending. But basically you just have to be consistent over time. One nice thing since we started doing this is even though I sometimes think his individual purchases are a little spendy, I can demonstrate to myself that our elective spending is comparable since I buy a lot more random stuff on sale or other cheap but impulsive purchases that add up. So it removed that source of conflict from our relationship.
posted by ellebeejay at 6:02 PM on January 12, 2016 [1 favorite]

I have just loaded 2 years of transactions into this from Savvy Spreadsheets.

It doesn't let you tag mine/not mine/ours, but it would be possible to run 3 copies of it at the same time.

It was a great help in tracking categories of data, and then doing the analysis of it afterwards. Good luck!
posted by thenormshow at 7:35 PM on January 12, 2016

I have a fairly similar situation and use a google doc for my own spending record /budget and a separate google doc that i use to track spending with my boyfriend (live separately but spend weekends together, and we like to split costs evenly but take turns buying stuff for each other so it ends up being pretty stupidly detailed). The second one sounds like exactly what you want.

It works well for me but I love spreadsheets (and making them exactly right for me)... If you hate them then I'd go with ynab or whatever instead. The important thing is finding something you don't mind updating regularly. But if you do stick with spreadsheets I don't mind sending you a blank version of mine, if you would find either of them helpful.
posted by randomnity at 7:37 PM on January 12, 2016

I just realized that the savvy spreadsheet linked above would do it, as long as 10 categories of spending was enough. You could do a category "Groceries" and then have sub categories for mine/not mine/ours. Worth a look.
posted by thenormshow at 7:58 PM on January 12, 2016