Manage your personal cashflow using Excel

With the Government's austerity measures beginning to affect the economy, it is more important than ever to try and avoid running up hefty bank charges, or spoiling your credit rating.

A simple spreadsheet showing where your money currently goes each month can help drastically reduce future expenditure. In this article, we look at how Excel 2010 is used to create a spreadsheet to help manage your personal finances.

Creating the personal cashflow spreadsheet

To create a personal cashflow spreadsheet, begin by planning the column headings that best describe your items and the income and expenditure associated with them. These headings are important as they will help ensure you have the correct data to analyse.

Because we're looking at cashflow, it's important to know;

  • on which dates money is going in and out of the bank account
  • what the money is for
  • what the resulting bank balance will be

Let's assume we want to know in advance what our bank balance is likely to be throughout November.

Type the date of any planned transaction under Date, then type the reason for the transaction in the next column, Item. If money is coming into your bank account, type the amount into the In column: if money is leaving your account, type the amount in the Out column.

To calculate your starting bank balance use the simple formula = D3+E3, and add your current bank balance (subtract, if the account is overdrawn). In this case, there is £100 in the bank, so the formula is =D3+E3+100. Every future bank balance is "current bank balance plus any income minus any payments".

To calculate the bank balance after the second transaction on 01/11, the formula is =F3+D4-E4: then Enter.

This formula stays the same for all future bank balances, apart from the row numbers.

To copy the formula into the rest of the cells in column F, click the cell. A small square block will appear in the bottom right-hand corner of the cell. Click on this block and drag it down to the bottom of cell F19.

To highlight when your bank balance goes into overdraft, select the Bank Balance column, right-click the mouse and choose Format Cells from the menu which appears. Click the Number tab then choose Currency from the list of number types. Select your preferred number of Decimal Places and currency Symbol. A preview list of examples will be displayed in the box below: click the one you want, then choose OK.

Choosing to display negative amounts in red helps you see instantly when your bank account is going to be overdrawn.

As you can see, Excel 2010 can be an invaluable tool for managing your personal finances.

As well as monitoring your cashflow, you can use one of Excel's existing templates to keep track of your regular monthly and annual expenses, or budget for one-off projects.