How to Create an AR Aging Report

An accounts receivable (AR) aging report is one of the most powerful tools you have for managing your business’s cash flow. It shows you exactly who owes you money and, more importantly, how long they've owed it. This tutorial will break down what an AR aging report is, why it's so critical for financial health, and provide step-by-step instructions on how to create one using popular accounting software or a simple spreadsheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is an Accounts Receivable (AR) Aging Report?

In simple terms, an AR aging report is a list of all your unpaid customer invoices. But it does more than just list them - it categorizes each invoice based on how long it has been outstanding. Think of it as a financial to-do list that’s automatically sorted by priority.

The report is typically structured with columns, often called "aging buckets," that group invoices by time periods. While these can be customized, the standard buckets are:

• Current: Invoices that have been issued but are not yet due.
• 1-30 Days Past Due: Invoices that are 1 to 30 days overdue.
• 31-60 Days Past Due: Invoices that are 31 to 60 days overdue.
• 61-90 Days Past Due: Invoices that are 61 to 90 days overdue.
• 90+ Days Past Due: Invoices that are more than 90 days overdue.

Each row in the report usually corresponds to a customer, showing the total amount they owe spread across these aging buckets. This format gives you an immediate, at-a-glance understanding of where your cash is tied up and which accounts need urgent attention.

Why AR Aging Reports are So Important

Running daily operations with poor visibility into your accounts receivable is like driving at night with the headlights off. An AR aging report provides the clarity needed to make sound financial decisions. Here’s why it's a non-negotiable tool for any business.

It Helps You Manage Cash Flow

Cash is the lifeblood of your company. An AR aging report shows you not only how much cash you're expecting but also when you can expect it. If you see a large balance in the "Current" column, your short-term cash flow looks healthy. If that balance moves into the "31-60" and "61-90" day buckets, it’s a warning sign that a cash crunch might be on the horizon, giving you time to act.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

It Pinpoints At-Risk Customers

Are the same names always showing up in the past-due columns? This report makes it incredibly easy to spot risky customers or those who are consistently slow payers. Identifying these patterns helps you make informed decisions about extending credit. You might decide to shorten their payment terms, require upfront payments, or pause services until their account is current.

It Helps You Prioritize Collections

Without an aging report, your collection efforts are likely scattered and inefficient. This report allows you to triage effectively. The invoices in the "90+ Days Past Due" bucket are the highest risk, the longer an invoice goes unpaid, the less likely it is you'll ever collect it. The AR aging report lets your team immediately focus on recovering the oldest and largest debts, maximizing their impact and improving collection rates.

It Validates Your Credit Policy

If a large percentage of your total receivables consistently falls into the past-due categories, your credit policy might be too generous. Maybe your "net 30" terms should be "net 15," or perhaps your process for vetting new clients needs to be stricter. The AR aging report provides the data-driven evidence you need to evaluate and adjust your policies before they cause serious financial strain.

How to Create an AR Aging Report: Two Main Methods

You don’t need to be a finance whiz to create an AR aging report. Depending on the tools you use, it can be as simple as a few clicks or require a little spreadsheet work. Let's cover the two most common methods.

Method 1: Using Your Accounting Software (The Easy Way)

Nearly all modern accounting platforms (like QuickBooks, Xero, FreshBooks, and others) have a built-in AR aging report. This is the fastest and most reliable way to get the information you need, as the data is pulled directly from your integrated invoicing and payment records.

The steps are generally similar across all platforms:

1. Log in and navigate to the "Reports" section of your accounting software. This is usually found in the main dashboard or side navigation menu.
2. Find the AR Aging Report. Look for a report named "Accounts Receivable Aging," "A/R Aging Summary," or something similar. You might find it under a "Customers" or "Sales" category of reports.
3. Choose the Report Date. By default, it usually runs for "Today," giving you a real-time snapshot. You can also run it for a past date, like the end of the previous month or quarter, to see historical data.
4. Customize if needed. Some software allows you to adjust the aging buckets (e.g., change from 30-day intervals to 15-day intervals) or filter by specific customers.
5. Run and review the report. You'll see a clean, organized table showing each customer with an open balance and how their total debt is distributed across the aging periods.

For most businesses, the built-in report is more than sufficient for day-to-day cash flow management and collections.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Building an AR Aging Report in a Spreadsheet (Excel or Google Sheets)

If you don't use accounting software or want to create a highly customized report, a spreadsheet is your best friend. This process is more manual but gives you complete control over the format and any additional analysis.

Step 1: Get Your Raw Data

First, you need a list of all your open (unpaid) invoices. You can usually export this from whatever system you use for invoicing. Your data export must include these key columns:

• Customer Name
• Invoice Number
• Invoice Date
• Due Date
• Invoice Amount (the outstanding balance)

Step 2: Set Up Your Spreadsheet

Open a new spreadsheet in Excel or Google Sheets. Create columns for the data you just exported. Then, add the following new columns to the right:

• Days Past Due
• Current
• 1-30 Days
• 31-60 Days
• 61-90 Days
• 90+ Days

Your spreadsheet should now have headers that look like a proper aging report table.

Step 3: Calculate the "Days Past Due"

This column calculates how many days have passed since the invoice due date. Let's assume your invoice due dates are in column D, starting from D2.

In the "Days Past Due" column (let's say it's F2), enter this formula:

=IF(D2 > TODAY(), 0, TODAY() - D2)

Note: Replace D2 with the cell containing your due date.

This formula checks if the due date is in the future. If it is, it returns 0. Otherwise, it calculates the difference between today's date and the due date. Drag this formula down for all your invoices.

Step 4: Categorize Invoices into Aging Buckets

Now comes the fun part: using formulas to place each invoice amount into the correct aging bucket. We'll use a combination of IF and AND functions. We'll reference our newly-created "Days Past Due" column (F) and the "Invoice Amount" column (let's say it's E).

• For the "Current" column (G2):

=IF(F2=0, E2, 0)

• For the "1-30 Days" column (H2):

=IF(AND(F2>0, F2<=30), E2, 0)

• For the "31-60 Days" column (I2):

=IF(AND(F2>30, F2<=60), E2, 0)

• For the "61-90 Days" column (J2):

=IF(AND(F2>60, F2<=90), E2, 0)

• For the "90+ Days" column (K2):

=IF(F2>90, E2, 0)

Update the cell references like F2 and E2 to match your sheet. Once done, drag these formulas down for all your invoices. Each invoice amount will now appear in only one of the aging columns.

Step 5: Summarize the Data with Totals or a Pivot Table

To finish your report, calculate the total for each aging bucket by adding a SUM formula at the bottom of each column. This gives you a summary of how much money is in each aging category.

For a more advanced and cleanly organized view, use a Pivot Table. Select all your data, go to Insert > Pivot Table. Then:

• Drag "Customer Name" to the Rows area.
• Drag each of your aging bucket headers ("Current," "1-30 Days," etc.) to the Values area.

This will automatically create a perfect summary report, neatly grouping all invoices by customer.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Final Thoughts

The AR aging report is a foundational document for maintaining your company's financial stability. Whether you pull it instantly from your accounting software or build it yourself in a spreadsheet, regularly reviewing it empowers you to manage cash flow intelligently, improve collections, and make smarter credit decisions.

Spending hours manually updating a spreadsheet or constantly logging into different platforms to understand your finances can be a huge time-sink. We built Graphed to solve this exact problem. By connecting directly to your financial tools like QuickBooks and Stripe, you can instantly create live, interactive reports with simple natural language. Instead of wrangling formulas, you can just ask, "Show me an AR aging summary grouped by customer," and get back a real-time dashboard that updates automatically, helping you spend less time gathering data and more time acting on it.