--- type: Template title: Accounts Receivable Ledger Template (Free Excel) description: A free accounts receivable ledger template for Excel: record invoices and payments per customer with an automatic running balance and per-customer totals. resource: https://accounting.events/resources/accounts-receivable-ledger-template/ tags: [accounts receivable ledger template, accounts receivable subsidiary ledger example, ar ledger excel, customer ledger template] timestamp: 2026-10-19 author: Harry P. Smart fact_checked_by: Julie Turnbridge fact_checked: 2026-10-26 --- AR Ledger Template # Accounts receivable ledger template A free Excel accounts receivable (subsidiary) ledger: record invoices and payments per customer with an automatic running balance. By Harry P. Smart, CPA and Lead Reviewer. Fact-checked by Julie Turnbridge on October 26, 2026. Published June 9, 2026. Updated October 19, 2026. 6 min read. Quick answer **An accounts receivable ledger, also called the AR subsidiary ledger, records every invoice and payment for each customer and keeps a running balance of what they owe.** The total of all customer balances should equal the accounts receivable control account in your general ledger. This free template records each transaction in date order, with invoices as a debit and payments as a credit, and calculates the running balance automatically. A summary section totals each customer's balance with SUMIF. It works in Excel or Google Sheets and reconciles neatly against your general ledger. If your invoices are in Xero or QuickBooks Online, Paidnice sends customer statements from that ledger and posts late fees and interest to it automatically. Preview Xaccounts-receivable-ledger-template.xlsxAccounts Receivable LedgerDateCustomerDescriptionDebitCreditBalance2026-05-02Northwind TradingInvoice4,2004,2002026-05-20Northwind TradingPayment4,20002026-05-10Blue Mountain CafeInvoice1,9601,9602026-04-15Harbor LogisticsInvoice3,2003,2002026-05-18Harbor LogisticsPart payment1,0002,200Invoices debit, payments credit, with an automatic running balance per customer. Free AR ledger template Excel (.xlsx) with an automatic running balance and per-customer totals, plus a CSV. [Download Excel](https://accounting.events/resources/accounts-receivable-ledger-template/accounts-receivable-ledger-template.xlsx)[Download CSV](https://accounting.events/resources/accounts-receivable-ledger-template/accounts-receivable-ledger-template.csv) A clean subsidiary ledger is the backbone of accurate receivables: it shows exactly what each customer owes and underpins your aging and reconciliation. Key facts - The AR ledger is a subsidiary ledger: it holds the detail behind one general ledger control account, and the customer balances must add up to that account ([AccountingTools](https://www.accountingtools.com/articles/what-is-a-subsidiary-ledger.html)). - The IRS says to keep records for 3 years from filing, or 7 years if you claim a bad debt deduction ([IRS](https://www.irs.gov/businesses/small-businesses-self-employed/how-long-should-i-keep-records)). ## How the ledger works Each row is one transaction. Invoices go in the Debit column (they increase what the customer owes); payments and credit notes go in the Credit column (they reduce it). The running balance is the previous balance plus debits minus credits. Running balance **This balance = previous balance + debit (invoice) − credit (payment)** The customer balance summary uses SUMIF to total invoices minus payments per customer, so you always know each customer's outstanding balance. ## What are the key components of an AR ledger? An AR ledger needs enough detail to show what each customer owes and why. Six parts cover it: - **Date:** when each transaction happened, in order. - **Customer:** the account the transaction belongs to. - **Description:** invoice, payment, part payment or credit note, with a reference. - **Debit:** invoices, which increase what the customer owes. - **Credit:** payments and credit notes, which reduce it. - **Running balance:** the previous balance plus debits minus credits. ## Why do you need an AR ledger? The general ledger shows one accounts receivable total. It cannot tell you which customer owes what, which invoices are part paid, or which payment cleared which invoice. The AR ledger holds that detail. It is the source for customer statements and the aging report, and its customer balances must add up to the general ledger control account, which is the check you run at month-end reconciliation. ## Is the AR ledger the same as an aging report? No. The ledger is the full transaction history and running balance per customer; the aging report groups the resulting outstanding balances by how overdue they are. Use the ledger for detail and the aging report for prioritizing collections. ## Does it work in Google Sheets? Yes. Open the Excel file in Google Sheets or use the CSV; the running-balance and SUMIF formulas work the same. ## Example: three customers, one control total Preview transactions, totaled per customer: | Customer | Invoiced (debit) | Paid (credit) | Balance | | --- | --- | --- | --- | | **Northwind Trading** | $4,200 | $4,200 | $0 | | **Blue Mountain Cafe** | $1,960 | $0 | $1,960 | | **Harbor Logistics** | $3,200 | $1,000 | $2,200 | | Total | $9,360 | $5,200 | $4,160 | If the general ledger control account also shows $4,160, the two agree. ## What is an accounts receivable ledger? It is the detailed record of every invoice and payment for each customer, with a running balance of what they owe. Also called the AR subsidiary ledger, it sits behind the single accounts receivable figure in your general ledger. ## What is a subsidiary ledger versus the control account? The subsidiary ledger holds the detail (every customer and transaction); the general ledger control account holds the single total. The sum of the subsidiary ledger balances should always equal the control account, which is what you confirm during reconciliation. ## How do I maintain an AR ledger? Record each invoice as a debit and each payment or credit note as a credit, in date order, and let the running balance update. Review per-customer balances regularly and reconcile the total to your general ledger each month. ## What mistakes should you avoid with this template? - **Entering a payment as a debit.** Northwind's $4,200 receipt would lift its balance to $8,400 instead of $0 and the ledger total to $12,560. Put payments and credit notes in Credit. - **Posting a receipt to the wrong customer.** Post Harbor's $1,000 to Blue Mountain Cafe and Harbor shows $3,200, Blue Mountain $960. The total stays $4,160, so reconciliation misses it. Match each receipt to its invoice reference. - **Skipping the monthly reconciliation.** A $1,000 receipt posted to the control account but not the ledger leaves $4,160 against $3,160. Use the [reconciliation template](https://accounting.events/resources/accounts-receivable-reconciliation-template/) each month. ## More questions about this template ### How do you record a credit note in an AR ledger? Enter it in the Credit column on the customer's account, with the credit note number as the reference. It reduces the balance the same way a payment does. ### How do you write off a bad debt in an AR ledger? Enter it in the Credit column as a write-off so the balance falls to zero. The matching general ledger entry goes to bad debt expense or the allowance. ### How do you find one customer's balance in Excel? Subtract the customer's SUMIF credits total from their SUMIF debits total. The template's summary does this for every listed customer. **Bottom line:** This ledger suits a short customer list with invoices entered manually. Move to accounting software when re-keying invoices takes longer than chasing them. ## Related reading Independent software reviews and guides from Accounting.Events. [Template ### AR reconciliation template Reconcile this ledger to your GL control account. Read more →](https://accounting.events/resources/accounts-receivable-reconciliation-template/)[Template ### AR aging report template Group unpaid invoices into aging buckets automatically. Read more →](https://accounting.events/resources/accounts-receivable-aging-report-template/)[All resources ### Resources, templates and calculators Every free AR guide, template and calculator we publish. Read more →](https://accounting.events/resources/) **About the author: Harry P. Smart, CPA and Lead Reviewer.** Harry P. Smart is a CPA based between London and New York City. Harry writes and leads the review of the software guides on Accounting.Events. **Fact-checked by Julie Turnbridge, Freelance writer and bookkeeper**, on October 26, 2026. Checked prices, review counts, awards, integrations and dates against each primary source. Last updated October 19, 2026. This guide is general information, not accounting, tax, or financial advice. ## Sources Sources checked by Julie Turnbridge on October 26, 2026: 1. [AccountingTools](https://www.accountingtools.com/articles/what-is-a-subsidiary-ledger.html) (accountingtools.com/articles/what-is-a-subsidiary-ledger....) 2. [IRS](https://www.irs.gov/businesses/small-businesses-self-employed/how-long-should-i-keep-records) (irs.gov/businesses/small-businesses-self-employed/how-lon...)