Free Excel template · Excel for medical office

Free medical billing aging report template

For billing coordinators and office managers who follow up on unpaid claims and patient balances. Paste in each claim with its date of service, billed and paid amounts, set the as-of date, and the template works out the balance and days outstanding, sorts every open claim into an aging bucket, and totals the buckets and payers so you know where to start calling.

cellwise-medical-billing-aging-report.xlsxExcel workbook · 5.0 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I
1	Claims aging report
2	As of	Oct 31, 2026
3
4	Claim #	Patient ID	Payer	Date of service	Billed	Paid	Balance	Days outstanding	Aging bucket
5	CL-50211	P-1042	Commercial plan A	Oct 20, 2026	240	0	240	11	0-30 days
6	CL-50198	P-1213	Medicare	Sep 16, 2026	410	0	410	45	31-60 days
7	CL-50176	P-1150	Self-pay	Sep 28, 2026	95	0	95	33	31-60 days
8	CL-50140	P-1222	Commercial plan B	Aug 20, 2026	520	340	180	72	61-90 days
9	CL-50102	P-0954	Self-pay	Jul 28, 2026	120	0	120	95	91-120 days
10	CL-50233	P-1189	Commercial plan A	Oct 10, 2026	365	0	365	21	0-30 days
11	CL-50089	P-0871	Commercial plan B	Jun 15, 2026	610	0	610	138	Over 120 days
12	CL-50150	P-0988	Medicare	Aug 28, 2026	285	285	0		Paid
13	CL-50215	P-1301	Medicaid	Oct 2, 2026	150	0	150	29	0-30 days
14	CL-50120	P-1107	Commercial plan A	Jul 14, 2026	430	215	215	109	91-120 days
15
16	Bucket	Claims	Balance	% of A/R
17	0-30 days	3	755	0.31656184486
18	31-60 days	2	505	0.21174004193
19	61-90 days	1	180	0.075471698113
20	91-120 days	2	335	0.14046121593

How the formulas work

Days outstanding from a fixed as-of date

H5=IF(G5<=0,"",$B$2-D5)

Dates are day numbers in Excel, so the as-of date minus the date of service is the number of days. Using a typed as-of date in B2 instead of TODAY() means the report shows the same numbers when someone opens it next week, which matters when you send it to a manager. Paid claims are left blank so they do not count as old.

Sort each claim into an aging bucket

=IF(H5="","Paid",IF(H5<=30,"0-30 days",IF(H5<=60,"31-60 days",IF(H5<=90,"61-90 days",IF(H5<=120,"91-120 days","Over 120 days")))))

The nested IF checks the smallest bucket first and stops at the first match, so 45 days becomes 31-60 days. The labels include the word days on purpose: Excel can turn a typed label like 1-30 into a date (January 30), which would break the bucket totals.

Total each bucket

C17=SUMIF($I$5:$I$14,A17,$G$5:$G$14)

SUMIF adds the balance for every claim whose bucket label matches the label in A17. The ranges are locked with dollar signs so the formula copies down the bucket list. Column B counts the claims the same way with COUNTIF, and column D shows each bucket as a share of the total.

Dollar-weighted average days outstanding

C31=ROUND(SUMPRODUCT(G5:G14,H5:H14)/SUM(G5:G14),1)

SUMPRODUCT multiplies each balance by its age and adds the results, so a $610 claim at 138 days counts far more than a $95 one at 33 days. SUMPRODUCT treats the blank text in paid rows as zero. Watch this number month to month: if it creeps up, follow-up is falling behind.

Make it yours

Export open claims from your practice management system as CSV and paste the values into rows 5 onward. If dates paste as text (left-aligned), select the column and run Data > Text to Columns > Finish to turn them into real dates.
Need more rows? Insert them inside the claims list (between row 5 and row 14) and copy the formulas in G, H and I down; the SUMIF ranges grow automatically.
Sort the claims by Days outstanding, largest first, to get a call list. Or filter column I to 91-120 days and Over 120 days.
Change B2 to the last day of each month and save a copy, so you can compare aging month to month.

FAQ

How do I calculate days outstanding in Excel?

Subtract the date of service (or claim submission date, if that is how your office measures it) from an as-of date: =$B$2-D5. Format the result as a number, not a date.

Why not use TODAY() for the aging date?

TODAY() recalculates every time the file opens, so last month's report would quietly show different numbers. A typed as-of date keeps each report fixed.

Should patient names go in the aging report?

Use the patient or account ID your system already uses, and follow your practice's privacy policy on where files with patient information can be stored and who can open them.

Functions used

Related guides

More free templates

Updated November 2, 2026