Use Excel to keep track of your finances

Setting up a spreadsheet
Most people have Excel configured to display three worksheets in each workbook – called Sheet1, Sheet2 and Sheet3. The first step in setting up your own finances is to create enough extra sheets to cope with all your bank and financial accounts.

These will include personal and business current accounts, savings accounts, ISAs and credit cards. You should also define a separate consolidation sheet on which balances from all the other sheets can be totalled and analysed. To create an extra worksheet, go to Insert, and select Worksheet.

To change the label on each sheet’s tab, double-click on the tab and overtype the unhelpful Sheet + number identification with something more meaningful, such as Current Account or Savings. The order of the tabbed sheets can be changed by dragging the tabs, either arranging them alphabetically or grouping them by type.

Having arranged the sheets, select them one at a time and add suitable column headings. This usually means Date, Description, In, Out, and Balance, but it’s also useful to have a column on the right where you can mark to show whether or not a transaction has been reconciled.

With the headings in place, format them to make the tabs stand out and then lock them in place so that when you scroll down a long screen of transactions, the headings remain in view at the top of the page. In Excel, headings are locked by placing the cursor in column A of the row beneath them, and then selecting Freeze Panes from the Window menu.

Recording transactions
The first transaction on any sheet should not include any formulas. Simply enter the date and the amounts in and out, and then work out the balance for yourself, typing the answer into the Balance column.

On subsequent lines you’ll use a formula that takes the balance from the line above, then adds in the current line’s payment and deducts the current line’s withdrawal to arrive at a new balance. To see this in action, take a look at the Current account sheet in the Moneytrack.xls workbook and click on cell E3.

This is the Balance column and contains the formula ‘=E2+C3-D3’, where E2 is the balance for the line above and C3 and D3 are the amounts in and out from the current line. Note that all formulas in this feature should be entered without the quote marks.

Subsequent transactions should not be typed on to the next available row. Instead, create a new row immediately above the bottom row, and enter the most recent transaction there. To insert such a row in Excel, place the cursor on the bottom row and click on the Edit menu, then click on Rows.

You don’t need to type in a new Balance formula every time you do this; simply copy the contents from the Balance cell of the row above and the formula will adjust itself automatically.

When you’ve finished adding all your recent transactions on new rows, put the rows into date order by selecting and sorting them, using the Sort icon on the spreadsheet’s toolbar. The reason for this way of doing things will become clear when you start naming cells in the worksheets.

What’s in a name?
It is possible to name any cell in a worksheet so that instead of having to refer to it by its sheet name and location (for example, ‘=ESavings!E83’), you can refer to it by the name you’ve chosen for it. When you insert rows above a named cell, the named cell’s location is automatically updated to take account of the extra rows, so you never need to remember the actual address of the named cell or even know where it is.

If you name the balance cell on the bottom row of each worksheet, you can refer to the balance cells by name on the consolidation worksheet. To name a cell, select it and then open the Insert menu. Click on Name, followed by Define. This opens the Define Name dialogue box where you can type in a name.

Putting it all together
To see how consolidation works, take a look at the Consolidation worksheet of Moneytrack.xls. In column A are plain English names of the accounts stored on each worksheet.

In column B are the current balances from each sheet, which in every case are referenced simply by name. For example, the balance from the Current ac sheet is represented in cell B2 by ‘=Current’, and the balance from the Cash ISA sheet in cell B6 is referenced as ‘=ISA’.

The figure for total worth is arrived at by adding all the balances representing assets, and subtracting the two debt balances – for example, money that is owed on credit cards.

The formula in cell B10 that achieves this is ‘=SUM(B4:B7)-B8-B9’.

If you arrange the Consolidation worksheet so that assets are listed before debts, you can select the column headings together with the assets rows and click on the Chart Wizard to create a pie chart that shows where your assets are allocated.

If you are weeks behind at entering new transactions, then the consolidated total worth figure will be historical rather than current, but if you develop the habit of recording new transactions as you make them, the total worth figure will always be spot on.

Individual totals are important too. For example, at the end of each month you’ll want to ensure that the amount owing on your credit card is less than the balance of your current account.

Special features
Some of the sheets in the Moneytrack.xls workbook work differently from standard bank and credit accounts. The Bills sheet records household spending from year to year. It serves as an instant guide to annual expenditure and can easily be adapted to include other expenditure.

The Premium Bonds sheet records premium bond winnings and calculates what the winnings represent in terms of an annual equivalent interest rate. The figures on row 30 are arrived at by dividing the total annual winnings on row 29 by the number of bonds held and then formatting the result as a percentage.

The figures on row 31 adjust this rate to reflect what it is worth to a standard rate tax payer. This yield can be compared with what’s on offer from the banks to see whether your money might not work better for you as a more conventional investment.

Do it your own way
Experiment with the Moneytrack.xls workbook to see how it works, and then have a go at building a personal system geared towards the way you organise your own finances. If you fancy a challenge, try adding a column to the Current ac sheet that allocates expenditure and income to categories. If you do, you’ll then be able to sort on this column and produce totals that can be used to graph where your money comes from and, more importantly, where it goes to.