I’ve been keeping personal finance spreadsheets since 2004. A decade of information is very powerful when making big decisions – career changes, relocation abroad, and planning for the likelihood of a future mortgage. But even only a couple of months of information can help you see where your money goes, and easy changes to save a bit more. I think the biggest barrier to entry is setting up something simple that works, so I’d like to share some tips over two posts.
There are a number of tools and apps online which you can take advantage of, even some banks offer tools. But I like to use a spreadsheet package for maximum flexibility, and to push my computing skills. I use Excel, but gmail and Excel for skydrive are good free online spreadsheets.
I’ve experimented with a number of setups and helped others set up theirs. The personal finance spreadsheet should serve two purposes: recording expenses as they occur (“Transactions”), and planning ahead (“Budget”). The second is by far much harder, and we’ll tackle it in another post. The most important reason for considering the two separately is because one deals with the past, and the other with the future. If the two are separate and your future plans change or break down, you still have an accurate record of expenses in the past.
Before we look at the Transactions component, there are some considerations we need to explore:
-
What is your ‘accounting period’? You need to think of periods of time in order to compare changes in your expenditure. Some would pick weekly, but I prefer to use months: a decent enough time frame to capture a range of expenses. Note that for the purposes of recording I wouldn’t pick from payday to payday as these things change based on days of the week and between jobs.
-
What is your ‘accounting year’? It makes it easy to make improvements to your spreadsheet if you start a new one every year (keeping the old ones for reference). I take from 1 July – 30 June to match the Australian financial year. In England you might do this from April – March. Or you might use a calendar year. Or based on the day you start your spreadsheet.
-
What level of detail do you want to go into? I like to keep mine simple and have about nine expense categories, and some for income. I have built spreadsheets for others with up to 40 sub-categories, grouped around 10 or so larger categories. I used to do this myself but found reducing categories and keeping a column with a description was more useful. Don’t be afraid to experiment. Also, creating a new file every year allows you to change categories as your life situation changes… I don’t need one for baby expenses at this age, nor do I have a ‘car’ category living in London, but I can add one when the time comes.
-
Think about your sources of income. If you have multiple jobs, income from Zazzle or ebay, interest accounts – you might want to create different categories for each. Think about which income streams are passive and which are active. Complex things, such as share portfolios or managing your pension/superannuation, should be dealt with in other spreadsheets.
-
Do you need to record or report in multiple currencies? How will you treat the exchange rates and which currencies do you want to report on? Since I’m an Australian living in London, I convert everything back to GBP and AUD.
My spreadsheet has a cover tab, a “Transactions” tab, a currency tab, “Budget” tab and a “Totals” tab. Both the Budget and Totals tabs link into “Transactions”, but are separate from each other. We’ll cover “Budget” in the next post.
The “Transactions” tab is essentially a really long list. It doesn’t fix you into a row-per-day style recording, and you can set it up to filter and sort. This is really handy when you’re checking back over your records when your payslip is emailed to you late, or you’ve not been checking your online banking whilst travelling. Or if you find a scrunched up taxi receipt in the bottom of your pocket from the early hours after a night of heavy drinking.
In a new spreadsheet create a tab named “Transactions”. In your first column, you record the date including the year – use date format as you’ll be able to take advantage of formulae to automate your spreadsheet. Column B should record the amount, column C the currency (you’ll need another tab to handle multiple currency reporting. If you’re only reporting in your ‘home’ currency, you could have a column for conversion).
Column D and E handle “Transaction” and “Budget” categories. I’ll cover the Budget categories in the next post. My transaction categories right now are: A – Food in, B – Clothes, C – Transport, D – Essential Goods and Services, E – Food out, F – Alcohol, G – Travel, H – Discretionary Goods and Services, K – Rent, P – Net Income, I – Interest, T – Income Tax. Finally, column F allows me to make notes on individual transactions – useful if I’ve shared the expense with somebody or I want to make a note of where or what was bought. I hate clutter and throw away unnecessary receipts, so this column is useful.
Once you’ve got your list, it’s time to learn how to put Excel to work. Create a tab called “Totals” and learn how the following formulae work: SUMIFS, SUMPRODUCT and maybe IF statements are the most useful. If your accounting period is a month, create a table of information with months-categories as your two axes. This can drive line charts and pie charts. Colour code your categories and charts to make them pretty – spreadsheets can be turned into works of art. Fill out your transaction list as often as possible, and quickly you’ll find this becomes part of normal routine. Record everything. Quickly you’ll know exactly where your money is going.
Next post I’ll talk about creating budgets. For now, just record what you are spending honestly.