Aging report: AR vs AP, templates, build fast
Aging report dashboard with invoices grouped by days past due
Bookkeeping and tax management

Aging report: AR vs AP, Excel steps, and what to do

An aging report groups unpaid invoices or bills into time buckets (Current, 1–30, 31–60, 61–90, 90+ days) so you can see who owes you money (AR) or who you owe (AP) and what is overdue. Use it to prioritize collections or schedule payments, forecast cash flow, and flag credit or vendor risks—most accounting systems can generate it automatically, or you can build one in about 10 minutes in Excel or Google Sheets.

In practice, the aging report has two faces: the Accounts Receivable version lists customer invoices and helps you follow up on late payments, while the Accounts Payable version lists supplier bills so you can time cash outflows and capture early-payment discounts. Either way, the goal is clarity: what’s current, what’s slipping, and what action to take next.

Aging report dashboard with invoices grouped by days past due

Aging report, explained in 60 seconds

At its core, an aging report is a snapshot that groups open items by how many days they are past due. The standard buckets are Current, 1–30, 31–60, 61–90, and 90+. Many teams add a 121+ bucket for extra visibility. Past due means the due date has passed; current means it has not. These buckets help you focus attention where it matters: heavier balances in older buckets usually demand faster, higher-touch outreach or escalation.

There are two primary variants: AR aging (customers owe you) and AP aging (you owe vendors). AR aging is owned by finance or the collections team and powers cash-in forecasting; AP aging is owned by accounts payable and treasury to schedule payments and maintain vendor relationships. You can produce both from most accounting apps with a click, but if the data isn’t clean—or if you run a very small shop—you can build a serviceable version quickly in a spreadsheet.

Report type Who owes whom Primary purpose Typical users/owner Standard buckets Key decisions/actions Update cadence Best tool
AR (Accounts Receivable) Customers owe your business Prioritize collections and forecast cash-in Finance, collections, sales ops Current, 1–30, 31–60, 61–90, 90+ Who to call/email, when to escalate, credit hold Daily to weekly Accounting software; spreadsheets for small teams
AP (Accounts Payable) Your business owes vendors Schedule payments and manage cash-out AP clerks, controllers, treasury Current, 1–30, 31–60, 61–90, 90+ Which bills to pay now, capture discounts, avoid late fees Weekly to biweekly Accounting software; spreadsheets for light volume

Accounts Receivable (AR) aging report: what it shows and how to use it

An AR aging report lists each customer’s open invoices with invoice date, due date, balance due, and days past due, then totals by bucket. A typical row reads like this: Customer: Blue Sky Co.; Invoice #10452; Invoice Date: Apr 12; Due Date: May 12; Balance: $2,400; Bucket: 31–60. The summary view aggregates balances by bucket per customer, and the detail view shows each invoice line.

How to act on it: start with 61–90 and 90+ days where balances are material. Set a follow-up cadence by bucket: email reminders shortly after due date, phone calls at 15–30 days, manager outreach or stop-ship reviews at 45–60 days, and escalation or payment plans at 90+. Document disputes, partial payments, and promises-to-pay in your CRM or accounting notes so your team has context on the next call. If a customer repeatedly lands in older buckets, consider lowering credit limits, requiring deposits, or moving to advance payment for future orders.

Forecasting and credit decisions: trends by bucket help you estimate near-term cash receipts. A decreasing 61–90 bucket suggests momentum in collections; a swelling 90+ bucket may signal deteriorating credit behavior. Before extending terms to a new account, scan similar customers’ aging patterns. Use history and sales input, not just a single snapshot. For teams new to financial statements, this primer is a helpful companion to reading aging results alongside the balance sheet and income statement: Unfamiliar with Financial Statements? This Guide is for You!

Accounts Payable (AP) aging report: why it matters

AP aging arranges unpaid bills by days to or past due so you can schedule payments deliberately. The aim isn’t to pay late; it’s to plan payments to match cash inflows and to capture supplier incentives without incurring fees. Many vendors offer discounts for paying early (for example, 2/10 net 30). If your AP aging highlights bills currently in the 1–10 day window, you can decide whether the discount is worth it given your cash position and cost of funds. For a refresher on how those discounts actually work and how to think about the implied rate, see 2/10 n/30: Meaning, APR, and how to calculate.

Vendor relationships benefit from a well-run AP aging. Consistent on-time payments can help you negotiate better terms or priority allocation during supply constraints. When disputes occur, flag problem bills to prevent accidental payment and log the status—waiting for corrected invoice, merchandise return, or credit memo—so they don’t distort the report. Finally, AP aging is a planning tool: use it to create a weekly pay run calendar that aligns with expected deposits and approval cycles.

Build an aging report in Excel/Google Sheets in 10 minutes

You can create a simple AR or AP aging report using a flat list and a few formulas. Here’s a quick build that works in both Excel and Google Sheets.

  1. Gather the data. Export open items from your accounting system. At minimum include: Customer/Vendor, Invoice/Bill Number, Invoice/Bill Date, Due Date, Original Amount, Amount Paid, Open Balance (or compute), Status, and any flags like Dispute or Credit Memo.
  2. Normalize columns. Ensure dates are true dates, text is consistent (same name for the same customer), and open balances equal Original Amount minus Amount Paid. If you must compute: =IFERROR([@OriginalAmount]-[@AmountPaid],0).
  3. Calculate days past due. Add a column DaysPastDue. Use: =MAX(TODAY()-[@DueDate],0). This returns 0 for not-yet-due items so they fall in Current rather than a negative bucket.
  4. Assign buckets. Add a column Bucket with one of these formulas:
    Excel IFS: =IFS([@DaysPastDue]<=0,"Current",[@DaysPastDue]<=30,"1–30",[@DaysPastDue]<=60,"31–60",[@DaysPastDue]<=90,"61–90",TRUE,"90+")
    Excel nested IF: =IF([@DaysPastDue]<=0,"Current",IF([@DaysPastDue]<=30,"1–30",IF([@DaysPastDue]<=60,"31–60",IF([@DaysPastDue]<=90,"61–90","90+"))))
    Google Sheets IFS: =IFS([DaysPastDue]<=0,"Current",[DaysPastDue]<=30,"1–30",[DaysPastDue]<=60,"31–60",[DaysPastDue]<=90,"61–90",TRUE,"90+")
  5. Summarize by customer/vendor and bucket. Insert a PivotTable (Excel) or PivotTable (Sheets). Rows: Customer/Vendor. Columns: Bucket (ordered Current, 1–30, 31–60, 61–90, 90+). Values: Sum of Open Balance. Add a Grand Total. This gives you a classic aging summary.
  6. Detail tab for action. Create a filtered view that lists only past-due items (DaysPastDue > 0) with columns you’ll need for outreach: contact email/phone, last contact date, notes.
  7. Reconcile totals. The sum of Open Balance in your spreadsheet should tie to your accounting subledger control totals. If not, check for filters, hidden rows, or sign errors (credits negative vs positive). See data hygiene tips below.

Handling credits, deposits, and partials: Credits and prepayments often cause false negatives. Record credit memos as separate lines with negative balances and link them to the correct customer/vendor. If credits show as unapplied, your bucket may misstate exposure. Apply credits to the intended invoice so the open balance shrinks in the right bucket. For deposits, keep a separate column and subtract deposits from the invoice balance if your policy is to net them. For partial payments, ensure the remaining open balance is what drives the bucket, not the original amount.

Optional refinements: Add a DueInDays column =[@DueDate]-TODAY() to sort near-term items; add a flag for “Stop-ship candidate” when Bucket is 61–90 or 90+ and the customer exceeds a threshold. If you segment by region or sales rep, include those fields and add slicers to your pivot for quick filtering.

If you’re currently choosing software, this comparison guide can help you match features like aging reports, reminders, and integrations to your workflow: Find the Right Bookkeeping Software for Your Business. And if you’re deciding between cash and accrual, understand how due dates and recognition affect aging and financial statements: Cash basis vs accrual accounting: a practical guide for service businesses.

Read your aging report: thresholds, KPIs, and decisions

Turn the snapshot into action with clear thresholds. Treat these as guidelines you can tune by industry and risk tolerance:

  • AR follow-up cadence: reminder email at 3–5 days past due; personal email at 10; call at 15; manager escalation at 30–45; suspend new credit at 45–60; formal demand letters or payment plans at 90+.
  • AP scheduling cadence: plan pay runs weekly; aim to pay Current items on schedule; evaluate early-pay discounts within the first 10 days when practical; set approval cutoffs 48–72 hours before pay run.
  • Stop-ship guideline: if 61–90 exceeds a set dollar limit or if any 90+ exists, hold new shipments until a payment plan is agreed.
  • Credit review trigger: two consecutive months with 90+ balances, or percent of total AR in 90+ above your policy threshold.

Track KPIs alongside the aging report. Common metrics include:

  • DSO (Days Sales Outstanding): average days to collect. Pair DSO trends with the share of AR in 61–90 and 90+ to get a fuller picture of collection effectiveness.
  • CEI (Collection Effectiveness Index): measures how well you convert beginning receivables plus current period sales into cash within the period.
  • Bucket ratios: percent of receivables in 61–90 and 90+—declines indicate healthier collections.

Using aging to estimate allowances: Many companies use a weighted approach at period-end, assigning higher expected loss rates to older buckets. For example, Current at a low rate, 31–60 at a higher rate, and 90+ at the highest rate. Calibrate weights based on your history and policy, and align with your accountant. Aging gives the structure; your policy sets the percentages.

Dealing with chronically late accounts: reset expectations, tighten terms (e.g., move from net 30 to net 15), require partial upfront payment, or stop extending credit until the account stabilizes. Document each change so sales and finance stay in sync.

Common mistakes (and how to fix them)

Small data issues can undermine the report and waste your team’s time. Here are frequent traps and fixes:

  • Mixing invoice date and due date: Aging must be based on due date to reflect terms. Make it policy to compute days relative to due date. If your system exports only invoice date, recreate due date using terms logic.
  • Unapplied cash or credits: Receipts or credit memos not applied to the intended invoice leave both the credit and the invoice open. Clean the matching weekly. In spreadsheets, add a column for unapplied so you can filter and resolve.
  • Partial payment handling: Ensure open balance declines with each payment. In exports, confirm Open Balance equals Original minus Paid; if not, compute it and use that field for buckets.
  • Including closed or disputed items: Filter out fully paid invoices and create a Dispute status that removes items from normal follow-ups while you work resolution. Keep a separate dispute log with owners and next steps.
  • Wrong or inconsistent due dates: Terms should be centrally maintained. If different terms exist for specific customers, encode them accurately. When in doubt, standardize and communicate changes.
  • Multi-currency chaos: Convert to a display currency before aggregating so your buckets and totals are comparable. Keep the original currency and rate in detail for audit trails.
  • Tax and rounding issues: Confirm that tax-inclusive invoices are handled consistently, and round open balances only in the final display to avoid tie-out differences.

Automation, cadence, time and cost

How often to run it: Many AR teams run aging daily for internal review and weekly for account owners; AP teams often produce it weekly or biweekly to plan pay runs. The owner is typically finance, but sales reps should receive customer-level AR snapshots so they can assist with outreach.

Manual vs automated: A manual spreadsheet build takes about 10 minutes once data is exported, plus time for reconciliation and outreach planning. Automation in accounting software reduces errors, standardizes buckets, and integrates reminders. Entry-level systems often include aging by default; more advanced tools add email reminder workflows, call task queues, and notes. Confirm capabilities and pricing on each vendor’s site. Integrations that help: automated dunning emails, VoIP auto-dial lists tied to the 61–90 and 90+ buckets, and CRM tasks that reflect the latest bucket changes.

When an aging report isn’t the right tool: If you operate cash-only retail, require prepayment, or run auto-collected subscriptions with near-perfect success rates, an aging view may add little. Instead, monitor settlement failures, refund rates, and involuntary churn. For projects with long milestones, use a milestone billing schedule and project dashboards rather than a classic aging report.

Free aging report template and a quick checklist

The starter template most teams need has two tabs: Summary and Detail. The Summary is a pivot by customer/vendor and bucket with totals and percentages; the Detail lists each open item with days past due, bucket, contact info, and notes. You can customize buckets (for example, add a 121+), add departments or regions to filter visibility, and include a column for dispute status to exclude those items from standard follow-ups. To extend the template, add slicers for sales rep or region so managers can run reviews quickly.

Customize buckets: If your industry has longer cycles (construction, enterprise software), consider 0–15, 16–30, 31–60, 61–120, 121+. If cycles are shorter (SMB SaaS, consumer goods), a 0–7, 8–14, 15–30 structure might better match your cadence. Update the IFS/IF formula to match your structure and update the pivot column sort order.

Collections-day checklist

  • Refresh data from your accounting system and confirm export time stamps.
  • Reconcile totals: pivot totals tie to the AR/AP control balance.
  • Filter out disputes and closed items; add them to a separate log.
  • Sort by past-due bucket and highest balance; focus on 61–90 and 90+.
  • Assign calls and emails; queue reminders in your software.
  • Send friendly reminders for newly past-due accounts; document tone and content.
  • Log outcomes—promise dates, partials, disputes—so the next touch is informed.
  • Escalate per policy thresholds; consider stop-ship or tighter terms when applicable.

For broader bookkeeping routines that support a reliable aging process, see our practical finance guides: Get Organized: Essential Bookkeeping Practices for Small Businesses and Make Bookkeeping Easier for Freelancers.

FAQ highlights about aging reports

What is the difference between AR and AP aging reports? AR shows who owes you and helps you collect; AP shows who you owe and helps you schedule payments. Both use the same buckets, but decisions differ: AR is about outreach and credit control; AP is about approvals and cash planning.

What are the standard aging buckets, and can I customize them? Standard buckets are Current, 1–30, 31–60, 61–90, 90+. Yes, you can customize. The key is to publish the standard and apply it consistently so trends are meaningful.

How do I create an AR aging report in Excel or Google Sheets? Use the steps above: compute DaysPastDue, assign buckets via IFS/IF, then a pivot by customer and bucket. Add a filtered detail view for action.

How often should I run an aging report? Run AR daily or weekly depending on volume; AP weekly or biweekly. Increase cadence during busy seasons or if risk is rising.

How do I handle partial payments, deposits, or credit memos? Track the remaining open balance, apply credits to specific invoices, net deposits against the invoice if your policy supports it, and ensure buckets reflect the open balance, not the original amount.

How is an aging report used to estimate allowances? Many teams assign higher loss rates to older buckets and compute a period-end estimate. Calibrate with history and your accountant’s guidance.

One last tip: pair the aging report with clear SLAs and a weekly review cadence. Numbers without owners fade; numbers with owners move.