Easy Deposit in Transit Ending Formula (With Deep-Dive)


Today, you’ll learn everything about deposit in transit ending formula.

Things you need:

  • Book debits (accounting record)
  • Bank statement credits (deposits recorded by a bank)
  • Previous months deposits in transit, if applicable
  • SUMIF function formula

Deposits in transit need much effort to deal without enough skills and techniques. First of all, there are no common references between accounting records and bank records regarding bank deposits. An accounting office has their own journal entry voucher numbers, while a bank has their own reference numbers to record cash deposits. Furthermore, manual matching of amounts is required to identify in-transit deposits. All book receipts are compared with bank statement credits. Likewise, reconciling deposits in transit is harder to accomplish if accounting records are problematic. Accounting should have sub-schedules when deposits are made in batches. Hence, a better method is required to calculate deposits in transit accurately.

The deposit in transit ending formula in this post can increase accuracy and efficiency in calculating the remaining deposits in transit at the end of a month. For instance, all data required for the reconciliation are described. The most common are book debits of a cash book and bank credits of a bank statements. In addition, an excel function is used to match accounting records and bank statements to identify the remaining deposits that have not yet cleared in a bank. The SUMIF function is used to reconcile accounting records and bank records. That function dramatically increases the accuracy and efficiency of reconciliations. Also, the last step is to sort unmatched deposits to calculate the ending balance to be reported in the reconciliation.

Requirements of the formula

Book debits

A cash deposit is debited to a cashbook. First, deposits are debited to a bank account and a credit to corresponding income/expense/asset accounts. It is also preferred to record all collections and deposits at the same time to reduce the number of accounting entries. Furthermore, it is debited because it increases the assets of an organization. Debit journal entries are used to increase assets and expenses. In addition, all adjustments that increase cash are recorded on the debit side. Examples are recognition of unrecorded deposits and interest income. Hence, when reconciling deposits in transit, all book debits are used in a formula to calculate the ending deposits in transit on a monthly basis.


Consequently, the formula requires that all bank debits are ready for reconciliation with complete references. For instance, an excel sheet has book debits on the month reconciled. Data is required to use the formula correctly. Also, one column is added for encoding deposit batch numbers. Batch numbers are used to match book debits with the bank statement credits later on. In addition, the references should include dates, descriptions, amounts, and other required information. They are needed after deposits in transit are identified.

The ending formula – Using the SUMIF function

Here is the formula:

Prior months’ remaining deposits in transit + book debits – bank statement credits = ending deposits in transit

Bank statement credits

Bank credits in a bank statement are all money deposits in a depositor’s account. For instance, a cash deposit appears on the credit side of the statement. The deposit increases an account balance. Furthermore, online fund transfers are recognized by a bank through credit entry as well. Both transactions are credited for the recognition of liabilities. In addition, all interest income, unrecorded deposits and credits are included in the bank credits. These are mostly unrecorded in the books of the depositor. Hence, to reconcile deposits in transits, the formula requires using bank credits.

Previous months deposits in transit

Previous months’ deposits in transit are also needed to complete the deposit in transit formula. Certainly, they are to be cleared first. All deposits that appear in the bank statement credits should not appear in the list anymore. Also, without correctly calculating the prior months’ in transit deposits, the amount derived from an ending deposit in transit formula is usually erroneous. Erroneous amounts usually result in an unbalanced bank reconciliation statement. Furthermore, beginning uncleared deposits should usually be clear in the next month. It is a sign that there is a strong internal control in place.

Bonus tips

The SUMIF function is usually used to apply the deposit in transit ending formula. First, use deposit batch numbers in book debits and prior months in transit deposits. Each book debits has a unique deposit batch number. Next, subsequently put the batch numbers to bank credits. A batch number is usually deposited several times, hence they can appear in bank credits several times. Then, calculate the total bank credits per batch numbers using the SUMIF function. It is a process to match book debits and bank credits. Afterwards, deduct the total from the book debits to arrive at remaining balances. This is the last step in applying the formula to actual reconciliation. Finally, the remaining balances are usually deposits in transit. Now we have applied the formula correctly.

Click on the link to view an actual example.

Recent