Balance Your Checkbook With Templates and Spreadsheets
Avoid Fees, Fight Fraud, and Spot Errors
Do you want to know exactly how much you have available to spend from your bank account? Would you like to catch errors (including bank errors and mistakes you’ve made) before they cause major problems? Balancing your bank account helps you keep track of everything in your account, and it’s a relatively easy task to complete.
In the past, you’d balance your checkbook on paper. There’s nothing wrong with continuing to do it that way, but you might prefer additional options. So, if you’re looking for a tool to help you keep track of things, several of them are listed below. After you get your tool of choice, we’ll discuss how and why to balance your account.
- Balancing on paper: If you like to use a pencil and paper, check the back of your bank statements — there’s probably a template printed there. You can also download and print this PDF form.
- Excel spreadsheets: For Microsoft Office users, there’s a simple Excel spreadsheet template and more robust versions that track your spending categories for you.
- Google Sheets: To track everything in your Google account, use this classic checkbook version or just go with the simplest possible design.
- Open Office: Open source fans also have an excellent template available.
How to Balance your Account
No matter which format you use, you should be able to accomplish two critical tasks:
- Know how much money you have available for spending.
- Review transactions to spot errors or signs of identity theft in your account.
Any of the templates above will help you do that. To get started, grab your most recent account balance. Traditionally you’d get that number from your monthly statement, but you can also get an up-to-the-minute balance online (there’s still value in balancing your account – even if you can see an account balance 24/7).
Enter Your Balance on Form or Spreadsheet
Enter the most recent balance on your form or spreadsheet. Look for an entry with a name like ending balance shown on the statement, previous ending balance, or beginning balance
Add missing deposits and credits to the balance above. These are items that don’t yet show as transactions in your account, but that you’re certain will be credited before you spend any money.
For example, you might have deposited funds at an ATM over the weekend (and you know from experience that your bank will credit the full amount to your account on Monday), or you might know that wages from your employer will arrive in your account by a certain date.
For spreadsheets and templates with separate columns for deposits and withdrawals, deposits go in the column labeled “Credits.”
Subtract Outstanding Items
Subtract outstanding items such as withdrawals and payments that don’t yet show as transactions in your account but you know that they will hit the account soon. For example, you might have written a check to somebody who has not yet deposited it (you’ll have a record of those items in your check register), or you might know that your mortgage payment will be deducted automatically in the near future.
For spreadsheets and templates with separate columns for deposits and withdrawals, withdrawals and payments go in the column labeled “Debits.”
Do the Math
You now have the following:
- The balance reported by your bank
- Additions to your account (Step 2)
- Items to subtract from your account (Step 3)
So add it all up, and you’ll find out how much money you’re really able to spend from your account. Again, be careful in Step 2: Only add items that you are certain will be credited to your account before you spend money.
Look through every transaction on your bank statement (or online) and compare any checks paid to your check register. If you’re not sure what something is, figure it out — it could be a sign of identity theft, a bank error, or a “gray charge” for a subscription fee that you previously authorized but need to cancel. In rare cases, it may mean that you’ve made an error and forgotten about something. One easy way to review transactions is to mark the transactions that are legitimate and that you’ve cross-referenced with your check register.
- If you’re doing this on paper, put a check mark next to each item in your check register after you find it on your bank statement (and put a check mark next to each item on your statement after you find it in your check register).
- If you’re doing this online, flag the transactions that you’ve reconciled with something like a “1.” That way you can easily sort your spreadsheet to find any transactions that are missing that flag. Alternatively, color the cells so that you can visually scan and see if anything is missing.
Bonus steps: Now that you’ve reviewed every transaction, you should be able to avoid any major surprises. But you can do even more to optimize your bank account.
- Set up alerts so that your bank automatically notifies you of any large withdrawals — or if your balance falls below a certain level. Most banks and credit unions can send text or email alerts based on rules that you specify.
- Keep a buffer of cash in your account so that you can absorb any surprises. Insufficient funds fees can easily cost $35 or so, and your bank can still apply those charges even if you opt out of overdraft protection.
- Evaluate overdraft options and decide if you want overdraft protection on your account. If you rely on overdrafts to fund your spending (or “avoid embarrassment”), there’s a problem. But if you keep it there just in case of emergencies (and never pay the fees) it can make sense. Some options, like a transfer from savings or an overdraft line of credit, may be less expensive than traditional overdraft protection.