Mind Like Water

How to balance your checking account using a Google Docs spreadsheet

Have you ever gotten an overdraft fee? It really sucks when your account only has a few bucks in it, and then that check you wrote last week that you forgot about posts and you get slapped with a $39 fee. It happened to me a few times, but then I figured out a way to keep track of everything going in and out of my checking account using a Google Docs spreadsheet.

It's a simple system, pretty similar to balancing a checkbook. The idea is that I need to know at a glance how much money in my account is available and can be spent. In order to do that, I need to know what money is already allocated (for example, when I write a check or have an upcoming bill to pay) and what money is pending (say, a paycheck that comes next week).

Step 1: Write down everything in your checking account

If you're like me, you've probably been keeping track of what's in your checking account in your head. Rent here, check there, spending money there, etc. Write it all down and make sure to give it a good description so you'll remember what you meant next time you open the spreadsheet. I recommend using the headers "Description", "Allocation", "Pending" but you can do whatever works for you.

Here's an example:

Step 2: Calculate your account balance

Once you're done, type "=SUM(B2:B6)" in the B7 cell (or B2:B19 in the B20 column, etc.) and press enter. This will sum up all of the amounts in your allocation column.

If you did it right, the calculated sum should equal the current balance in your checking account. If so, high five! If not, go back and double check that you got all the amounts right.

Step 3: Write down all incoming and outgoing amounts

If you have money allocated, say $100, you should write down -$100 in the pending column on that same row because that money is outgoing. If you have a paycheck on the way, write that in the pending column on a new row, because that money is incoming.

If you type =-← (equals, negative, left) in the pending column and press enter, it will calculate the negative of the amount allocated.

When you're done, type "=SUM(B2:C6)" in the C7 cell (adjust as necessary) and press enter. This will sum up all of the amounts in both your allocation column and your pending column. This is your available spending money!

If you pay by cash and don't use a credit card, don't write down any incoming money (but definitely keep writing down any outgoing money!) because you need an at a glance picture of what's in your account NOW and not tomorrow.

Step 3: Update as necessary when transactions post

Once a week or more: look at your checking account and find the day where your account balance equaled your spreadsheet balance (the bold line in the allocation column). Go through each transaction since then, one by one, and update the spreadsheet, adding/deleting/moving amounts as necessary. It might seem weird at first, but after a while it's second nature.

Nobody wants to be monitoring their checking account all the time, so you can go through this process as often or as little as you are comfortable with. I typically do it once every couple of days, but would recommend that you pick one day a week to balance your account (or accounts, just add a new sheet for each account you have) and put a reminder in your calendar so you don't forget.

That's it! A simple, quick way to keep track of money going in and out of your checking account.