How to Fill Out and Use an Accounts Receivable Form Template

An accounts receivable form template is a working document, usually a spreadsheet, that lists every invoice your business is owed and tracks who owes what, when it’s due, and how overdue it has become. Done well, one row per invoice gives you a running picture of cash coming in, flags accounts that need attention, and produces the records you’ll need at tax time or during an audit. The sections below walk through the information to collect first, the columns and formulas to set up, and how to keep the template useful as payments come in, interest accrues, and some accounts go bad.

Information to Gather First

Before you type anything into the template, pull together the following for each customer you’ll be billing on credit.

  • Legal business name. Use the name on the customer’s articles of incorporation or registration, not a trade name or DBA. Your state’s Secretary of State business entity search will confirm it. The legal name is what you’ll need if you ever file a collection suit or a UCC financing statement.
  • Billing address and a direct contact. Record the street address tied to the accounts payable department, plus a phone number and email for someone who actually handles invoices. A mailing address alone slows follow-up.
  • Taxpayer Identification Number. A TIN or EIN confirms the customer’s tax identity and feeds your year-end information returns. Starting in 2026, you must file a Form 1099-NEC for any nonemployee you pay $2,000 or more during the year, up from the previous $600 threshold.1Internal Revenue Service. Taxpayer Identification Numbers (TIN)2Internal Revenue Service. Publication 1099 (2026), General Instructions for Certain Information Returns
  • Credit terms. Agree on timing before the first shipment. Net 30 gives the customer 30 days to pay; Net 60, 60 days. Record any early-payment discount, such as 2/10, Net 30 (2 percent off if paid within 10 days), and any late-payment interest rate.
  • Credit limit. Base it on payment history, financial statements, industry risk, and expected monthly sales. A common starting point is one to one-and-a-half times the customer’s expected monthly purchases, raised only after several months of on-time payments.

On a sale of goods, the credit period runs from the date of shipment, and post-dating an invoice pushes that clock back by the same amount.3Legal Information Institute. Uniform Commercial Code 2-310 – Open Time for Payment or Running of Credit; Authority to Ship Under Reservation Record the actual delivery or service date on every transaction so there’s no ambiguity about when the customer’s payment window opened.

Columns to Build Into the Template

A spreadsheet in Excel or Google Sheets works for most small and mid-sized businesses. Accounting software like QuickBooks or Xero generates AR reports automatically, but a spreadsheet gives you full control over layout and formulas. Either way, each row represents one invoice, and the column set is the same.

  • Invoice Number. A unique identifier for every transaction. Sequential numbering (INV-001, INV-002) is simplest and makes gaps obvious during audits.
  • Invoice Date. The date you issued the invoice.
  • Customer Name. The legal business name you verified during setup.
  • Description. A short note on what was sold or performed.
  • Invoice Amount. The total billed, including any applicable sales tax.
  • Payment Terms. Net 30, Net 60, or whatever you agreed on.
  • Due Date. Use a formula. If column B is the invoice date and column F is the number of days, =B2+F2 gives you the due date automatically.
  • Amount Paid. Updated as partial or full payments arrive.
  • Balance Due. Another formula: =E2-H2, invoice amount minus amount paid.
  • Aging Category. How many days past the due date the balance has been sitting.
  • Status. Open, partially paid, paid in full, or written off.

Aging Buckets

The aging column is where the template earns its keep. Standard buckets are current (not yet due), 1–30 days past due, 31–60, 61–90, and over 90. You can assign them with an IF formula comparing today’s date to the due date. Invoices in the over-90 bucket have roughly an 18 percent chance of ever being paid, which is why they call for aggressive follow-up or a write-off decision.

The most useful report your template produces is an aging summary: total dollars sitting in each bucket. It tells you at a glance whether cash flow is healthy or whether you’re quietly financing your customers’ operations.

Keeping the Template Current

Add a new row the moment you ship goods or complete a service. Batching invoices at month-end creates a stretch of time when outstanding balances are invisible, and that’s exactly how overdue accounts slip past you.

When payments come in by ACH, check, wire, or card, update the Amount Paid column and log the payment date and reference number in a notes column. Partial payments take care of themselves because the Balance Due formula does the math. Move fully paid invoices to a separate “Paid” tab or change their status to keep the active sheet clean, but don’t delete them. You need the history for reconciliation and tax records.

Reconcile the template against your bank statements every month. When recorded payments don’t match actual deposits, the gap usually traces to a data-entry typo, a payment applied to the wrong invoice, or a bank processing delay. Catching these early stops them from compounding into financial-statement errors.

Late-Payment Interest

Your credit agreement should spell out the interest rate on overdue balances. If it’s silent, the rate defaults to whatever your state’s statutory prejudgment interest rate is, usually somewhere between 2 and 10 percent annually. Every state sets its own ceiling, and charging above it exposes you to usury claims. Some states carve out exceptions for business-to-business transactions, but the limits still apply, and violating them can void the interest or create legal liability.

Record the agreed late fee or interest rate in two places: the master customer record and each individual invoice. When a dispute surfaces months later, that documentation separates an enforceable claim from an argument.

Estimating What You Won’t Collect

Not every receivable gets paid. The allowance for doubtful accounts is a contra-asset entry on your balance sheet that estimates how much of your outstanding AR you expect to lose. Skip it and your financial statements overstate what the business actually owns.

The simplest method uses historical collection data. If about 5 percent of your receivables have gone unpaid over the past several years, multiply your current AR by 0.05. A company carrying $200,000 in receivables at a 5 percent loss rate books a $10,000 allowance.

A more granular approach ties the estimate to the aging schedule. Current invoices might carry a 1 percent loss rate; invoices over 90 days might get 40 or 50 percent. Multiply each bucket’s balance by its rate and sum them. Businesses required to follow generally accepted accounting principles use one of these methods or a variation, and the FASB’s Accounting Standards Codification is the authoritative source for the applicable standards.4Financial Accounting Standards Board. Standards

Writing Off a Bad Debt

When a debt is genuinely worthless because the customer has gone bankrupt, disappeared, or refused to pay despite repeated attempts, federal tax law allows a deduction for any business debt that becomes wholly worthless during the tax year, and a partial deduction for debts recoverable only in part.5Office of the Law Revision Counsel. 26 USC 166 – Bad Debts

There’s a catch worth understanding before you count on the deduction. You can only deduct amounts previously included in your gross income. Accrual-method businesses record revenue when the invoice is issued, so an uncollected invoice is income you already reported and paid tax on, and the bad debt deduction reverses that. Cash-method businesses don’t report income until cash is received, so there’s nothing to reverse and no deduction is available for an unpaid invoice.6Internal Revenue Service. Topic No. 453, Bad Debt Deduction

To claim the deduction, you have to show you took reasonable steps to collect and that there’s no realistic expectation of repayment. Going to court isn’t required if you can demonstrate a judgment would be uncollectible anyway. Take the deduction in the year the debt becomes worthless; you can’t hold it and claim it later.6Internal Revenue Service. Topic No. 453, Bad Debt Deduction

How Long to Keep the Records

The IRS requires you to keep records supporting income on your return until the statute of limitations expires, generally three years from the date you filed. For a bad debt deduction, keep the supporting records for seven years.7Internal Revenue Service. How Long Should I Keep Records?

Tax retention is only part of the picture. State statutes of limitations on written contracts, the window during which you can sue to collect, typically range from four to ten years depending on where the debtor is located. Destroy the records in year three because the tax deadline passed and you’ve lost the evidence you’d need in court in year four. The safer rule: keep accounts receivable records for at least as long as your state’s statute of limitations on written contracts, or seven years, whichever is longer.

What an Auditor Will Check

If your business goes through an external audit, the auditor will almost certainly confirm a sample of receivables by contacting customers directly. The confirmation process verifies that balances reported as assets actually exist and are owed in the amounts stated.8Public Company Accounting Oversight Board. AU Section 330 – The Confirmation Process Mismatches between your template and what a customer reports (a different invoice amount, a payment you failed to record, a disputed charge) turn into audit findings that are expensive to resolve.

Keeping the template accurate throughout the year, rather than cleaning it up before year-end, is the difference between a smooth audit and a painful one. Consistent invoice numbering, prompt payment recording, and monthly bank reconciliation handle most of the work. The template is the first thing an auditor asks for, and it sets the tone for everything that follows.