AR Aging Analysis for Dental Practices: What It Reveals and How to Act on It (June 2026)

You pull your accounts receivable aging report example pdf once a month, see that total AR is holding steady, and figure collections are on track. But steady totals can hide a dangerous shift: new charges filling the 0-30 bucket while old balances quietly slide from 60 days to 90 days and beyond, where your chance of collecting drops to 15-25%.

An ar aging analysis splits receivables into time buckets (0-30, 31-60, 61-90, 90+) so you can see which dollars are fresh and which are going cold, whether the problem is insurance claims sitting unfiled or patient statements that never triggered a follow-up call. If you're building your own aging report in excel template free or using an accounts receivable aging report template word file, the aging of accounts receivable formula in excel is just TODAY() minus invoice date, then a nested IF to assign buckets.

But the formula is the easy part. The hard part is reading the distribution and knowing that a bloated 60+ insurance bucket means you need to refile claims with corrected codes, while a bloated 60+ patient bucket means your front desk needs a tighter payment plan workflow at checkout. How to generate aging report in excel with formula matters less than what you do once the free accounts receivable aging report example shows you where the leak is.

TLDR:

• AR aging sorts receivables into 0-30, 31-60, 61-90, and 90+ day buckets using (AR in Bucket / Total AR) x 100
• Healthy dental practices keep 70-75% of AR under 30 days and no more than 10% past 60 days
• Accounts over 90 days past due have only a 15-25% chance of collection
• Each aging bucket requires different follow-up: automated reminders under 30 days, phone calls at 31-60, demand letters at 61-90
• Lassie posts EOBs in real time and triggers patient statements at 30 days to keep receivables in collectible buckets

What AR Aging Analysis Is and Why Dental Practices Need It

AR aging analysis sorts unpaid receivables into time buckets (0-30, 31-60, 61-90, and 90+ days past invoice date). For a dental practice, it answers two questions: how much patients and payers owe, and how long that money has been uncollected.

A balance sheet shows the total. Aging shows which dollars are going cold and which carriers are dragging. Tracking AR Days alongside your aging distribution tells you whether collections are speeding up or slipping behind.

How AR Aging Reports Work in Dental Practice Management Systems

Every dental PMS builds aging reports from three data streams pulled at a single point in time: open patient ledger balances, outstanding insurance claims, and recorded payments. The system timestamps each charge, subtracts payments and adjustments, then drops the remainder into a bucket based on days since service or claim submission.

Dentrix, Eaglesoft, and Open Dental split the output into two views:

• Patient AR: balances owed directly by patients after insurance estimates settle
• Insurance AR: outstanding claims still pending with payers

The split matters because patient AR responds to statements and calls, while insurance AR needs refiles and appeals.

The AR Aging Formula and How to Calculate It Manually

The math behind aging analysis is straightforward arithmetic:

Aging Bucket % = (AR in Bucket / Total AR) x 100

Run it once per bucket. If total AR sits at $100,000 and $75,000 falls inside the 0-30 day window, that bucket represents 75% of receivables. Get paid 4x faster by keeping more balances in this window. Repeat for 31-60, 61-90, and 90+ to get a full distribution. Healthy practices keep 70-75% of AR in the 0-30 bucket and hold the 90+ bucket to 10-15% or less. Buckets always sum to 100, so a shift in one means a shift elsewhere.

Dental Practice AR Aging Benchmarks: What the Numbers Should Look Like

Healthy dental aging looks different from a generic B2B receivables curve because insurance processing alone eats 30 days before a claim resolves.

Targets practice consultants reference:

No more than 10% of receivables should sit past 60 days, per Pearly's dental benchmarking data. Anything older than 90 days gets treated as functionally uncollectible by most valuation analysts. Behind on AR? Automate it.

How to Read Your AR Aging Report: A Step-by-Step Walkthrough

Open the summary view first. It shows totals by bucket and percentage of AR in each. Scan the 90+ column: if it holds more than 15% of total AR, flag it.

Then drill into the invoice-level detail tab. Sort descending by balance within the 60+ bucket. The top accounts here typically account for the bulk of your aging risk, which is why working them first clears the most exposure.

Watch for concentration patterns (short-staffed teams need this view):

• One carrier dominating 31-60: a payer-side processing issue
• Patient balances clustered past 90: a statement workflow gap
• Single-account spikes over $5,000: same-day call worthy

Creating AR Aging Reports in Excel: Templates, Formulas, and Automation

If your PMS export feels thin, Excel fills the gap. Start with six columns: Customer Name, Invoice Date, Due Date, Amount, Days Outstanding, and Aging Bucket.

Calculate days outstanding in column E:

=TODAY()-D2

Then drop this nested IF into column F to assign buckets:

=IF(E2<=30,"0-30 Days",IF(E2<=60,"31-60 Days",IF(E2<=90,"61-90 Days","Over 90 Days")))

Drag both formulas down. Build a pivot table with Aging Bucket as Rows and Sum of Amount as Values, then add a slicer for payer or patient type to toggle between insurance AR and patient AR. Save as a template, paste fresh PMS data monthly, and the pivot refreshes in one click. Or learn more about automated posting that removes this manual step.

What Your AR Aging Report Reveals About Collections Performance

Your aging report works as a diagnostic chart. The shape of the distribution tells you where the revenue cycle breaks.

A bloated 60+ insurance bucket points to claims failures: missing attachments, incorrect codes, or unfiled denials. A bloated 60+ patient bucket points to missed statements, unclear checkout estimates, or weak follow-up cadence.

Sudden changes matter too. A jump in the 31-60 bucket often traces to one carrier slowing reimbursement or a posting gap.

Per Pearly's dental AR research, practices have only a 15% to 25% chance of collecting an account once it crosses 90 days past due.

How to Act on AR Aging Data: Collection Strategies by Aging Bucket

Each bucket needs its own playbook. Treating a 75-day patient balance like a 20-day one wastes effort on both ends.

• 0-30 days: automated email and SMS reminders, no human time required
• 31-60 days: personal phone call from the front desk, refile rejected claims with corrected codes
• 61-90 days: written demand letter, payment plan offer, escalated payer outreach with claim reference numbers
• 90+ days: final notice, collection agency referral for accounts over $500, write-off below that

Focus on dollar value, not chronology. A $4,000 account at 75 days beats twenty $80 balances at 100 days. Book a demo to see how we rank these automatically.

Common Mistakes Dental Practices Make With AR Aging Analysis

Four mistakes show up repeatedly when practices audit their aging workflow:

• Reviewing reports monthly instead of weekly, letting 31-60 balances slide into 61-90 unnoticed
• Applying the same follow-up cadence to every bucket
• Lumping insurance and patient AR into one figure, hiding whether the problem is payer-side or front-desk-side
• Watching total AR alone and ignoring distribution

The last one matters most. A practice carrying $200,000 with 80% inside 30 days is far healthier than one with 40% past 90 days. Same headline number, very different collectability.

How AR Aging Impacts Dental Practice Valuation and Financial Health

Buyers price the AR book line by line during acquisition. Receivables under 30 days get valued near face, while balances past 90 typically get discounted 50% or written off, pulling directly from the sale multiple.

Lenders apply the same logic. SBA underwriters pull aging reports during diligence, and a top-heavy 90+ column tightens borrowing capacity or raises rates.

Working capital drag compounds the issue. Every dollar trapped in the 60+ buckets funds operations out of pocket, forcing larger cash reserves or credit draws. Get started on clearing your backlog.

How AI and Automation Are Changing AR Aging for Dental Practices

Three approaches collapse the aging problem at its source instead of chasing it after the fact:

• Auto-posting of EOBs in real time, so insurance payments hit the ledger the day they clear instead of sitting in a queue that ages claims artificially. The system reads each EOB, applies payments and adjustments down to the cent against the expected fee schedule, and flags any line that does not match instead of guessing. That keeps insurance AR from inflating just because a paper check or remittance sat unposted for two weeks.
• Threshold-triggered patient billing, where statements fire the moment a balance crosses 30 days past due. Once insurance settles and the remaining patient portion is calculated, a statement goes out by SMS text link without front-desk staff queuing it manually, and any balance still unpaid at 30 days surfaces on a report that syncs with the PMS in real time. This catches patient AR while it is still in the 0-30 window where collection rates are highest.
• AI prioritization that ranks open balances by dollar value and collection probability, surfacing the ten accounts worth a call today. Instead of sorting a flat aging report by date, the queue weights a $4,000 balance at 75 days ahead of twenty $80 balances at 100 days, separates payer-side claims from patient-side balances, and points each to the right follow-up so staff spend their limited call time on the dollars most likely to come back.

The deeper change is cadence. Monthly review becomes a continuous feed, and the report evolves from a backward-looking diagnostic to a live work queue. Because posting, billing, and prioritization run as the data arrives instead of once a month, balances are worked while they are still young, the 0-30 bucket stays full, and fewer accounts ever reach the 90+ window where collection odds fall to 15-25%. The aging report stops being a monthly autopsy and starts driving the day's tasks.

How Lassie Automates the Work That Keeps AR From Aging

Most aging happens because EOBs sit unposted. Lassie closes that gap by retrieving, reading, and posting EOBs into Dentrix, Eaglesoft, or Open Dental in real time, with custom posting rules and bank reconciliation that flags variances to the cent. Never post an EOB again.

Automated patient billing fires statements the moment insurance settles, and the smart agent report surfaces unpaid balances at the 30-day mark. By catching receivables inside the 0-30 bucket, practices keep more balances in the window where collection rates are highest.

Final Thoughts on Using AR Aging Data in Your Dental Practice

Aging analysis works when you review it weekly and act on what you see. Flag balances past 60 days, sort by dollar value, and route patient AR and insurance AR through different workflows. The goal is simple: catch receivables early, before they age into the 90-day bucket where collection probability drops below 25%. Book a demo to see how Lassie keeps receivables in the collectible window.

FAQ

Can I build an AR aging report in Excel without a dental PMS?

Yes. Export your receivables data (customer name, invoice date, amount), calculate days outstanding with =TODAY()-[Invoice Date], then use nested IF formulas to assign aging buckets (0-30, 31-60, 61-90, 90+). Build a pivot table to sum balances by bucket and your aging report updates whenever you paste fresh data.

What's the difference between patient AR and insurance AR aging?

Patient AR tracks balances owed directly by patients after insurance pays, while insurance AR tracks outstanding claims still pending with payers. Patient AR responds to statements and phone calls; insurance AR requires claim refiles, payer follow-up, and appeals. Most PMS systems separate these in aging reports because each needs a different collection workflow.

How do you calculate the AR aging analysis formula for dental practices?

Divide the receivables in each aging bucket by total AR, then multiply by 100 to get the percentage. If you have $75,000 in the 0-30 day bucket and $100,000 total AR, that bucket is 75% of receivables. Repeat for each time period (31-60, 61-90, 90+) and the percentages will sum to 100%.

AR aging analysis Excel vs dental PMS reports?

PMS reports pull live data from patient ledgers and insurance claims with automatic bucket assignment, giving you real-time aging without manual formulas. Excel templates work when you need custom views or your PMS export is limited, but you'll paste data manually each time and recalculate formulas. For weekly monitoring, PMS reports save time; for custom analysis or practices without full-featured PMS reporting, Excel templates with pivot tables get the job done.

When should I escalate a dental AR balance to collections?

Flag accounts over $500 once they hit 90 days past due, since collection probability drops to 15-25% beyond that threshold. Send a final demand letter first, offer a payment plan for patients willing to engage, then refer to a collection agency if no response within 15 days. Write off balances under $500 at 90+ days unless the patient is actively making payments.