Accounts receivable aging report in Excel

An accounts receivable aging report sorts unpaid customer invoices by how long they have been outstanding. It tells you who to chase first and how much of the balance may never be collected. In a spreadsheet you build it in three moves: count the days, assign a bucket, then total each bucket.

What an aging report is and why it matters

Accounts receivable is the money customers owe a business for goods or services already delivered on credit. Each unpaid invoice has a date. The older an invoice gets, the less likely it is to be paid in full.

An aging report groups invoices into age ranges called buckets. A common layout uses four: 0-30 days, 31-60 days, 61-90 days and over 90 days. The report answers three practical questions:

• Collections: which customers are late, and by how much?
• Credit decisions: should a customer with old invoices get more credit?
• Bad debts: how much of the total should the business expect not to collect?

The third question feeds the books. Under the allowance method, a business estimates uncollectible receivables and records that estimate as an allowance for doubtful accounts, with bad debt expense on the other side. The aging method sets a different uncollectible percentage for each bucket, because older balances are riskier. You will apply that idea at the end of the worked example.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes "Perform routine accounting activities", and under it a skill for accounts receivable and payable: organizing sales and purchase data, building aging reports, and working out bad debt expense. This lesson covers the aging and bad debt parts.

Remember that MO-220 task instructions describe the purpose rather than naming the function. You might be asked to "show the total amount in each aging bucket", and you have to recognize that a conditional sum is the tool. The exercises here are written the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: Pine Street Dental

Pine Street Dental wants to know how old its unpaid customer invoices are. The aging date in B2 is 12/31/2026. Invoices sit in rows 5 to 16, with the invoice number in column A, the customer in B, the invoice date in C and the amount in D. Here are the first rows and a few later ones:

Step 1: days outstanding

A spreadsheet stores each date as a whole number, counting days from a fixed starting point. That means you can subtract one date from another and get the number of days between them. In E5:

=$B$2-C5

Fill it down to E16. For INV-1406, 12/31/2026 minus 8/10/2026 is 143 days. The other invoices come out at 110, 90, 68, 60, 55, 32, 30, 16, 10, 8 and 3 days.

Two details make this formula work. First, the aging date comes first, so the answer is positive. Second, B2 is locked with dollar signs. Every row must compare against the same aging date, so that reference must not move when you fill down. The invoice date, C5, stays relative so it moves to C6, C7 and so on. If the result shows as a date instead of a number, change the cell's number format to a plain number.

Step 2: assign each invoice to a bucket

Now turn the number of days into a label. The rule is a chain of tests, smallest bucket first. In F5:

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

Read it left to right. If the invoice is 30 days old or less, it is 0-30. If not, and it is 60 or less, it is 31-60. If not, and 90 or less, it is 61-90. Anything left is Over 90. Because each test only runs when the one before it failed, you never need to write "between 31 and 60".

The boundaries matter. Pine Street has invoices at exactly 90, 60 and 30 days. They belong in 61-90, 31-60 and 0-30, which is why the tests use "less than or equal to". The full result for Pine Street is two invoices Over 90 (143 and 110 days), two in 61-90 (90 and 68), three in 31-60 (60, 55 and 32) and five in 0-30 (30, 16, 10, 8 and 3). A function that tests several conditions in order, such as IFS, works just as well as nested IFs.

Step 3: total each bucket and estimate the allowance

The lesson's third exercise uses Maple Lane Catering's ledger, with days and buckets already filled in. A small summary sits beside it: bucket labels in H5:H8, balances in I5:I8, example uncollectible percentages in J5:J8 and the allowance in K5:K8.

To total the invoice amounts in each bucket, use a conditional sum. In I5:

=SUMIFS($D$5:$D$16,$F$5:$F$16,H5)

It adds the amounts in column D only on rows where column F matches the label in H5. The ranges are locked, and the label reference stays relative, so you can fill down to I8. Then total the column with =SUM(I5:I8) in I9. The allowance for each bucket is the balance times its percentage, =I5*J5, and K9 adds them up.

Maple Lane should carry an allowance for doubtful accounts of about $9,101.32 against $75,681.30 of receivables. Notice where the risk sits: the two oldest buckets hold 56% of the balance but almost 92% of the allowance. That is the point of aging. The percentages here are example rates for practice; a real business sets its own from its collection history.

Common mistakes

The exercises check for each of these, and each gives you feedback that names the problem.

The aging date reference moves when you fill down

If E5 says =B2-C5 without dollar signs, E6 becomes =B3-C6. B3 is empty, which counts as zero, so E6 turns into a large negative number. Only the first row looks right. Lock the aging date as $B$2 before you fill down.

Negative days

Writing =C5-$B$2 subtracts in the wrong order. INV-1406 shows −143 instead of 143, and every bucket test then fails. Subtract the invoice date from the aging date.

Boundary ages land in the next bucket

Using "less than" instead of "less than or equal to" moves every invoice that sits exactly on a boundary. Pine Street's 30-day invoice would show as 31-60, the 60-day one as 61-90, and the 90-day one as Over 90. In the bucket exercise for Bluebird Veterinary, invoices at 30, 60 and 90 days are there on purpose to test this.

Leaving out the Over 90 bucket

A formula that stops at three buckets, with 61-90 as the final "else", puts every old invoice in 61-90. Pine Street's 143-day invoice would be reported as 61-90. The oldest, riskiest balances then disappear from the report.

Counting invoices instead of adding amounts

COUNTIFS uses the same arguments as SUMIFS without the sum range, so it is an easy swap. For Maple Lane it would show 2 in the 0-30 row instead of $13,501.95. A balance column should be in dollars.

Using one percentage for every bucket

If every allowance row multiplies by $J$5, every bucket gets the 1% rate. Maple Lane's allowance would come to $756.81 instead of $9,101.32. Each bucket has its own rate, and the older buckets use higher ones.

How to check your report

• The bucket balances should add up to the total of all invoices.
• Every invoice should have exactly one bucket, and no days figure should be negative.
• Look at the invoices on 30, 60 and 90 days and confirm their buckets by hand.
• The allowance percentages should rise from the newest bucket to the oldest.

Related topics: the allowance becomes an adjusting entry at period end, covered in adjusting entries practice. Credit sales that create receivables are covered in sales and purchase journal entries, and the receivable balance flows into financial ratios in Excel.