Accounts Receivable Aging Analysis Tool

The fundamental formula used in accounts receivable aging analysis is:

Aging Percentage = (Amount in Aging Category / Total Accounts Receivable) × 100

This formula is applied to each aging time bucket to calculate what percentage of your total receivables falls into each age category. These percentages provide critical insights into your collection efficiency and AR health. For example:

Current (0-30 days) Percentage = (Current AR / Total AR) × 100
31-60 Days Percentage = (31-60 Days AR / Total AR) × 100
And so on for each aging category

An accounts receivable aging schedule (also called an aging report) is a financial analysis tool that categorizes outstanding customer invoices based on the length of time they've been unpaid. This analytical report provides a structured view of AR broken down into standardized time intervals:

• Current (0-30 days)
• 31-60 days past due
• 61-90 days past due
• 91-120 days past due
• Over 120 days past due

The aging schedule can be prepared in different formats:

• Customer-Specific Aging: Shows the aging breakdown for each individual customer
• Invoice-Level Aging: Lists each outstanding invoice with its respective age
• Summary Aging Analysis: Provides aggregated totals and percentages for each aging category

Finance professionals use this analytical report to assess collection efficiency, identify problem accounts, and implement appropriate follow-up actions based on the age of receivables.

A good accounts receivable aging analysis result varies by industry, but generally follows these benchmarks:

• Current (0-30 days): 75-85% or more of total receivables
• 31-60 days: 10-15% of total receivables
• 61-90 days: 5-7% of total receivables
• 91-120 days: 2-3% of total receivables
• Over 120 days: Less than 2% of total receivables

Comprehensive aging analysis should show these key indicators of AR health:

• Less than 20% of receivables beyond 60 days old
• Declining percentage in older aging buckets over time
• Average age of receivables within your standard payment terms plus 15-20 days
• Consistency across customer segments and minimal concentrations of old receivables

Industries with longer standard payment terms (such as healthcare, construction, or government contracting) may have different benchmarks than retail or service businesses with shorter terms.

Effective management of accounts receivable aging involves a systematic approach:

1. Regular Aging Analysis:
   • Conduct weekly aging analysis reviews
   • Compare current analysis to previous periods to identify trends
   • Set up automated aging reports from your accounting system
2. Structured Collection Strategy:
   • Implement a tiered collection approach based on aging categories
   • 30-60 days: Email reminders and friendly phone calls
   • 61-90 days: More urgent communications and escalation to management
   • 90+ days: Formal collection letters, potential payment plans, or third-party collection consideration
3. Proactive Credit Management:
   • Review credit limits for customers with consistently poor aging profiles
   • Implement credit holds for accounts with significant overdue balances
   • Conduct regular credit reviews based on aging analysis insights
4. Process Optimization:
   • Address invoicing errors or disputes quickly to prevent aging issues
   • Offer multiple payment methods to facilitate faster collections
   • Consider early payment incentives for chronically late customers
5. Performance Monitoring:
   • Establish KPIs based on aging analysis metrics
   • Track collection effectiveness by aging category
   • Hold regular meetings to review aging trends and address issues

In professional accounts receivable aging analysis, calculation involves several methodical steps:

1. Determine the Reference Date: Typically the current date or the date of analysis
2. Calculate Invoice Age: Subtract invoice date (or due date, depending on company policy) from the reference date
3. Classify By Age: Categorize each invoice into the appropriate aging bucket based on calculated age
4. Sum Category Totals: Add up all invoice amounts within each aging category
5. Calculate Percentages: Divide each category total by the total accounts receivable balance and multiply by 100
6. Compute Weighted Metrics: Calculate weighted averages such as average days outstanding by multiplying each category's percentage by the midpoint of its day range

Advanced aging analysis may include additional calculations:

• DSO by Aging Category: Days Sales Outstanding calculated for each aging bucket
• Collection Effectiveness Index: Comparing what was collected versus what was available to collect
• Bad Debt Risk Exposure: Applying increasing risk percentages to older aging categories
• Customer-Specific Aging Metrics: Average days to pay by customer, frequency of late payment, etc.

Most enterprise accounting systems and ERP platforms automate these calculations, but they can also be performed manually using spreadsheet programs with the appropriate formulas.