Bank reconciliation: example, format, template

A bank reconciliation example, worked line by line

A company’s QuickBooks Online checking account shows $12,558.00 at the end of the month. The bank statement for the same account, same date, shows $12,160.00. The two numbers are $398 apart, and most of that gap is not a mistake. The statement this example builds is also a free Excel template, with no email needed.

Working through the statement against the register turns up seven things:

1. A $750 deposit was recorded on the 30th and did not reach the bank until the 2nd.
2. Check 1042 for $420 was mailed and has not been cashed.
3. Check 1045 for $185 has not been cashed either.
4. The bank paid $60 of interest that nobody entered.
5. The bank charged a $30 monthly service fee that nobody entered.
6. A customer’s $250 check, deposited mid-month, came back unpaid, and the bank charged $15 for the returned item.
7. Check 1039 was written for $86.00 and entered in the books as $68.00.

The first three belong on the bank’s side, because the books already know about them and the bank has not caught up. The last four belong on the books’ side, because the bank already knew and the books did not. Getting that split right is most of what people get wrong.

Bank reconciliation, end of month	Bank	Books
Balance per bank statement / per books	12,160.00	12,558.00
Add: deposit in transit	750.00
Less: outstanding check 1042	(420.00)
Less: outstanding check 1045	(185.00)
Add: interest paid by the bank		60.00
Less: monthly service fee		(30.00)
Less: customer check returned unpaid		(250.00)
Less: returned-item fee		(15.00)
Less: check 1039 entered as $68.00, written for $86.00		(18.00)
Adjusted balance	12,305.00	12,305.00

Both sides land on $12,305.00, and every dollar of the original $398 gap has a name attached to it.

That is a reconciliation. Not two numbers being equal, but every difference between them being identified and explained. The adjusted figure is also the true cash balance: the $12,558.00 in the books was never right, and neither, as a measure of what the company can spend, was the $12,160.00 on the statement.

It is a plain workbook: no email, no sign-up, and it opens in Google Sheets as well as Excel.

How do you do a bank reconciliation, step by step?

1. Get the statement and the book balance for the same date. The statement is the document from the bank, not a balance read off the banking app, which moves every day.
2. Tick off what appears in both. Every deposit and payment on the statement gets matched to its entry in the books, by amount and by check number where there is one.
3. List what is in the books but not on the statement. Those are deposits in transit and outstanding checks, and they adjust the bank side.
4. List what is on the statement but not in the books. Interest, fees, returned checks and anything else the bank did first. Those adjust the book side.
5. Compare the two adjusted balances. If they agree, the account reconciles. If they do not, whatever is left is an error, on one side or the other, and the size of the gap is the best clue to where it is.
6. Record the book-side items and keep the evidence. Enter or correct every book-side item so the books show the adjusted balance, and save the statement and the reconciliation together.

The formula, in the only form worth memorizing

Adjusted bank balance = statement balance + deposits in transit - outstanding checks and payments ± bank errors.

Adjusted book balance = book balance + interest and other credits the bank recorded - fees, charges and returned checks the bank recorded ± errors in the books.

The reconciliation is complete when those two adjusted figures agree. The asymmetry between the two lines is the whole logic of the exercise: the bank side gets adjusted for timing, and the book side gets adjusted for information. Things the bank has not seen yet go on the bank side. Things the bank knew before you did go on the book side.

That split has a practical consequence. Nothing on the bank side needs an entry in your books, because those items will clear on their own next month. Everything on the book side does, because until someone enters it, the books are wrong.

What are common bank reconciliation items?

Five kinds of difference between the bank and the books are normal, and none of them is an error.

Deposits in transit are payments recorded in the books that the bank has not yet credited. A deposit dropped on a Friday afternoon is real money and a real accounting entry; it just is not on a statement that closed the same day.

Outstanding checks and payments are the mirror image. The check was written, the bill was paid, the expense was recorded, and the payee has not cashed it. An outstanding check is not a missing transaction and it should not be voided to make a reconciliation easier. Voiding it puts back an expense the business did incur.

Bank fees and service charges flow the other way. The bank knew about them at the moment they were assessed; the books find out when someone reads the statement. Until they are entered, the register overstates cash.

Interest and other bank-side credits are the same problem with the opposite sign. Small enough to ignore for years, which is exactly why they accumulate into an unexplained difference nobody can trace.

Customer checks returned unpaid, usually called NSF checks, are deposits the books counted and the bank later reversed. The money never arrived, so cash comes down and the customer owes the amount again.

Difference	Known first to	Until it is dealt with	Clears when
Deposit in transit	The books	Books show more cash than the statement	The bank credits it
Outstanding check or payment	The books	Books show less cash than the statement	The payee cashes it
Bank fee or service charge	The bank	The register overstates cash	Someone enters it from the statement
Interest or bank-side credit	The bank	The register understates cash	Someone enters it from the statement
Customer check returned unpaid (NSF)	The bank	The register overstates cash	The deposit is reversed in the books

Everything else that shows up in a reconciliation is an error, either yours or the bank’s, and needs correcting rather than adjusting for.

The journal entries for a bank reconciliation

Every item on the book side of a bank reconciliation needs an entry, dated on or before the statement date, so the adjusted book balance becomes the actual book balance. Items on the bank side need none. Using the book-side items from the example at the top of this page, interest of $60, a $30 service fee, a $250 customer check returned unpaid with a $15 fee, and a check written for $86.00 but entered as $68.00, the entries are:

Item	Debit	Credit	Amount
Interest paid by the bank	Checking	Interest income	60.00
Monthly service fee	Bank service charges	Checking	30.00
Customer check returned	Accounts receivable (the customer)	Checking	250.00
Returned-item fee	Bank service charges	Checking	15.00
Check 1039 under-recorded	The expense the check paid	Checking	18.00

In QuickBooks Online, most of these should not be journal entries at all. Fees and interest can be entered on the reconcile screen itself or as an expense and a deposit. The returned check is best handled by reversing the original payment against the invoice, so the invoice reopens and the customer’s balance is right. And check 1039 should be corrected by editing the original check from $68.00 to $86.00, not by posting an $18.00 entry beside it. A correcting entry makes the account tie while leaving a check in the register that does not match the one the bank paid.

If the returned-item fee is going to be passed on to the customer, it goes to their balance rather than to bank charges. That is a policy decision for the business, and it has to be stated in the terms the customer agreed to.

Errors on the bank’s side, and a second example

Banks post the wrong amount, credit the wrong account, and duplicate a debit occasionally. It is rare enough that assuming it first will waste a day, and common enough that refusing to consider it will waste a week. The test is documentary: the image of the cleared check settles the argument.

A second, shorter example shows where a bank error goes. The books show $8,645.00 and the statement shows $8,940.00. A $1,500 deposit is in transit and a $2,000 check is outstanding. The bank charged a $25 fee nobody entered. And check 2210, written and correctly entered for $1,240.00, was paid by the bank as $1,420.00.

Bank reconciliation, end of month	Bank	Books
Balance per bank statement / per books	8,940.00	8,645.00
Add: deposit in transit	1,500.00
Less: outstanding check	(2,000.00)
Add: bank error, check 2210 overpaid	180.00
Less: bank service fee		(25.00)
Adjusted balance	8,620.00	8,620.00

The $180 goes on the bank side because the books are right and the bank is not. It needs no entry in the books. It needs a call to the bank, because the correction has to arrive on a future statement or the same gap reappears next month, and until it does, the $180 carries forward as a reconciling item.

Errors in the books, which are most of them

A transposed digit. A payment entered twice, once by hand and once through the bank feed. A deposit dated to the wrong month. A deposit entered as a payment. A transaction categorized to the right amount and the wrong account, which does not break the reconciliation at all and is the reason a clean reconciliation proves less than people think.

The useful diagnostic is the shape of the difference:

• A gap that exactly matches a line on the statement is almost always one transaction, missing or duplicated.
• A gap divisible by 9 points to a transposition. A check for $86.00 entered as $68.00 leaves $18.00, which is 9 times 2. Any two swapped digits leave a difference that divides evenly by 9.
• A gap that is twice the amount of a transaction points to a sign error: a $400 deposit entered as a $400 payment moves the books $800 in the wrong direction. Halve the difference and look for a transaction of that size.
• An uneven figure spread across many cents is usually several small items stacked together, and it is faster to compare statement totals period by period than to hunt transaction by transaction.

How often to reconcile, and what waiting costs

Monthly, as soon as the statement arrives, is the standard for most businesses. An account with heavy volume, or one where fraud would be expensive, is worth reconciling weekly against the bank’s online activity, with the formal reconciliation still done against the monthly statement.

The deadline is partly legal. Under the Uniform Commercial Code, a customer has to examine statements with “reasonable promptness” and report an unauthorized signature or alteration. A customer who does not report one within one year of the statement being made available loses the right to claim it against the bank at all, and once a customer has had a reasonable period, never more than 30 days, to spot a forgery, the bank is protected against further forgeries by the same person. A business that reconciles once a year, at tax time, can lose money that a monthly reconciliation would have recovered.

Outstanding checks that never clear

An outstanding check older than a few months deserves a phone call, not patience. Most are lost in the mail or sitting in a drawer, and the payee is still owed the money.

Under the Uniform Commercial Code, a bank has no obligation to pay an uncertified check presented more than six months after its date, though it may choose to. Stale does not mean the debt went away. The business still owes the payee, and the check stays on the outstanding list until it is reissued or resolved.

What it should not become is income. Voiding an old check into miscellaneous income or back to the expense account removes a real liability from the books. Every state has unclaimed property rules that require uncashed vendor and payroll checks to be reported and turned over after a dormancy period, commonly one year for wages and three to five years for vendor payments. The outstanding check list on a reconciliation is where that obligation first becomes visible.

The limits of a bank reconciliation

Reconciliation tests one thing: that the cash recorded in the books matches the cash the bank holds, once timing is accounted for. It says nothing about whether the money was recorded in the right place.

A $4,000 owner draw coded to contract labor reconciles perfectly. So does a loan repayment coded entirely to interest expense, an equipment purchase expensed instead of capitalized, and a customer payment applied to the wrong invoice. In every one of those cases the cash moved, the amount is right, the date is right, and the financial statements are wrong.

Three failures survive a clean reconciliation reliably enough to be worth checking for on their own. Duplicate transactions where both copies were reconciled in the same period, which nets to zero on the reconciliation screen and doubles an expense on the profit and loss. Miscoded transactions, which never touch the reconciliation at all. And a wrong opening balance, where the account has reconciled cleanly every month since the file was created and started from a figure that was never verified against a statement.

The format, and doing it in Excel

A bank reconciliation statement, in its traditional form, has two columns side by side, one for the bank and one for the books. The bank column starts at the statement balance and adjusts for timing. The book column starts at the book balance and adjusts for information. Both end at the same adjusted figure, and the supporting detail lists every reconciling item individually so the next reader can follow it. The heading names the account and the statement date, and the bottom carries who prepared it, who reviewed it, and when.

QuickBooks Online performs the same logic inside its reconcile screen and produces a report at the end, which is why most people working in it never build the two-column statement by hand. It is still worth understanding, for two reasons. When the reconcile screen refuses to reach zero, the two-column layout is what tells you which side the problem is on. And for accounts QuickBooks does not connect to, which still includes plenty of merchant accounts, escrow accounts, and older loans, a spreadsheet is the only place the reconciliation exists.

The slow part in a spreadsheet is the matching, not the statement. Export the bank statement and the register for the month into two tabs, amounts in the same sign convention, and add a column beside the register with =COUNTIFS(Statement!C:C, C2), where column C holds the amount. A 0 marks an entry the bank has not seen, which is a deposit in transit, an outstanding check, or a mistake. Run the same formula the other way to find what the bank recorded that the books did not. Two transactions for the same amount will match each other falsely, so check number or date has to break the tie.

To use the template:

1. Enter the account name, the statement ending date, the ending balance from the statement, and the book balance on the same date.
2. List each deposit in transit and each outstanding check or payment on the bank side, as positive amounts.
3. Enter any bank error on the bank side, negative if the bank overstated the balance. List interest, fees, returned checks and book errors on the book side; an error that overstates cash in the books is entered as a negative.
4. Read the difference line. It shows Reconciled only at 0.00, and the reconciliation is not finished until it does.
5. Post an entry, or correct the original transaction, for every item on the book side, then sign the preparer line.

What a reconciliation should leave behind

A completed reconciliation should be reproducible by somebody who was not there. That means the statement it was performed against is retrievable, the reconciliation report is saved, the reconciling items are individually identified rather than summarized into one adjusting figure, and no entry exists whose purpose was to make the difference disappear.

Where more than one person handles the money, the person who reconciles should not be the person who writes checks or makes deposits, or a reviewer should sign it. A reconciliation performed by the only person who could have made the error is a much weaker check.

The line about unexplained differences carries the most weight. QuickBooks Online will let you close a reconciliation with an unexplained difference by posting it to a miscellaneous account. Doing so records that the books and the bank disagree and that nobody found out why, and it moves the problem into next month’s beginning balance, where it is harder to trace.

Common questions about bank reconciliation

What is a 3-way bank reconciliation?

A reconciliation of three figures instead of two, used for accounts that hold other people’s money, such as a law firm’s IOLTA trust account or a landlord’s security deposit account. The bank statement balance, the book balance of the trust account, and the total of the individual client or tenant ledgers must all agree on the same date. A two-way reconciliation can tie while one client’s balance has quietly paid for another’s, which is the error the third column exists to catch. Bookkeeping for law firms works through one.

Who prepares a bank reconciliation?

Usually the bookkeeper, monthly, and someone else reviews it. In a small business the reviewer is often the owner, who reads the reconciliation report and the list of reconciling items rather than redoing the work. What matters is that the person who signs off did not also write the checks or make the deposits being checked.

What are the most common bank reconciliation mistakes?

Voiding an outstanding check to make the difference go away, which puts back an expense the business did incur. Forcing the reconciliation with an adjustment instead of finding the item. Reconciling to a balance read from the banking app rather than to the statement. Starting from a beginning balance that changed because someone edited a reconciled transaction. And treating a reconciliation that ties as proof the books are right, when a payment coded to the wrong account ties perfectly. The section on errors in the books above shows how to read the size of a difference to find the cause.

When the difference has been there for a while

A reconciliation that has not tied for one month is a search. A reconciliation that has not tied for a year, or one that has been forced to zero repeatedly, is a different job: the break has to be found in the period it actually happened, and every reconciliation after it has to be rebuilt on the corrected figure. A bank balance that won’t reconcile covers how to find the period, and the reconcile screen in QuickBooks Online covers the mechanics once you know which month you are rebuilding. Undoing a reconciliation in QuickBooks Online explains what reopening a forced month takes with it. If the difference traces back to customer payments that were received but never grouped into a deposit, the cause is usually a pile-up in Undeposited Funds.

Walking back through closed periods, correcting the underlying transactions, and re-reconciling each month in order is cleanup work, scoped by how many months back the break sits:

$99per month of backlog20% off 6+ months

If more than one reconciled month is affected, or you are not sure how far back the break goes, send the file for a free review and get a written answer on where the difference starts before anything is undone.

Once the file ties, a monthly close reconciles every account against a real statement each month, which is the only thing that stops this from rebuilding. Both are priced in full on the pricing page.