Accounts Receivable Tracking Template for Bookkeepers: Free Download 2026

Accounts receivable is where bookkeeping directly affects cash flow. The difference between a business that's profitable on paper but cash-poor, and a business that's actually thriving, is usually how well they track and collect what's owed to them.

This guide gives you an accounts receivable tracking system and template you can implement immediately — whether you're managing AR for clients or teaching clients to manage it themselves.

What Should an AR Tracking System Do?

A functional AR tracking system does five things:

1. Shows you exactly who owes money and how much at any moment
2. Tells you how old each outstanding balance is (aging)
3. Triggers collection actions at the right time automatically
4. Documents your collection history so you know what's been tried
5. Flags accounts that are likely uncollectible before they become write-offs

Most small businesses use QuickBooks or Xero for AR, but the reports aren't always enough. A standalone tracking template gives you visibility across clients and lets you do analysis that's hard to do inside accounting software.

The AR Aging Report Explained

The AR aging report breaks outstanding invoices into buckets by how old they are:

• Current (0-30 days): Normal. No action needed unless you have aggressive collection policies.
• 31-60 days: Starting to age. Send a polite reminder.
• 61-90 days: Overdue. First collection call or firm reminder email.
• 91-120 days: Seriously overdue. Stop work if applicable. Escalate to owner.
• 120+ days: Consider sending to collections or writing off.

The statistics are clear: invoices over 90 days old have a 26% chance of never being paid. Over 180 days: 57% chance. Your window to collect is the first 90 days.

AR Tracking Template Structure

Here's what your AR tracking spreadsheet should include:

Invoice Log Tab

• Invoice number
• Client name
• Invoice date
• Due date
• Invoice amount
• Amount paid
• Balance outstanding
• Days outstanding (auto-calculated)
• Aging bucket (auto-calculated: current, 31-60, 61-90, 91-120, 120+)
• Last contact date
• Last contact notes
• Status (open, partial, paid, in collections, written off)

Aging Summary Tab

• Total AR by aging bucket
• Percentage of AR in each bucket
• Collections trend (month over month)
• Average days to collect by client

Collections Activity Log Tab

• Date of contact
• Invoice number
• Client name
• Contact method (email, phone, mail)
• Notes on conversation or response
• Next action and date

AR Collection Process Templates

The most effective collection strategy is consistent, professional follow-up with clear escalation steps. Here's the sequence:

Day 1 (Invoice Date): Send Invoice

Send professionally with clear due date, payment instructions, and late fee policy if applicable. Invoices with explicit due dates (not "net 30" alone) get paid 8 days faster on average.

Day 25 (5 Days Before Due): Courtesy Reminder

Subject: Friendly reminder: Invoice #[number] due [date]

Keep it brief. Attach the invoice again. Make it easy to pay by including a direct payment link if you have one.

Day 35 (5 Days Overdue): First Follow-Up

Subject: Payment due: Invoice #[number] — [amount]

Still friendly but direct. Ask if they received the invoice. Offer to answer any questions that might be holding up payment.

Day 50 (20 Days Overdue): Second Follow-Up

Subject: Second notice: Invoice #[number] past due

More direct. Reference the overdue status explicitly. If you have a late fee policy, apply it and mention it.

Day 65 (35 Days Overdue): Phone Call

Call the accounts payable contact or business owner directly. Email is easy to ignore; phone calls less so. Document the conversation.

Day 90 (60 Days Overdue): Escalation

Send a formal demand letter. If a service business, consider suspending services. Consider whether to continue the engagement. Evaluate collections agency vs write-off.

Red Flags in AR That Indicate Business Health Problems

As a bookkeeper, your AR tracking gives you early warning signals that the business owner might not see:

• Increasing average days outstanding: Customers are taking longer to pay — often signals the business is overextended or customers are unhappy
• Concentration risk: More than 25% of AR in one customer — losing them would be catastrophic
• Same customers appearing repeatedly in the 90+ bucket: They're chronic late payers and probably a bad fit for the business
• AR growing faster than revenue: The business is booking revenue it may never collect

These are things worth mentioning in your monthly report narrative. Clients who feel their bookkeeper is watching out for them stay clients longer.

Automating AR Follow-Up

Many clients can benefit from automated AR reminder sequences. Here's how to set them up in common platforms:

• QuickBooks Online: Automatic payment reminders → Settings → Account and Settings → Sales → Reminders
• Xero: Invoice reminders → Business → Invoices → Invoice Reminders
• FreshBooks: Late payment reminders built into invoice settings
• HoneyBook / Dubsado: Workflow automations can send reminder sequences

Download the Template

The Operator Atlas Bookkeeping Ops Pack includes a complete AR tracking spreadsheet template with automatic aging calculations, a collection letter sequence (5 email templates), and a bad debt write-off policy template — all formatted for immediate use.

Get the Bookkeeping Ops Pack →