Tracking expenses is one of the most important ways to take control of your finances. You can’t actively manage your money unless you know what you spend your money on. That said, tracking your spending can be tedious – manually entering hundreds of transactions a month is nobody’s idea of fun.
Things are getting better, there are now many automated apps that can do a lot of the grind for you. That said, I still think it’s better for everyone to track their own expenses in their own way. That’s because it means you are actively engaging with your spending. Automating expenses tracking can mean you end up on auto-pilot, not really challenging what you spend each month.
Each month, we track our expenses in a homemade spreadsheet. It means we don’t hand over our bank details to any third-parties and we can monitor our spending habits.
At the end of each month, we sit down together and download our bank statements. We then input those into our spreadsheet, do a bit of labelling and out the other end comes our summarised expenses and savings rate. It only takes us about 30 minutes to an hour each month. Which is much less than any other non-automated process we’ve tried.
Over time, with lots of similar items (mortgage, utilities, groceries) the process becomes quite straightforward. It also helps to spot spending patterns, identify odd or expensive spending and has a side benefit of picking up on fraud (which has thankfully never happened to us).
We’ve used it for over two years. After many requests, I’ve finally tidied it up and changed a few things so that I can share it with you.
It’s a deliberately very simple spreadsheet. There are no complicated formulas (for excel geeks: the most complicated formula is ‘SUMIFS’, the ugliest formula a not very nice ‘IF’ function). There are no macros or VBA. Sure, it could be cleaner, faster, better (if you have suggestions, or find errors do let me know). But, I believe this is a spreadsheet that anybody can use.
This is also a spreadsheet where you don’t have to fill everything (or in fact anything) for it to work. Likewise, this spreadsheet is pretty easy to customise and add/change things if you want. I’ve designed it with redundancy built-in for that reason.
How it tracks expenses
Let’s give a run through for how it works. Where you see a yellow or blue tab, that’s where you manually enter data. Everything else is automated.
There are three elements to the spreadsheet:
- The ‘labels’ – where you input a few things on first use
- The ‘bank account tabs’ – numbered 1 through 10, each represents a bank account – here you input your bank account transactions
- The ‘summaries’ – these summarise the data from the bank account tabs in various ways, with the option to add some more data if desired.
First up, there’s the ‘Labels’ tab.
There are a few things to do in this tab:
- Enter the starting date.
- Customise the expense ‘tags’ in column 2, these can be whatever you want, use as many as you need.
- If you want, you can then label whether these tags are bills, one-off items or if you want to exclude them from the summaries:
- We label some items as ‘bills’ these are the things that Mrs YFG and I split between us
- Some items are ‘excluded’ these are where we transfer money between accounts (‘transfers’); money we invest (‘investments’) and capital expenditure (such as, our house extension. These won’t show up in the summaries.
- Some items we label as one-offs, these are big expenditure items that will only occur once. There are then separate summary lines which show expenses and income minus these lines, to show the more ‘regular’ in and outgoings
- Next up is to do the same for the income tags (blue cells)
- Next, enter your names, this is used to split expenses and income by person (you can use ‘joint’ or similar if you have a joint account).
- Finally, you can designate how the bills are split between yourselves.
The bank account tabs
On first use, there are two things to do. One, select the person from the drop-down menu and two, put in your bank account name (or identifier) for reference (so you know which tab is for which account.
The process for using this tab is quite straightforward. What we do is to login to our online banking; navigate to the page where you can download your bank statements; and export them as an excel or csv file.
We then copy this data into the relevant yellow boxes. Be careful to make sure all items are entered as positive (not negative figures). Money going into the account should go in the “money in” column, money going out into the “money out” column.
Finally, for each item, select the relevant ‘tag’ (i.e. mortgage, groceries, etc.).
Here’s an example of how to enter the data:
Two minor things.
You’ll notice I’ve left a cell with “-1.00”. If you’ve got negative values, select this cell, and hit copy. Select the negative values, then do CTRL+ALT+V (aka ‘paste special’) then under ‘operation’ select ‘multiply’ this will multiply all the negative values by minus 1 to turn them positive.
Another thing to note is refunds. There are three ways to deal with these:
- Set up an income label as ‘refunds’ or something similar
- Move these items into the money out column as negative values and label them the same as the earlier expenditure (so they cancel out the earlier expense).
- Delete the entries manually, and the corresponding expense.
(I’ve tried to think of a better solution, but couldn’t come up with one!)
You do that for each of your bank accounts and all the automation is done for you by the snazzy formulas.
The summary tabs
First up, there is ‘Expenses by individual‘. This tab brings together the expenses for an individual across all the bank accounts. No need to enter any data here. (To the excel geeks: this is the tab with the horrible IF formulas – sorry!)
Next is the ‘Income by individual‘. It works in the same way as the expenses tab above, just for income. One difference is that you can manually add in income which you might want to include, but doesn’t flow into your bank accounts (such as, interest in investment accounts etc.) This can be done in the yellow boxes as usual.
The third summary is ‘Savings rate by individual’, here you can calculate each person’s savings rate. There are some tabs where you can add in extra savings that don’t go through your bank accounts (i.e. pension contributions, SAYE, etc.)
The main summary is surprisingly named ‘Summary’. This aggregates everything together. With a table for expenses, income and finally savings rate.
Finally, there are some charts in the ‘Charts’ tab which are linked to the ‘Summary’ tab. These are the ones we use, but feel free to create your own.
And that’s it. If you’ve been looking for an expenses spreadsheet, please download it and give it a whirl:
Updated Google docs link: https://docs.google.com/spreadsheets/d/1TuyLlAIPMx9SDJrQZUFOHkTKQLO7773DEHrdWNzsdE0/edit?usp=sharing
If you have any questions or suggestions, do leave a comment or drop me an email. I certainly think of this as a living document, so I expect to make updates and changes over time.
Do feel free to make changes, and edit it the spreadsheet if you want. I think it’s good to make the spreadsheets and documents feel like your own!
All the best,
Young FI Guy