How we track our expenses

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.

The process

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).

The Spreadsheet

Enough abstract. Let’s talk about the spreadsheet. (Excel: link Google docs: link)

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.

The labels

First up, there’s the ‘Labels’ tab.

There are a few things to do in this tab:

  1. Enter the starting date.
  2. Customise the expense ‘tags’ in column 2, these can be whatever you want, use as many as you need.
  3. 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:
    1. We label some items as ‘bills’ these are the things that Mrs YFG and I split between us
    2. 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.
    3. 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
  4. Next up is to do the same for the income tags (blue cells)
  5. 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).
  6. 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:

Expenses tracking example
Screenshot of expenses tracking spreadsheet – transactions

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:

  1. Set up an income label as ‘refunds’ or something similar
  2. 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).
  3. 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.

Wrap up

And that’s it. If you’ve been looking for an expenses spreadsheet, please download it and give it a whirl:

Updated Excel v1.2: https://youngfiguy.com/wp-content/uploads/Income-and-expenses-spreadsheet-1.2.xlsx

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

Comments

  1. A comprehensive spreadsheet and you’ve put a lot of effort into it.

    On the topic of spending categories – where did you come up with them?  for example you have food and lunch – does that include a lunch time yoga class for £5 or a sandwich on the go?  But you then also have eating out but nothing for booze.

    Just wondering, I think that whatever works for you then do that. 🙂 but I’ve had trouble with consistent categories for spending – for example, you spend £50 in Tescos and £20 of that is booze, then go out at night and withdraw £50 and spend that on dinner and drinks and have £10 left over to spend at the butchers the next day.  Maybe I don’t like thinking of how much I used to spend on booze. 🙂

     

    1. Our categories are a little weird haha. Food is take out or convince food rather than groceries. Lunch is lunch.

      Eating out includes boozing. Mrs YFG and I don’t do much going out for drinks (or out for dinner) unless the host is paying.

  2. I have been using Banktree for some months (simply downloading data from bank accounts NOT using screen scraping). Like any program there is a bit of a learning curve but its pretty straight forward for cash accounts and checking auto allocation gets quicker each month. Lots of report options, including custom ones, which are also good for spotting errors or unusual items.

  3. Thanks for the Excel Sheet and the walk through. I have only been tracking account balances and income to come up with a savings rate overall. It will be interesting to find out the breakdown of expenses more clearly. I think I might give this a go.

    -Fireplanter

  4. Thanks a lot for this, I’d been using MoneyDashboard for the last two years, but I do prefer having a tool available offline and able to cope with my foreign accounts as well.
    Looking forward to filling it in every month!

    1. Thank you Alexandre, I’ve used Money Dashboard in the past but like you prefer something offline and without access to my bank accounts. Let me know if the spreadsheet works for you!

    1. Thank you Roberto. It took a while, but a worthy exercise. It has meant I’ve ironed out some of the issues in the spreadsheet Mrs YFG and I were using. Also very handy to keep the excel skills from getting rusty!

  5. Thanks for this awesome spreadsheet–I just wanted to check whether we should be aiming for the “CHECK” to balance as “true”–it seems that unless your income and expenditure matches exactly in a month, this will never be the case?

    1. Hi CW, thank you. Yes, you should aim to have the checks saying ‘TRUE’. These are checking that every penny is being accounted for – i.e all the transactions have been labelled.

  6. Hi YFG, thank you for sharing this brilliant spread sheet. I’d like to use it because it looks really more brilliant than mine, but it seems that OpenOffice has some serious probles opening the .xlsx files without messing all the formulas. Is there any chance you could convert it in an open type of document like odt? I tried with MS Excel Online but it still makes a mess. Thank you!

    1. Hi Isabella, thank you for the kind words. I’ll have a look at converting it into an open office and a Google sheets. Not sure what would be causing the problems.

        1. Hi Isabella, I’ve now uploaded it to google docs, and it works (after a few updates). The new links are in the article.

          Unfortunately, Open Office absolutely hates the spreadsheet for some reason, so I think I think I’ll just have to give up on that.

Have some thoughts or comments? Please share!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: