Medical Billing Aging Report Excel Template: Buckets, Formulas, and Follow-Up

7 min read Medical Billing 2026 Guide

Spreadsheet aging report with color-coded receivable buckets, illustrating a medical billing aging report template

Direct answer: A medical billing aging report groups every unpaid claim balance by how long it has been outstanding, usually in buckets of 0 to 30 days, 31 to 60 days, 61 to 90 days, 91 to 120 days, and over 120 days, so a billing team can see at a glance where money is stuck and what to work first. Built in Excel, the template needs only a few columns, a formula that assigns each balance to a bucket from the date of service or the billing date, and a follow-up action tied to each bucket. The report's value is not the totals. It is the discipline behind them: nothing over 90 days without an owner, nothing over 120 days without a documented reason, and no bucket worked out of order while an older bucket grows.

THE COLUMNS THAT MAKE IT WORK

Keep the template narrow enough that someone will actually use it. The essential columns are patient or account identifier, payer, date of service, date billed, total balance, payer responsibility balance, patient responsibility balance, days outstanding, aging bucket, status, owner, and next action with a due date. Two design choices matter. First, separate payer balances from patient balances, because they are worked by different people through different routes, and a combined balance hides which side is stuck. Second, calculate days outstanding from a single consistent date, and state that date in the template header: date of service for a true aging view, or date billed if the team measures payer response time. Mixing the two in one report makes the buckets meaningless, so pick one, label it, and keep it for every row.

THE BUCKETS AND WHAT EACH ONE MEANS

The standard buckets are 0 to 30 days, 31 to 60 days, 61 to 90 days, 91 to 120 days, and over 120 days. Each bucket means something different, and the action should change with it. In 0 to 30 days, most balances are simply waiting for the payer's normal processing, so the work is confirmation: was the claim accepted, is it in process, is anything missing. In 31 to 60 days, the work becomes follow-up: status checks on claims that should have adjudicated by now, and corrections for anything returned. In 61 to 90 days, the work becomes investigation: denials worked through appeal or corrected claim, payer errors documented, and secondary billing completed. In 91 to 120 days, the work becomes escalation: supervisor review, payer calls with reference numbers, and a decision on every balance. Over 120 days, the work becomes resolution: appeal, write-off review under policy, or collection referral, each documented, because an unexplained balance over 120 days is a process failure, not a payer delay.

FORMULAS THAT BUILD THE REPORT

The template can be built with ordinary spreadsheet formulas, and the worked logic below uses Excel-style functions. Days outstanding is the report date minus the billing date, for example =ReportDate - BillingDate, with the report date held in a single cell so the whole report refreshes when it changes. The bucket is assigned with a nested IF on days outstanding, for example: 0 to 30 days when the value is 30 or less, 31 to 60 when it is 60 or less, 61 to 90 when it is 90 or less, 91 to 120 when it is 120 or less, and over 120 days for anything above that. Bucket totals use SUMIF on the bucket column, so each bucket's total updates as balances move. A pivot table over the same columns gives the payer view: total balance by payer by bucket, which is the view that shows whether one payer, rather than the whole practice, is the problem. Keep formulas visible and documented in the template, not hidden in pasted values, because a report nobody can audit will not be trusted when its totals are questioned.

WORKED EXAMPLE

Take a small practice with four balances, illustrative figures, not real practice data. A $400 balance at 12 days sits in 0 to 30 and needs only an acceptance check. A $650 balance at 45 days sits in 31 to 60 and needs a payer status call. A $900 balance at 75 days sits in 61 to 90 and needs a denial worked, not another status check. A $1,200 balance at 135 days sits in over 120 days and needs a documented resolution decision. The bucket totals are $400, $650, $900, and $1,200, and the practice total is $3,150. The report's message is in the shape, not the total: more than a third of the money is over 120 days old, so the oldest bucket is worked first, even though the newest balance is the easiest to clear. Working the easy new balances first while the old ones age is how a practice reports progress while its collectable revenue quietly expires.

THE FOLLOW-UP CADENCE BY BUCKET

Tie a cadence to each bucket and write it into the template so it survives staff changes. Balances in 0 to 30 days are checked once, at acceptance and again if no adjudication appears within the payer's normal window. Balances in 31 to 60 days get a status check and a documented payer reference number. Balances in 61 to 90 days get a denial or error worked to its next step, appeal or corrected claim, with the deadline recorded. Balances in 91 to 120 days get escalation and a supervisor review date. Balances over 120 days get a resolution decision recorded in the status column, with the reason stated in plain language. The cadence only works if the status column is kept honest: "in process" is not a status for a 100-day-old balance, and "waiting on payer" without a reference number and a date is not follow-up, it is a note that says nothing happened.

MISTAKES THAT RUIN AN AGING REPORT

The most common mistakes are all avoidable. Mixing payer and patient balances in one total, which hides which side needs work. Calculating some rows from date of service and others from date billed, which corrupts the buckets. Pasting values over formulas, so the report stops updating and nobody notices. Leaving the owner column blank, so every balance belongs to everyone and therefore to no one. And reviewing the report without acting on it: a weekly report that nobody works is worse than no report, because it creates the illusion that aging is managed while it grows. The template is only as good as the meeting that works it: oldest bucket first, every balance with an owner, every action with a due date.

AGING REPORT CHECKLIST

One consistent aging date, labelled in the header.
Payer and patient balances separated.
Bucket formula applied to every row, no pasted values over formulas.
Bucket totals by SUMIF, payer view by pivot table.
Every balance over 60 days with an owner and a next action.
Every balance over 120 days with a documented resolution reason.
Report worked oldest bucket first, on a fixed weekly cadence.

FREQUENTLY ASKED QUESTIONS

What is an aging report in medical billing? A report that groups unpaid balances by how long they have been outstanding, so a team can prioritize follow-up.

What buckets should it use? The standard set is 0 to 30, 31 to 60, 61 to 90, 91 to 120, and over 120 days.

Should payer and patient balances be combined? No. They are worked differently, so keep them in separate columns and separate totals.

How often should the report be worked? Weekly, oldest bucket first, with every action given an owner and a due date.

What does a growing over-120-day bucket mean? It usually means denials are not being worked to resolution, or follow-up has no owner. Treat it as a process signal, not just a payer delay.

Can this template replace practice management software? No. It is a work queue built from exported balances. The system of record remains the practice management system, and the template must reconcile to it.

A note on figures: Dollar amounts in the worked example are illustrative only, to show how the buckets and totals work. They are not benchmarks, targets, or results for any practice. This article is educational information about billing workflows, not financial advice.

Need help getting accounts receivable under control? Neximed Billing provides aging report reviews, denial management, and follow-up support. Contact us at info@neximedbilling.com or +1-406-2251570.