Are you looking for an Income and Expenditure Tracker?
Look no further! Lewis and I are tracking all of our expenditure and income down to the last cent and have replicated the template for you to use in Microsoft Excel. We are always being asked how much much do you need to travel and we are completing this spreadsheet for each country we visit. That way, we will have our costs of travelling in each country for a rough guide to what you will need (although this will vary for each person unless you buy exactly the same things us as on a daily basis).
In order to use this, your device will need to have this installed. We have created a simple to use spreadsheet (simple if you can use excel and hopefully simple if you don’t with our explanations below, although I haven’t gone into great detail about using excel).
Income And Expenditure Tracker
The spreadsheet is made up of 17 tabs, you may not want or need to use them all but we have created them all just in case. We use them all for our travels and used a rougher version of this whilst we were saving. Saying that, its not a bad idea to use this if you are not planning to travel and instead just want to manage your finances and see where all those last cents or pennies are spent. We have found that since tracking our spend, we are spending less as we know it is all accounted for. There is no hiding chocolate bars and cheeky pints anymore!
The first page is a key you will use for categories (as explained below), we have set these up for what we use and what we think you will use but these can be easily changed. Please note, these should be set up before you start using the spreadsheet to ensure the dropdown menus are all the same. Having said that, if you need to add or amend them, you can do.
Column A6 to A27 is the current drop down choices for categories of expenditure, you can overtype these to change the categories that you want and overtype the explanations just so you can remind yourself of what to log where. The tabs “January” to “December” pull this information as you will note later in this how to use guide.
The drop down menu is on a separate tab to ensure that it doesn’t get deleted, when this is within a working spreadsheet, there is a chance it could get lines deleted from it which would create you more work.
The summary tab is an overview of your expenditure, income, monthly balance and year to date totals. This sheet is completely completed via the other tabs and nothing should be typed onto this sheet or you will lose the formulas that are present. I haven’t locked the cells as this would make it not as flexible as it is currently.
If you are travelling, it is likely that you will have income and expenditure in more than one currency so we have made 4 separate columns in order to review each currency balance. Clearly if you are on a world tour, you will need more than 4 but it would be a massive spreadsheet! If you need more, just use more than one of these spreadsheets, they can be easily linked. If you only need one currency as you want to use this for day to day spending, you can easily hide the surplus column. Should you want to hide the income for all currencies other than GBP, click on number 7 and drag the mouse down to highlight the rows to number 10, then right click and select hide rows.
The rows will then be hidden but will be there if you ever want them back. To get them back, just click on number 6 and drag down to 11 and right click, select unhide and the rows will reappear.
For the purposes of the spreadsheet, we have set up other for Euros as GBP, AUD, USD and Euros and the four biggest currencies in use.
I have added a currency conversion chart which the summary table uses to calculate an overall figure. As I am from the UK, I wanted to see all the various currencies give me an overall GBP figure. Again, this can be changed to your currency of choice.
You will note the total combined in row 23, this takes all income and expenditure and currently turns it into GBP. You will see the formula (in the formula bar highlighted) adds up GBP monthly balance, the AUD monthly balance multiplied by the AUD to GBP rate (to give me the balance in GBP, the USD balance multiplied by the conversion rate etc If you wanted the balance in Euros for example, the formula would be as below.
The rates of exchange in the table can be updated at any time to give you a real time view of current monies, this doesn’t count if you have already exchanged some of the money but a good way to get an overview give or take. There is a way to create multiple rates of exchange but this will need additional formulas which I haven’t created but could help you out if you ever needed it.
There is an expenditure overview section on here which links to each individual tab. This will pull through the monthly spend per category in an easy to view table.. For illustration purposes, our table is below so you can see what it looks like. We turned ours into a pie chart to see how we spend our money, as you can see we spend way to much on non-essential food!
The next tab is account balances. In this tab, all accounts can be logged for easy access and to be able to see the total balances all in one place. If you get to save more than a few thousand pounds, it makes sense to have it in more than place. Many banks and savings accounts offer a much higher rate on the first few thousand. We have created a few accounts to make the most out of these offers and make our money work harder. The downside for me was not being able to see one big balance, then we created this page so I can see it all in one place.
The accounts that you complete here will pull through to the interest tracker page as below.
You shouldn’t type in anything into this tab as it should just pull through. The boxes on the interest tab will be grey until the account balance is completed. Each month, you can log the interest received and this will pull through to your summary page. It may be interest but it is still an earning!
The monthly interest will be a manual entry each month onto the interest tracker tab as shown above.
The Pay slips tracker is something we have created to track our pay, keep track of our superannuation and our annual leave values. This is a tab you may not wish to use and you don’t have to. It isn’t linked to anything so don’t worry if this isn’t something you want to do.
The main bulk of the spreadsheet is the monthly tabs as shown below.
Each tab does the same thing, just for different months. The date column is where you log the date of the expenditure/income. The category information pulls through from the 1st tab that we discussed earlier. Whatever titles you chose initially will now be your drop down selections. Simply click on the box and a down arrow will appear, select the category of the spend from the list and away you go. The description is where you can manually type in the detail to go along with your selection i.e. if you chose drink essential, the detail may be water or if you want more detail, you can state the brand and size. This is up to you. Then you type the value into the corresponding column, in this case if its GBP type the amount into column D, for AUD column E etc. Again, if you have only one type of expenditure you can hide the other columns to make it easier for you. For income, you put the date of when the income was obtained, the source of income i.e. wages, tips, etc, then add the amount into the right column.
These amounts will then pull through to the summary tab. Just remember to use the rightly monthly tab when adding so the months are accurate.
You will also note a summary box, currently this is a manual entry that I summarise at the end of each month. Again, you can choose whether you want to use this or not. This will pull through to the table on the summary tab for you to be able to create a pie chart with should you choose as we showed earlier in the blog.
Continue this month on month to start tracking your income and expenditure. If you want to find out how to save money, we wrote a useful hints and tips blog you can read here. Whilst it was written for saving money to travel, it can be saving money for anything not just travelling!
N.B. I haven’t gone into great detail of how to use excel but if there is something you are struggling with, drop me an email and I will help you out.
Download the excel file
Click here to download the worksheet.
Once open click on the download button at the top right of the page.