Mike Perris



home >> excel >> Tracking credit card and bank account balances with Excel

Keeping track of your credit card and bank account balances with Excel

Have you ever opened a credit card bill and looked at it with a general feeling of horror? Or opened a bank statement and wondered, as the blood drains from your face, how you’re going to pay the bills next month? If so, you’re not alone.

Since I wrote this article about two months ago, it’s become the single most read item on this site. People from all around the world, many of whom work in banks, visit here after searching “how do I manage my debt using a spreadsheet?”

Whilst I must emphasis that I’m not a financial adviser, I do know a thing or two about making computers more useful, and to this end I share with you a neat little spreadsheet I created many years ago, which (a) tells me how much money I’ve got (or haven’t got) at any given time, and also gives an early warning if, financially, things may be going a little wrong.

Forecast

At this point, I would like to emphasise a key - and perhaps fairly obvious - point: the spreadsheet requires you to input information. Every time you spend (and earn) money, you must put that information into the spreadsheet without fail. Without the correct information, it won’t work. Indeed, no solution would work.

In a nutshell, if you don’t want your credit card bill or bank statement to come as a nasty surprise, you need to make a note of your current balances and keep a running tab that details what you’re spending and what you’re earning. Whilst you could do that on a piece of paper or back of an envelope, a spreadsheet is more useful as it allows you to create a ‘super statement’ of all your bank and credit card accounts, all joined together, showing you how they affect each another, what their status is at the moment, and what they’re likely to be in the future.

 

This entire article relates to the ‘account balances’ Excel 2010 spreadsheet, which you can download from here.

Overview of the Spreadsheet’s Layout.

Once you’ve loaded the spreadsheet into Excel 2010, you’ll be able to have a quick look around, and inputting some figures to see what happens is the easiest way to figure out how it works. While it’s designed to be simple and quick to use, there are a few things that need explaining.

At the bottom of the worksheet are named tabs for each month of the year (Jan-Dec).



The leftmost tab (before Jan) is called “LABELS”. This worksheet contains easily-customised account headings that are used on each of the monthly sheets (Jan-Dec). I’ll explain more about this later.

If you click on the month you’ll notice that each sheet is largely grey in colour, with the exception of the sheet for the current month (which is bog-standard Excel white). This is simply because I’ve put conditional formatting on each of the sheets, which makes them grey if they don’t relate to the current month. Without the conditional formatting, each sheet would look the same and it would be too easy to start inputting figures onto the wrong month’s sheet.

Headings

Without alteration, this spreadsheet will let you keep track of two credit cards, two bank accounts (current [or ‘checking’] and a savings account, for example), plus cash.

There are columns that keep a running total of your combined credit card debt, the combined wealth  in your bank accounts, and your net worth, which is how much money you’ve really got when you take into account (no pun intended) your credit card debt (although excluding things like mortgage and outstanding car-loans, which you could add to the spreadsheet if you wanted, but is beyond the scope of this article).

Each monthly sheet (Jan to Dec) starts with two sets of columns for keeping track of your credit cards, as shown below:



The headings (“VISA” and “Mastercard”) on each of these ‘monthly’ sheets can be changed on the ‘LABELS’ sheet – if you change it there, it changes on each of the Jan-Dec sheets, automatically.

On the row below the name of the card, we’ve got Date, Description, Amount and Balance. On the row below that, you’ve have “Account as at (end of previous month)” in the description column, and the amount. This figure – the balance carried forward - is always copied from the last cell of the previous month, with the exception January, when it must be input manually or linked to the previous year’s spreadsheet, detailed later (if you’re starting the spreadsheet part-way through a year, you’ll need to put the opening balance in manually, too, obviously).

Next to the credit card section is the “bank account section”. Essentially very similar to the credit card totals (credit cards are, after all, another form of bank account), and again we have the date, description, amount and balance columns.

I’ve designed the spreadsheet to keep track of two bank accounts (one for current accounts [the type your salary is paid into and your mortgage/rent comes out of] and the other for deposit accounts. Once again, the headings (“Current Account” and “Savings Account” ) can be renamed by altering the text on the LABELS sheet (cells B4 and B5).

As you spend money on your credit card, you should input the date, description and amount in the appropriate columns of the spreadsheet. The ‘Balance’ column is calculated automatically. Likewise, as you spend money from your current account (by making direct debits, using a debit card, making cash-withdrawals from an ATM or writing cheques/checks), do the same in the ‘Current Account’ columns.

In the case of the bank accounts, you should also record money being paid in to the account, whether it’s wages or interest paid on the account, or money you’re getting from eBay sales.

On each sheet, from the current month to the end of the year, you should pre-enter known outgoings and incomes. For example, your rent or mortgage, home and car insurance, anything and everything that you know is going to be deducted from (or paid into) your account.

Linking payments/deductions

If you’ve scrolled down the spreadsheet, you’ll in the each credit card section there’s a description that say “VISA Payment” (or “Mastercard Payment”) and in the Current Account section  there are entries for the same thing – this is where the payment to a credit card is mapped to the debit from the current account – see the example below



(columns F through J – the ‘Mastercard’ columns – are hidden, for clarity)

The calculation behind the payment is relatively simple – the spreadsheet keeps a running total of the balance of the credit card. On the row where we put the card’s payment (row 41), we simply have the formula “-1*E40”.

The corresponding cell in the Current Account section (as shown below) is simply “=C41”, so we’re simply referring to the value on the Credit Card section – so whatever value appears in the credit card total is matched in the Current Account. (In practice, it won’t quite work like that, as I’ll explain later.)


Other Running Totals

At the far right of the spreadsheet, we have three more columns (Y, Z and AA) which show us “Total Credit Card Debt”, “Total Savings incl. Cash” and “Net Worth”.



As you might expect, each row in the column “Total Credit Card Debt” is a calculated figure based on one credit card’s balance added to the other’s, on the same row – so Y39=E39+J39.

“Total Savings Incl Cash” is a calculated figure based on Current Account plus Deposit Account plus Cash, again on the same row.
Net Worth is the total savings minus the credit card debt.

To show all this in action, consider the following:



(I’ve hidden some of the columns – such as Mastercard and Deposit Account - that aren’t applicable to this example.)

In the above example, column E shows the running balance of the Visa card, at £265.33. No other debits are made on the card, so the balance remains constant until we get to row 41 and the item “VISA Payment” with the figure -£265.33 (which is calculated by multiplying the £265.33 in cell E40 by -1).
Looking at the Current Account columns, we can see a balance of £1,712.47 in column O. There are no payments to or debits from the account until row 41, where we have “VISA Payment” of -£265.33, which reduced the balance of the current account to £1,447.14.

Note: when you put payment entries into the current (or ‘deposit’ or ‘cash’) columns, always put a minus-sign (“-“) before the number, because you are deducting this money from the account.

(You would be right in thinking that we should also put minus-signs when typing entries into the credit card columns; when you first get a credit card it has a balance of 0.00, and when you start spending on the card the balance is decreasing, just as it would with any other bank account. However, when we get our credit card bill it shows positive figures increasing merrily. This is reverse psychology by the credit card company.

If your credit card statement ends “Balance: $2,900”, it doesn’t look too bad, but what it really means is “Balance: -$2,900”, which does look bad.
You can, if you like, change the workings of the spreadsheet to show red figures in the credit card columns, but I’ve left it as most people would expect to see it, as that’s what we’re conditioned to see. Changing it confuses things.)

What text’s red and why

You won’t have missed that in the heading of the column “Total Credit Card Debt”, the word “Debt” is in red. This is to remind you that all the figures in that column reflect a negative balance (see above). For that reason, I’ve formatted the cells in the column to show red numbers, even though the numbers are technically positive.

Other than that, positive numbers are in black, negative numbers are in red, with the exception of the credit card columns, which are opposite, for the reason stated.

Of great importance is, of course, the Net Worth column. This is how much money you’ve got after your credit card debts have been taken into account.
Take a look at the above example, again.

As you can see, column Y shows the Total Credit Card Debt of £265.33 in red. We have total savings of £1,712.47, giving us a net worth of £1,447.14 (which is £1,712.47 minus £265.33). You’ll note that the credit card debt stays at £265.33 until row 41 when the “Visa Payment” shows up. At that point, the credit card debt is 0.00. The bank balance drops to £1,447.14, but the Net Worth remains the same throughout. Indeed, once the credit card debt is settled, the Total Savings and Net Worth figures match up, which is exactly what we’d expect.

Hiding columns that aren’t used or used infrequently

If you only have one credit card, or you don’t have a savings account, you can hide the columns on your spreadsheet, so you can focus on what’s relevant to you. You could, of course, delete the unused columns, but you should be careful that you don’t mess up any of the spreadsheet’s formulae. For this reason, I would generally just keep unused columns hidden.

Inputting data

Every time you spend or earn money, enter it on the sheet. There’s no special allocation for days of the month – ie purchases made on the 15th don’t necessarily go on the 15th. When I set up this spreadsheet, the idea was to make it as painless as possible, so as you input new items they go directly beneath the previous item, as shown below (this also helps you check your bank and credit card statements, as discussed later):



Inputting regular payments – mortgage/rent/insurance/wages

Obviously, the spreadsheet’s ability to forecast your bank balance is dependent on it having accurate information – “Garbage in, garbage out”, as they say. If you have recurring (predictable) expenditure, such as rent/mortgage, council tax and so on, input them onto each sheet of the spreadsheet from the current month to the end of the year (there’s no point inputting this stuff for earlier months that you’ve missed – so if you’re starting this in September, don’t bother about January to August. Next year you will, but not now.

If you have a payment that’s due to go out in the middle of the month, put it around about the middle of the sheet (say, column 21). You can always shift it later. So long as it appears somewhere on the sheet, the running balance (at least at the end of the month) should be accurate. Over time, the sheet becomes very accurate indeed; it’s a pretty rare event for me to check my bank account online and find it doesn’t match the figure on the spreadsheet.

Moving card payments from end of each sheet

“Out of the box”, as it were, the spreadsheet assumes that your credit card is paid off at the end of the month. In practice, of course, this generally doesn’t happen – for example, your bill might arrive on the 5th, and gets settled on the 20th.

Keep the default “end of month” payments where they are until you actually make the payment, then input them with the date, description (“Card payment”, for example) and amount (as a negative). Make sure you then delete the entry at the bottom of the sheet! You will then need to put the corresponding payment into your Current Account column (and remove the default one from the end of the column).

Because you’ll have (probably) kept using your card beyond the statement date, you will (probably) never see the running balance drop to ‘0.00’. Don’t be concerned about this – the running balance will, like the bill, carry over to the next month (unless the current month is December, in which case you need to create a new spreadsheet for the coming year, as explained later).

What you’ll see when you input a payment is something like this:



The rolling-balance, explained

With the exception of the January sheet, every worksheet in the spreadsheet carries-over the balances from the month before. For example, the first ‘value’ cell at the top of the Visa ‘balance’ column simply refers to the last ‘value’ cell from the previous month’s Visa balance column. Or, in spreadsheet-ese, March’s J4 (top-most cell) equals February’s J42 (bottom-most cell).

NB: It’s therefore crucial that if you add or delete rows from any worksheet, you check that the next month’s sheet is still referring to the final cell in the sheet you’ve altered.

New Year

When you first use the spreadsheet, January’s account details don’t carry on from the previous month – because there’s nothing to refer to. However, when you get to the end of the year you will probably want to set up a new spreadsheet for the next year’s figures, and you will want it to carry on from where the current year’s spreadsheet leaves off.

To create a spreadsheet for the coming year, you need to create a copy of the current spreadsheet, with a new filename (if the spreadsheet you’re currently using is saved as “account balances 2015”, create a copy with the name “account balances 2016”).

You can create that copy either from a blank template (such as the one you downloaded from this website), or you can save your current spreadsheet with the new name and then remove entries (such as day to day shopping, income from eBay sales and such like) that don’t apply to the next year – this might sound a bit arduous but it only takes a few minutes and you only have to do it once a year.

Once you’ve created your new spreadsheet, click the ‘Jan’ tab at the bottom of the screen and link the Balance columns to the previous year’s ‘Dec’ tab. For example, to make cell J4 (the opening balance for the ‘Visa’ credit card) refer to the closing balance on the previous year’s sheet (which is cell E42 on the ‘Dec’ tab), you might have a formula that looks like this:

='C:\Users\spreadsheets\[account balances 2015.xlsx]DEC'!E42

Checking your bills

As you input your credit card spending onto the spreadsheet, you’re creating something that looks very similar to your next credit card bill – the dates, the descriptions and the amounts should all be the same, and in the same order.

This makes it very easy to check your credit card bill, and there’s a column on each ‘credit card’ section of the spreadsheet (and on the current and savings account sections), where you can cross-off each item as you’ve checked it. These are the thin columns D, I, N and S. Just stick any character (I use an ‘x’) in that column as you check the item on your card’s bill or your bank statement. This is just to let yourself know that you’ve checked the amount.

In doing this, you’ll spot any data-entry mistakes you might have made (which would lead to incorrect balances being calculated), and also help you track-down items you may have missed (which again would lead to incorrect balances). It could also help you spot credit card fraud; I once paid for a meal in a restaurant in Germany, and the purchase later showed-up on the credit card bill as you’d expect. However, further down the bill was another charge to the same restaurant, dated several days later (by which point I was in France). This was fraud, and very easy to spot if you use this spreadsheet to check your bills, but can be overlooked if you don’t.

Conclusion

I do hope the spreadsheet proves as useful to you as it does to me. It may take a month for the sheet to become accurate, and for the balances shown on it accurately reflecting the balances as they really are. Once it does, it should prove itself invaluable. It might not stop the balance on your credit card bill from looking horrific, but it will stop it coming as a nasty surprise.

OTHER EXCEL ITEMS

Importing data from an Access Database into Microsoft Excel

Keeping track of your credit card and bank account balances with Excel

How to write a simple VBA macro that will read and write values to and from Excel cells

Excel's "What-if Analysis" Goal Seeker

Importing data from a text file into Microsoft Excel

Calculating the difference between two dates

How to add a button to an Excel worksheet, and link the button to a VBA macro

An Excel macro to show the effect of inflation on spending-power

An Excel spreadsheet that calculates how much you've spent on cigarettes

Password-protecting and encrypting Excel spreadsheets

How to record and modify an Excel macro

Naming cells and ranges in Excel

How to adjust the widths of columns in an Excel spreadsheet

Filtering data with Excel 2007/2010

Inverting Numbers in a range of cells

Getting a summary of numeric data in a range with Excel

Website design Copyright (C) 2007-2014 Mike Perris.