# Accounts Receivable Reconciliation Template With Aging

- Author: PreciseBankFlow (https://www.precisebankflow.com)
- Published: 2025-07-23
- Updated: 2025-07-23
- Category: Account reconciliations
- Canonical: https://www.precisebankflow.com/blog/accounts-receivable-reconciliation-template

> An accounts receivable reconciliation template that ties the aging to the ledger, lists unapplied cash and credits, and checks customer balances.
An accounts receivable reconciliation template proves that the customer aging report adds up to the receivables balance in the general ledger. It lists every difference between the two, including unapplied cash and open credit memos, and then tests a handful of customer balances against invoices and remittances so the total rests on real amounts owed.

## What goes on an accounts receivable reconciliation template?

An AR reconciliation template needs four blocks: a header naming the entity, account and period end; a balance block comparing the aging total with the ledger; a list of reconciling items with dates and owners; and a sign-off block. Everything else, such as customer tests and aging analysis, hangs off those four.

The header looks trivial, yet it causes more rework than any other part. Write the legal entity, the ledger account number and name, and the exact date the balance is taken as of. If the business keeps separate receivable accounts for trade customers, employees or related parties, give each its own sheet. A combined sheet hides which account carries the problem.

The balance block holds three figures. The first is the aging report total at period end. The second is the general ledger balance for the same account on the same date. The third is the difference, which the reconciling items must explain to the cent.

The reconciling-items block is where the work shows. Each line carries a description, the amount, the date the item arose, the customer if there is one, and the person who will clear it. A last column, "unexplained", should show zero when you finish.

The sign-off block records who prepared the sheet, who reviewed it and the dates of each. In an Excel version, lock the formula cells so the difference cannot be typed over. The same layout works in Google Sheets or inside an accounting system's own attachment folder.

### A filled example

The figures below are examples for a business called Example Tools Ltd at March 31.

| Line | Example amount |
| --- | --- |
| Aging report total, all customers | 218,440.00 |
| General ledger, trade receivables | 214,915.00 |
| Difference (aging less ledger) | 3,525.00 |
| Less: unapplied customer payment, Harbor Fabrication, received March 30 | (2,800.00) |
| Less: credit memo CM-118 posted to the ledger, not yet applied in the aging | (725.00) |
| Unexplained difference | 0.00 |

Two items close the gap. Each has a name, a date and a document behind it. That is the standard for every line on the sheet.

## Tying the AR aging report to the receivables ledger balance

The aging report and the receivables ledger should agree because the aging is the detail behind the ledger total. When they differ, either an entry reached the ledger without passing through customer accounts, or the two reports were run at different moments. Find which before testing any single customer.

Run both reports as of the same date and after the same posting cut. A common trap is an aging run on the second business day of the month, after a few receipts dated April 1 have posted. The ledger shows March 31, the aging shows April, and the difference is just timing on your side.

Next, look for entries posted straight to the receivables account. Bookkeepers sometimes record a write-off, a bad-debt recovery or a correction as a journal entry against the account, not against a customer. The ledger moves; the aging does not. Filter the ledger detail for the period by source type and pull out anything that is not an invoice, a receipt or a credit memo.

Then check the scope. Some systems produce an aging for one currency or one branch by default. Others include receivables that the ledger keeps in a separate account, such as retentions on long contracts. The aging and the ledger must cover exactly the same population, or the comparison is meaningless.

Accrued revenue deserves its own line. Revenue earned but not yet invoiced often sits in a separate accrued-receivables account. If someone booked it to trade receivables instead, the ledger runs ahead of the aging until the invoice is issued. The reconciliation of those balances, with the reversal each month, is covered in the [accrual reconciliation template in Excel with reversals](/blog/accrual-reconciliation-template-excel).

## Unapplied cash and open credit memos in receivables

Unapplied cash is a customer payment that has been recorded but not matched to a specific invoice. An open credit memo is a credit issued to a customer and not yet applied. Both reduce what customers owe, but neither appears against an invoice line, so they are the most frequent reason an aging and a ledger disagree.

Systems handle unapplied cash differently. Some show it on the aging as a negative line under the customer. Others leave it out of the aging entirely and park it in a separate account or in a holding status. Know which way your system works before you decide whether the item is a reconciling item at all.

For each unapplied payment, record the payer name as it came through the bank, the amount, the deposit date and the reason it is not applied. The usual reasons are a missing remittance advice, a payment that covers several invoices with no breakdown, a short payment that implies a dispute, or a payer whose name does not match any customer record. Each reason has its own follow-up. A missing remittance means emailing the customer. A short payment means asking sales about the dispute. An unknown payer means checking the bank detail for an account number or reference.

Open credit memos need the same treatment. Note who approved each one and why. A credit memo that sits unapplied for months may belong to a customer who has stopped buying, in which case the customer may be owed a refund. It may also be a credit issued without proper approval, which is a control question, not a bookkeeping one.

The bank side helps here. When receipts are matched from the bank statement to the ledger, an amount that clears the bank but has no invoice match is your unapplied-cash list in waiting. [PreciseBankFlow](/) reads statement lines and flags the deposits that do not match the ledger, which gives the receivables team a starting list for the month.

## How do you check individual customer balances on the AR template?

Checking customer balances means choosing a few accounts and proving each one to outside evidence: the invoices, the payments received in the bank and, where the customer will cooperate, the customer's own confirmation. Pick the largest balances, the oldest balances and any account with unusual activity, then work each to zero unexplained difference.

A practical selection for a small or mid-sized business is the ten largest balances, every balance over 90 days past due, and any customer with a credit balance. Add a customer at random if the reviewer wants an unpredictable test.

For each selected customer, list the open invoices from the aging. Pull each invoice and check the date, amount and terms. Then look at payments received after period end. A payment in April that clears a March invoice is strong evidence that the March balance was real. This subsequent-receipts check is cheap and persuasive, because cash in the bank is hard to argue with.

When a customer sends a statement of account or a remittance listing, compare it line by line in the same way you would compare a vendor statement. Any invoice the customer does not recognise needs a delivery record. Any payment the customer says it made, and you cannot find, needs a bank search by amount and by date.

Keep the evidence with the sheet: the invoice copies, the bank lines for subsequent receipts, and any correspondence. A reviewer should be able to pick up the file six months later and see why each tested balance was accepted.

## Receivables aging buckets a reviewer reads first

Receivables aging buckets group open invoices by how long they have been outstanding, usually current, 1 to 30, 31 to 60, 61 to 90 and over 90 days past due. A reviewer reads the oldest bucket first, then watches how each bucket moved since last month. The shape of the aging says more than its total.

Look at the over-90 bucket as a share of the total, and compare it with prior months. A rising share means collections are slowing or someone is not following up. A sudden drop is worth a question too, because large write-offs or credit memos can empty the bucket without any cash arriving.

Watch for these patterns:

- **One customer dominating the old buckets.** That customer's ability to pay drives the allowance for doubtful accounts.
- **Small old balances that never move.** These are often short payments or bank fees deducted by the customer. They need a decision, not another month on the list.
- **Invoices dated after period end.** These suggest an aging run late or invoices back-dated into the period.
- **Current bucket larger than monthly sales.** That may mean invoices were raised before delivery, which is a revenue cutoff issue.

The allowance for doubtful accounts should be reviewed against this analysis every month or quarter. The reconciliation does not set the allowance, but it gives the evidence the person setting it needs.

## Mistake: netting customer credit balances inside the receivables total

A customer credit balance means the business owes that customer money, usually from an overpayment, a duplicate payment or a credit memo with nothing left to apply it to. Netting those credits against debit balances makes receivables look smaller and hides a liability. List them separately and decide how each will be cleared.

The netting happens quietly. The aging report adds up all customers, debit and credit, and gives one total. That total matches the ledger, so the reconciliation looks clean. Yet a reader of the balance sheet sees receivables lower than the true amount customers owe, and no liability for the refunds due.

Fix it in three steps. First, run the aging with credit balances shown on their own, and total them. Second, for each credit balance, find the cause in the payment history: an overpayment, a payment applied to the wrong customer, or a credit memo issued after the invoice was paid. Third, decide the treatment. Misapplied payments are moved to the right customer. True overpayments are refunded or applied to the next invoice with the customer's agreement. At period end, material credit balances are often reclassified to a liability account so the balance sheet shows both sides.

Credit balances left long enough can turn into unclaimed property obligations in some places, so they should not be ignored as harmless. They are also a known hiding place for misdirected refunds. A reviewer should ask for the list each period, even when it is short.

### Where this template sits among the others

Receivables are one of several balance sheet accounts that need the same discipline. The [prepaid account reconciliation template](/blog/prepaid-account-reconciliation-template) applies it to amounts paid in advance, and [account reconciliation meaning, with examples by account](/blog/account-reconciliation-meaning) explains what each account should be tied to. If your receivables live in QuickBooks Online and you have never closed a period there, start with [how to reconcile in QuickBooks Online for the first time](/blog/how-to-reconcile-in-quickbooks-online-for-the-first-time), so the cash side is settled before the receivables sheet is built.