Accounts Payable Reconciliation Template for Excel

Quick Answer: An accounts payable reconciliation checks three things: that the total of your open vendor invoices equals the AP balance in the general ledger, that each vendor's statement agrees with what you show as owed to that vendor, and that the payments you recorded actually cleared the bank. In Excel, that means a summary tab, one tab of open invoices, one of vendor statement balances, and one of bank-cleared payments, tied together with SUMIFS and XLOOKUP.

Accounts payable reconciliation proves that what your books say you owe suppliers is right. You compare your AP ledger with the general ledger control account, with the statements vendors send you, and with the payments that left your bank account. An Excel template makes the comparison repeatable each month, with every difference listed and explained rather than buried in a plug figure.

What exactly are you reconciling?

Three comparisons, each catching different errors:

1. AP subledger vs general ledger. The sum of every unpaid bill in your AP detail (aged payables report) should equal the balance of the Accounts Payable account on the balance sheet. A gap usually means a journal entry was posted straight to the AP account without a vendor attached.
2. Your records vs each vendor's statement. The vendor's statement shows what they think you owe. Differences point to invoices you never received or entered, payments they haven't applied, credit notes, or duplicates.
3. Recorded payments vs the bank statement. A payment marked as made in the books should appear on the bank statement. If it doesn't, it may still be in transit, it may have been voided, or it may never have been sent.

The third comparison is the one most AP templates leave out, and it's often where the money actually goes missing.

The template layout

Build one workbook per month with these tabs:

Format each data tab as an Excel table so formulas use names like Invoices[Open amount] and grow as you paste in new rows.

Step 1: tie the AP detail to the general ledger

On the Summary tab:

• AP per general ledger: type the closing balance of the Accounts Payable account from the trial balance.
• AP per open invoices: =SUM(Invoices[Open amount])
• Difference: the first minus the second.

If the difference isn't zero, look first for manual journal entries to the AP account during the month, then for bills dated after the cutoff that were included in one report but not the other, and finally for voided payments that reversed in the ledger but not in the invoice detail.

Step 2: reconcile each vendor's statement

For each vendor that sends a statement, record their closing balance next to yours. Balance per books comes from the open invoices tab:

=SUMIFS(Invoices[Open amount],Invoices[Vendor],[@Vendor])

Microsoft's SUMIFS reference covers the syntax if you add more conditions, such as invoice dates on or before the statement date.

A worked example for one vendor (hypothetical):

Each line becomes a row on the Reconciling items tab with an action: enter the credit note, void the duplicate, request a copy of invoice 7802. Timing items like the in-transit payment need no action other than confirming next month that the vendor applied it.

How do you confirm payments actually cleared the bank?

Paste the month's bank statement rows into the Bank tab. If your bank only provides PDFs for the period you need, convert the bank statement PDF to Excel first; QuickBankConvert reads the transactions into date, description and signed amount columns, with the statement's opening and closing balance shown so you can confirm nothing was dropped.

Then, on the Payments tab, find each payment on the bank side. For checks, match on check number; for transfers, match on amount within a few days. XLOOKUP does the first case:

=XLOOKUP("*"&[@Reference]&"*",Bank[Description],Bank[Date],"Not found",2)

The final argument, 2, turns on wildcard matching, so a check number anywhere in the bank description is found. Microsoft's XLOOKUP reference lists the match modes. For amount-based matching, look up the negative amount, since money out is negative on the bank tab:

=XLOOKUP(-[@Amount],Bank[Amount],Bank[Date],"Not found")

Set Status with an IF: "Cleared" when a date is found, "Outstanding" otherwise. Then review the outstanding list:

• Recent and normal: checks mailed in the last days of the month. They should clear next month.
• Old outstanding checks: follow up with the vendor; they may never have received the check.
• Payments in the books that will never clear: a check that was voided or never printed, still recorded as paid. Reverse it, which puts the invoice back into open AP.
• Bank debits with no matching payment in AP: an automatic debit from a vendor that nobody entered, or an unauthorized charge.

The same outstanding checks list feeds your bank reconciliation, so the two jobs share work. The outstanding checks guide explains how they're handled on that side.

Why doesn't my AP aging match the balance sheet?

The usual causes, roughly in order of how often they turn up:

1. A journal entry debited or credited AP directly, so it has no vendor or invoice behind it.
2. A vendor credit was recorded but never applied to an invoice, leaving both open.
3. The aging report was run as of a different date from the balance sheet.
4. A bill was entered in the wrong period.
5. Foreign currency bills were revalued in the ledger but not in the report, or vice versa.
6. A payment was recorded as an expense instead of against the bill, so the bill stays open and the expense is doubled.

Aging buckets for the open invoices tab

Add a Days past due column, =MAX(0,$B$1-[@[Due date]]) with the reconciliation date in B1, and a bucket column:

=IFS([@[Days past due]]=0,"Current",[@[Days past due]]<=30,"1-30",[@[Days past due]]<=60,"31-60",TRUE,"60+")

A PivotTable of open amount by vendor and bucket gives you the aged payables summary for the month-end file.

Month-end checklist

• Export open invoices and payments as of the last day of the month.
• Pull the AP control balance from the trial balance.
• Collect statements from your largest vendors.
• Paste or convert the month's bank statement rows.
• Clear the Summary differences to zero or list each one as a reconciling item.
• Save the workbook with the vendor statements. The IRS recordkeeping page notes that good records help you keep track of deductible expenses and support items on your return, and paid invoices with proof of payment are part of that.

Auditors usually ask for exactly this file; the bank audit report guide describes what they look for.

Related guides

• Bank statement reconciliation guide
• How to do account reconciliation
• Reconcile balance sheet accounts