Account reconciliation template (free Excel download)

What does an account reconciliation template need to do?

This account reconciliation template ties any balance sheet account to its supporting detail, lists and ages the reconciling items, and turns green when every cent is explained. It's a free Excel file with a filled-in example. Enter your work email and it lands in your inbox.

Free download

What's in the template?

One Excel workbook with a reconciliation sheet, an aged reconciling items log, a 15-account index, a worked example and a short how-to. Enter your work email and it arrives in your inbox.

Excel workbook · 4 sheets · works in Excel and Google Sheets
  • A ready-to-use reconciliation for any balance sheet account: GL balance, supporting detail and the difference to explain
  • A reconciling items log with type, owner, clear-by date, journal entry flag and age in days (amber over 60, red over 90)
  • An unexplained difference check that turns green at 0.00
  • A reconciliation index: 15 common balance sheet accounts with owner, frequency, risk and status
  • A worked example: accrued expenses with GL 86,420.00 and schedule 84,970.00, a 1,450.00 difference fully explained
Account reconciliation template (Excel): the filled-in example sheet
Free template

Get it in your inbox.

Work email only. We send this one email with the file, and an occasional note you can unsubscribe from. See our privacy policy.

The basics

What is an account reconciliation, and what's the formula?

An account reconciliation proves that a balance sheet balance is right. You compare the general ledger (GL) balance to the supporting detail, then explain every difference.

The formula is simple: balance per GL minus balance per supporting detail equals the difference to explain.

Supporting detail depends on the account. For cash it's the bank statement. For receivables it's the customer aging. For accrued expenses it's the accruals schedule.

A reconciliation is finished when the explained items add up to the difference and the unexplained amount is 0.00. Close enough isn't finished.

Distributors feel this most. Freight accruals, received-not-invoiced goods and rebate accruals all sit in liability accounts that drift quickly. Our month-end close checklist shows where reconciliation fits in the close, and the month-end close automation page covers the wider process.

Inside the file

What's inside the account reconciliation template?

Here's exactly what the workbook contains, sheet by sheet. Nothing more is claimed.

0 of 11 settled
Reconciliation sheet
Reconciling items log
Reconciliation index
Worked example and how-to
How to fill it in

How do you fill in the template each month?

Seven steps, in this order. Do them every period and the close gets calmer.

1Step 1

Pull the trial balance.

Export the GL balance for the account as of period end. Use the closed period, not today's balance.

2Step 2

Pull the support.

Get the bank statement, aging report or schedule dated to the same day. Mismatched dates create fake differences.

3Step 3

Compare.

Enter both balances on the reconciliation sheet. The difference calculates itself.

4Step 4

Investigate.

Log each cause in the reconciling items log: type, amount, owner and clear-by date. Keep going until the unexplained difference is 0.00.

5Step 5

Adjust.

Flag items that need a journal entry. Post it in your ERP and note the reference.

6Step 6

Document.

Save the support next to the reconciliation. Someone should be able to rebuild it a year later.

7Step 7

Review.

A second person checks it, then you update its status in the index.

How to fill in the account reconciliation template: Pull the trial balance., Pull the support., Compare., Investigate., Adjust., Document., Review.
The steps at a glance.
Worked example

How does the Northwind accrued expenses example reconcile, line by line?

Northwind Distribution Inc. (illustrative), account 2100 Accrued expenses, a liability. Period end 31 Oct 2026. Supporting source: the October accruals schedule. This is the filled-in example in the template.

LineWhat it isAmount
Balance per general ledgerAccount 2100 at 31 Oct 202686,420.00
Balance per supporting detailOctober accruals schedule84,970.00
Difference to explainGL minus schedule1,450.00
Item 1: Error in GL28 Oct 2026, utility invoice 88412 posted to 2100 instead of accounts payable 2000. Action: reclass journal, debit 2100, credit 2000. Owner AP lead, clear by 5 Nov. Journal entry: Yes1,200.00
Item 2: Timing31 Oct 2026, payroll tax accrual booked in the GL but the schedule was updated after period end. Action: add to the November schedule. Owner Payroll, clear by 3 Nov. Journal entry: No250.00
Total reconciling items1,200.00 plus 250.001,450.00
Unexplained difference1,450.00 less 1,450.000.00: reconciled

Prepared by A. Patel on 4 Nov, reviewed by J. Moreno on 5 Nov. Only item 1 needs a journal entry. Item 2 clears itself when the schedule catches up.

More examples

What do cash, receivables and prepaid reconciliations look like?

The same logic works on any account. These three examples are for teaching, so the numbers are illustrative and aren't in the template file.

Cash. Bank statement 50,150.00. Add a deposit in transit of 3,200.00. Subtract outstanding checks of 1,450.00. Adjusted bank: 51,900.00. The book balance is 52,400.00, less a bank fee of 40.00 and a returned customer check of 460.00. Adjusted book: 51,900.00. Journal entry: debit bank charges 40.00, debit accounts receivable 460.00, credit cash 500.00.

Accounts receivable. GL control account 418,300.00. Customer aging 419,100.00. The 800.00 gap is a customer deposit credited straight to the control account with no customer. Journal entry: debit 1200 Accounts receivable 800.00, credit 2300 Customer deposits 800.00. Our accounts receivable reconciliation guide goes deeper.

Prepaid. GL 24,000.00. Amortization schedule 22,000.00. October insurance amortization wasn't booked. Journal entry: debit insurance expense 2,000.00, credit prepaid expenses 2,000.00.

Review and sign-off

Who should review an account reconciliation, and when?

A reconciliation nobody checks is only a spreadsheet. Set these rules once and write them into your close policy.

0 of 12 settled
Separate the people
Set a threshold
Age the items
Keep the evidence
ERP how-to

Where do you pull the numbers in Business Central, Dynamics GP and NetSuite?

Get the GL balance from the ERP and the support from its subledger or bank tool. Menu names vary by version and localization, so check yours.

What you needBusiness CentralDynamics GPNetSuite
GL balance at period endG/L Entries or the trial balance report, dated to period endTrial balance from the financial reports, dated to period endTrial Balance report for the period
Bank supportBank Acc. Reconciliation page: Statement Ending Balance, outstanding checks and transactions on the Test ReportReconcile Bank Statements window, checkbook ID and statement ending balanceReconcile Account Statement, after matching bank data
Receivables and payables supportReconcile Cust. and Vend. Accs. report compares G/L net change to customer and vendor ledgersReconcile to GL routine (check the exact menu in your version)A/R and A/P aging reports compared to the control accounts
Template column to fillGL balance and supporting balanceGL balance and supporting balanceGL balance and supporting balance

In Business Central, you can't post a bank reconciliation that still has differences on statement lines. Microsoft documents this on the Reconcile bank accounts page.

Common mistakes

Why won't an account reconcile?

Most breaks come from a short list of causes. Check these before you dig into transactions.

CauseWhat it looks likeFix
Cutoff mismatchSupport is dated a day before or after period endRe-pull both sides at the same date
Direct postingEntries hit the GL control account but not the subledgerReclass the entry through the proper account
Timing itemReal transaction, recorded a few days apartLog it, set a clear-by date, confirm it clears
Wrong accountAn invoice sits in a different liability accountReclass journal, like invoice 88412 in the example
Stale reconciling itemSame amount sits on the log for monthsAge it, escalate it, then clear or write off with approval
Missing bank itemFees or interest on the statement, nothing in the booksPost the journal, then match it
With an AI agent

How does an approval-first AI agent do reconciliation work?

The agent does the pulling, comparing and drafting. A person approves every action before anything posts.

  1. 1
    Agent

    Pull the balances.

    The agent reads the GL balance and the supporting detail from your ERP at period end.

  2. 2
    Agent

    Compare and match.

    It finds differences and proposes the likely cause for each one, such as a misposted invoice or a timing item.

  3. 3
    Agent

    Draft the entry.

    For items that need a journal, it prepares the debit and credit and attaches the support.

  4. 4
    Your team

    Approve or reject.

    The preparer reviews the draft. Nothing posts until a person approves it.

  5. 5
    Our team

    Build and tune it.

    We connect the agent to your ERP, set your thresholds and approval rules, and test it on a closed period first.

FAQ

What do people ask about account reconciliation templates?

Is the account reconciliation template free?

Yes. Enter your work email in the download form and the Excel file arrives in your inbox.

What accounts can I use it for?

Any balance sheet account that has supporting detail: cash, receivables, payables, prepaid, accruals, fixed assets and more. The index lists 15 common ones with owner, frequency, risk and status.

How often should I reconcile balance sheet accounts?

It depends on risk. Cash and clearing accounts are often reconciled monthly or more often. Lower-risk accounts may follow a quarterly schedule. Set the frequency per account in the index and keep to it.

What's the difference between a timing item and an error?

A timing item is correct but recorded in different periods, like the 250.00 payroll tax accrual in the example. An error is wrong in the GL and needs a journal entry, like the 1,200.00 utility invoice posted to 2100.

Does the template replace my ERP reconciliation features?

No. It documents and proves the reconciliation. Business Central, Dynamics GP and NetSuite have their own bank and subledger tools, and the template sits next to them as the review record.

Ready to stop reconciling by hand?

Want an agent to prepare your reconciliations?

We build approval-first AI agents that work inside your ERP and draft reconciliations for your team to approve. Book 30 minutes and we'll look at one account together.

Book Appointment
Sahil kataria (1)
Sahil Kataria

Founder and CEO

amit Kumar
Amit Kumar

Chief Sales Officer

Talk To Sales

USA

+1 270-550-1166

flag

+1 270-550-1166

Phil J.
Phil J.Head of Engineering & Technology​
QServices Inc. undertakes every project with a high degree of professionalism. Their communication style is unmatched and they are always available to resolve issues or just discuss the project.​
Assured

Thank You

Thanks, we’ve got your details. A named member of our team will reply within one business day.