Accounts receivable reconciliation is the process of checking that the total of your customer balances (the AR sub-ledger) agrees with the accounts receivable control account in your general ledger. When the two do not match, something has been posted to one but not the other, and the difference needs investigating.
This free template compares each customer's sub-ledger balance with their statement, flags differences, and reconciles the total to the GL control account, where the difference should be zero. It works in Excel or Google Sheets.
If your invoices are in Xero or QuickBooks Online, Paidnice posts late fees and interest straight to the ledger, so those charges already match when you reconcile.
Key facts about AR reconciliation
- The sub-ledger total must equal the GL AR control account. In the example, 14,860 against 15,860 leaves a gap of 1,000.
- Receivables arise under the accrual method, which counts income when earned (IRS Publication 538).
- Keep reconciliations 3 years, or 7 years for a bad debt deduction (IRS).
How the reconciliation works
There are two checks. First, per customer, your sub-ledger balance should match what the customer agrees they owe. Second, the total of all customer balances should equal the AR control account in the general ledger.
Sub-ledger total (sum of all customer balances) = General ledger AR control account
If they differ, the gap is a reconciling item to find and fix.
At higher volumes an integrated receivables platform matches each payment to its invoice before the reconciliation starts.
Filled-in example
| Customer | Sub-ledger | Statement | Difference |
|---|---|---|---|
| Northwind Trading | 4,200 | 4,200 | 0 |
| Blue Mountain Cafe | 1,960 | 1,960 | 0 |
| Harbor Logistics | 2,200 | 3,200 | -1,000 |
| Vertex Builders | 6,500 | 6,500 | 0 |
| Total | 14,860 | 15,860 | -1,000 |
The statement total of 15,860 matches the GL, so one missing 1,000 Harbor Logistics invoice explains the gap. Post it and the difference clears.
What should an AR reconciliation template include?
A reconciliation template needs four parts so anyone can follow the check and see who signed it off:
- Header information: the entity, the period end date and the GL account being reconciled.
- Balance comparison: the sub-ledger total against the GL control account, with the difference.
- Reconciling items: each item that explains the difference, such as unrecorded payments or timing differences.
- Sign-off: who prepared the reconciliation, who reviewed it, and the dates.
How do you reconcile accounts receivable?
Total your AR sub-ledger (the sum of all customer balances) and compare it to the accounts receivable control account in your general ledger. Investigate any difference, correct the errors, and confirm the two balances agree.What is accounts receivable reconciliation?
It is the control check that your detailed customer balances add up to the single AR figure in your general ledger.What are common reconciling items?
Unrecorded or deposited-in-transit customer payments, invoices posted to the ledger but not the sub-ledger (or vice versa), credit notes or write-offs applied to one record only, period-end timing differences, and misposted or duplicated amounts.How often should I reconcile AR?
Most businesses reconcile accounts receivable monthly, as part of the period-end close.Does the template work in Google Sheets?
Yes. Open the Excel file or use the CSV. SUM and IF work identically in both.What mistakes should you avoid with this template?
- Plugging the gap. A 1,000 plug entry hides the missing Harbor invoice. Fix: find and post the real item.
- Checking only the total. A 1,400 error on Harbor and a 400 error on Vertex also net to a 1,000 gap. Fix: clear each customer line first.
- Using different dates. A 1,200 payment posted on the 31st in one record and the 2nd in the other creates a false 1,200 gap. Fix: use one report date.
What if the sub-ledger and general ledger do not match?
Trace each reconciling item until the difference is 0. In the example, one missing Harbor Logistics invoice explains the 1,000.
Should a write-off be posted to both records?
Yes. A write-off posted to only one record creates a gap equal to its amount.
Do I need customer statements to reconcile AR?
Not for the control total, but statements catch errors the total hides.
Bottom line: this template suits a business with a few dozen customer accounts and one person closing the books. Move to software when matching payments takes hours each month.
People also read
Independent software reviews and guides from Accounting.Events.
Sources
2 sources, checked by Julie Turnbridge on October 26, 2026
- IRS Publication 538 irs.gov/publications/p538
- IRS irs.gov/businesses/small-businesses-self-employed/how-lon...
Last updated September 28, 2026. This guide is general information, not accounting, tax, or financial advice.
