Bank Reconciliation Formula

Updated September 15, 2023

Table of Contents

• What is the Bank Reconciliation Formula?
  • Journal Entries
  • Procedure
  • Format
  • Examples
  • Importance
  • Reason for Difference

What is Bank Reconciliation Formula?

Bank reconciliation formula lets us compare if the account balance of a company’s cash book and the bank’s passbook is equal.

It is a crucial process that every company performs to ensure the accuracy of their financial records. It involves periodically comparing the company’s transaction records with the bank’s records to find any incorrect entries, missing transactions, or fraudulent activities. This way, it helps businesses ensure that their financial statements are accurate.

Key Highlights

• The bank reconciliation formula is useful for comparing the account balance of a company’s cash book with the bank’s passbook.
• It helps identify errors, omitted and incorrect or fraudulent transactions in the company’s and bank’s record statements.
• Various reasons like deposits in transit, outstanding checks, NSF checks, and more usually cause a difference in the balance.
• Companies can perform reconciliation every month, week, or at the end of every business day.

Journal Entries for Bank Reconciliation Formula

Companies keep journal entries of those transactions that appear on the bank statements or passbooks but are not yet recorded in the company’s cash book or record. These entries are known as adjustments to books or adjustments to balance per book.

The transaction details which get entered in the journal are as follows:

• Bank services or maintenance fees
• Deposit in transit
• Outstanding checks
• NSF (Non-Sufficient Funds) checks
• Bank interest earned
• Automatic withdrawals from banks like electricity bills, loan payments, etc.
• Other miscellaneous fees.

Bank Reconciliation Procedure

The following is a step-by-step procedure for using bank reconciliation formula,

Step #1: Gather Data
Gather financial data from the account section for the month or period you make a reconciliation statement. Then, prepare its financial reports and take a copy of the bank statements for that period.

Step#2: Compare the Debit and Credit transactions
Compare the balance amount and each credit and debit transaction details from the bank’s passbook and the company’s cash book. Also, identify any additional or missing transaction entries in both books.

Step #3: Adjust Bank Statements
Make necessary adjustments in the bank statements by adding transaction details present in the company’s cash book but not in the passbook. For example, you can add a deposit in transit and an outstanding check amount.

Step #4: Adjust Company Statements
Adjust the company’s statement by adding transaction details present in the bank’s passbook but not in the company’s record. For example, interest earned, bank fees, penalties, etc.

Step #5: Compare the Adjusted balance
After adjusting all the differences, compare the adjusted balance of bank and company statements. If the adjusted balance is the same, bank reconciliation is complete. If not, repeat the process by identifying and adjusting the transaction amount.

Format for Bank Reconciliation Statement

The format or representation of bank reconciliation statements varies depending on the company’s accounting guidelines and structure. The two formats that companies widely use are as follows.

Format #1

The standard statement includes the following data

1. Company Details:

• Name of the Company and statement date (when the company prepares the reconciliation statement).
• Some companies may even mention the account number of the bank.

2. Bank statement or Passbook details:

• Balance as per passbook.
• “Add” section- includes a list of deposits or any additional amount in the company’s record book but not present in the passbook.
• “Less” section- includes all missing deductions not mentioned in the passbook.
• Adjusted Bank Balance (Balance + total “Add” amount – total “Less” amount)

3. Company’s Records Details or Cash Book:

• Balance as per company’s record or cash book.
• “Add” section- include a list of deposits or any additional amount present in the bank’s passbook but not in the company’s cash book.
• “Less” section-include all deductions not present in the cash book, like bank service charges, NSF checks, etc.
• Adjusted Book Balance (Balance + total  “Add” amount – total “Less” amount).

Sample:

Format #2

The following is another format also adopted by most companies, which includes,

1. Company and statement details
2. Balance as per bank’s passbook or company’s cash book
3. “Add” section
4. “Less” section
5. Balance as per company’s cash book or bank’s passbook.
6. Adjusted Balance

Sample:

Examples of Bank Reconciliation Formula (With Excel Template)

Let’s take the following examples to understand the calculation of the bank reconciliation formula in a better manner.

Bank Reconciliation Formula – Example #1

Let’s say you own a small business named “Moonstar Electronics” and want to make a bank reconciliation statement for August 2023 using the following data.

1. The company’s cash book shows a balance of $8,700, while the bank passbook shows $9,000.
2. The company deposited a check of $800 to the bank, but it did not appear on the bank statement.
3. The bank misreported an amount of $550 as $500.
4. The bank’s service charges of $50 and interest earned of $100 are not entered in the cash book.
5. A client deposited $500 directly into the company’s bank account, only mentioned in the passbook.
6. A cheque of $550 was issued and was only mentioned in the bank’s passbook.
7. The company issued an uncleared or outstanding cheque of $100 not yet cleared by the bank.
8. The company’s deposit of $50 has been returned as NSF by the bank.

Solution:
Let’s enter all the data in Excel in the following manner.

Step 1: Enter amounts not present in the company’s cash and bank’s books in the “Add” section of their respective statements, as shown below.

Step 2: Enter all deductions amount in the “Less‘ ‘ section in the respective section. Like bank service fees in the cash book and outstanding checks in the bank book, as shown below.

Step 3: Calculate “Adjusted Balance”.
Adjusted Balance = Balance as per Bank Book + “Add” amounts – ”Less” amount
= 9,000 + (800 +50) -100
= 9,750
Similarly, calculate the adjusted balance for the cash book.

The data in Excel will look like,

Finally, the bank reconciliation statement of your “Moonstar Electronics” company will look like this,

Bank Reconciliation Formula – Example #2

Let’s prepare a reconciliation statement for M/s Helen Industries from the following particulars as of 31 May  2023.

1. Balance as per company’s cash book $30,500 and bank’s passbook $27,500.
2. Checks deposited into the bank but not credited by the bank  $7,450
3. Checks issued but not presented for payment $4,400
4. Bank interest earned $100
5. Bank service fees $50

Solution:

Enter the data in Excel as shown below,

The final bank reconciliation statement of M/s Helen Industries for 31 May 2023 will look like,

Importance of Bank Reconciliation Formula

This statement is every company’s most important activity for the following reasons.

• Identify discrepancies: It helps to identify any errors, missing, and incorrect transactions in the record statements.
• Transparency: By comparing the bank’s and company’s record statements, one can ensure clarity and accuracy in their financial statements.
• Detecting fraud: It also detects any fraud, missing funds, suspicious transactions, and money laundering activities by the company and bank.
• Manage cash flow: Reconciliation statements provide details of outstanding checks and deposits that are not yet cleared from the bank. It helps to manage cash flow and maintain sufficient cash in the account.
• Tax Payment: It helps companies to pay the right amount of taxes. They might pay more or less tax than expected if there are incorrect financial statements.

Reason for Difference

The following are some basic reasons that may cause differences or mismatches in the balance of the company’s cash book and the bank’s passbook.

1. Outstanding Checks
Outstanding checks means checks issued by the company but not recorded in the bank statement. It happens when the company enters the details of the checks in its cash book but is not deposited or yet cleared from the bank account.

2. Deposit in Transit
A deposit in transit occurs when a company deposits cash or checks at month-end or too late, and the bank does not record those details on the same day or month. Here, the company has already entered the cash or check details in its book. It may cause a difference in the balances.

3. Not sufficient funds (NSF) checks
When a client or customer deposits a check in the account, and the issuer’s account has insufficient funds, the bank returns the checks to the depositor as NSF checks. The banks may charge processing fees to depositors and issuers. This fee is only in the bank passbook and not in the company’s record.

4. Incorrect Entry of Data
Banks and companies’ financial personnel (accountants) may miss entering or wrongly entering the amount in their ledgers or statements.

5. Bank Fees
Banks may charge service or maintenance fees automatically detected from the company’s account. This amount gets added to reconcile the adjusted balance.

6. Bank Interest Earned
Banks may also deposit interest depending on the amount balance in the account for a certain period and get recorded in the bank’s passbook.

Frequently Asked Questions (FAQs)

Q1. What are the other types of reconciliation statements?

Answer: The other types of reconciliation are as follows:

1. Vendor Reconciliation
   It includes comparing the account details between the vendor or supplier statements and company statements to identify any pending purchase orders or other cash payments.
2. Customer Reconciliation
   It is comparing a company’s record statement with a customer’s statement. It helps to identify any missing unpaid invoices, overpayments, etc.
3. Business-specific reconciliation
   This reconciliation is specific to particular business operations, like payroll reconciliation, assets reconciliation, inventory reconciliation, etc.
4. Inter-company reconciliation
   It involves comparing accounts and ledgers between divisions or subsidiaries within the parent company.

Q2. How often should you do bank reconciliation?

Answer: Companies or individuals must do bank reconciliation monthly or weekly. It can also be done at the end of each business day if there are large transactions. Regularly making reconciliation statements will help ensure that a company’s financial records align with the bank statement. Also, if there are any discrepancies or errors, they can be quickly identified and addressed.

Q3. What are the common problems with bank reconciliation statements?

Answer: There are common problems that arise during bank reconciliation are as follows:

• Important data may not be available during the preparation of reconciliation statements.
• There may be duplicate entries in the company’s record or bank’s statement.
• Checks returned from the bank but are still in the company’s record.
• Banks charge service fees for account maintenance, electronic payments, etc. This fee amount is unknown to the company and only appears in the bank’s statements.

 Q4. Who performs bank reconciliation?

Answer: The company’s accounting or finance department makes bank reconciliation statements. These finance professionals compare the company’s internal financial records (cash book or ledger) with the bank statement provided by the company’s bank.

1. Trial Balance
2. Types of Accounting
3. General Ledger Accounting
4. Cost of Sales Formula