Credit card reconciliation — free Excel template

What’s inside the credit card reconciliation

The template is a single ready-to-use sheet with a frozen header row and one column per field. Its columns are:

• Date
• Merchant
• Amount
• Category
• Card last 4
• Business or personal
• Receipt on file
• Reconciled

What credit card reconciliation is

Credit card reconciliation is checking every charge and credit on a card statement against a receipt or invoice, giving each line an accounting category, and settling whether it was business or personal. When you are done, every line is either explained and marked reconciled, or flagged with the reason it is still open.

The US government’s purchase card program, GSA SmartPay, says reconciling a statement “includes reviewing receipts, citing proper accounting codes, documenting disputed items and completing any other documentation required by your agency.” In this sheet, Category is the accounting code, Receipt on file is the receipt check, and Reconciled is the sign-off.

Source: GSA SmartPay purchase card training, Lesson 9

How to reconcile a credit card statement, step by step

Gather the statement and its receipts, put each charge, fee and credit on its own row, and check that the rows add up to the statement. Then match every row to a receipt, mark it business or personal, chase anything missing or wrong, and mark a row reconciled only when all of that is settled.

• Get the statement PDF and the receipts or invoices for the same billing period.
• Put one row per charge, fee, interest charge or credit. Refunds and credits go in as negative amounts.
• Leave your payment to the card issuer off the sheet, or flag it. It settles the balance; it isn’t a charge.
• Check the total: the Amount column should equal the statement’s purchases plus fees and interest, minus credits.
• Match each row to a receipt or invoice and set Receipt on file.
• Set Business or personal. Personal charges on a company card are repaid under company policy.
• Missing receipt: ask the merchant for a copy. If a reprint isn’t possible, ask the card-issuing bank; GSA SmartPay Lesson 7 gives cardholders this order.
• A charge you don’t recognize, or one that’s wrong: annotate it and resolve it with the merchant and the card issuer.
• Set Reconciled to Yes only when the receipt, category and split are all settled.

Source: GSA SmartPay purchase card training, Lesson 7

What each charge needs behind it (IRS)

IRS Publication 583 says supporting documents for business expenses should show the amount paid and that it was for a business expense, and it lists credit card sales slips, invoices and account statements among them. Travel, meal, gift and transportation charges have stricter rules, set out in Publication 463.

• A statement line shows that you paid, not what for. Publication 583 says proof of payment, by itself, doesn’t establish a deduction, so also keep the sales slip or invoice that shows you incurred the cost.
• Under an accountable plan, employees must account to their employer for advances, reimbursements and allowances, and Publication 463 adds: “This includes amounts you charged to your employer by credit card or other method.”
• For travel, non-entertainment meals, gifts and transportation, documentary evidence is generally required, except for an expense other than lodging under $75, transportation where a receipt isn’t readily available, or meals and lodging under a per diem allowance.
• The evidence should show the amount, date, place and essential character of the expense. A restaurant receipt, for example, shows the restaurant’s name and location, the number of people served, and the date and amount.
• These are tax-record rules. Company card policy usually asks for more, such as a receipt for every charge.

Source: IRS Publication 463 (Travel, Gift, and Car Expenses)

Filling it from a statement PDF

Upload the card statement and ParseForMe reads each transaction’s date, description, amount, debit-or-credit direction and spending category, with a confidence score for the transaction list to guide your review. Map those to Date, Merchant, Amount and Category once, and later statements land the same way. Card last 4 and the three decision columns stay yours.

Merchant is the statement’s own description text, such as “ACME OFFICE SUPPLY #0412 AUSTIN TX”, not a cleaned-up name. ParseForMe gives each amount as a positive number with a separate debit-or-credit direction, so make credits and refunds negative yourself. The card number sits in the statement header, usually masked, so type the last four digits yourself. Business or personal, Receipt on file and Reconciled are decisions no statement makes for you.

Who it’s for

Bookkeepers working through a company card statement at month end, chasing the receipts behind each charge and separating business spend from personal.