Accounts payable aging report in Excel

An accounts payable aging report shows which vendor bills are due, which are late and by how much. Paired with a payment plan, it also shows which early-payment discounts you can still take. In a spreadsheet it comes down to a few date calculations, one bucket formula and one IF. This guide works through all of them with real exercise numbers.

What an AP aging report is

Accounts payable is the money a business owes its suppliers for goods and services bought on credit. Each unpaid vendor invoice has an invoice date, an amount and payment terms that say when it must be paid.

Terms are often written like 2/10, net 30. Read it as: take a 2% discount if you pay within 10 days of the invoice date; otherwise the full amount is due within 30 days. The first number is the discount percentage, the second is the discount period, and "net 30" is the due date. Some vendors offer no discount at all, which is simply net 30 or net 45.

An AP aging report answers three questions:

• What is late? Overdue bills can damage a supplier relationship or bring late fees.
• What comes due soon? That tells you how much cash you need in the coming weeks.
• Which discounts are still available? Paying early at 2% off is a real saving, but only if you pay by the deadline.

How it differs from receivables aging

If you have built an accounts receivable aging report in Excel, the bucket formula will look familiar. The difference is the starting point. The receivables lesson counts days from the invoice date, because it asks how long a customer has owed you. This payables lesson counts days past the due date, because the question is whether you are late. An invoice that is not yet due is Current, however old it is. So the payables buckets are Current, 1-30, 31-60 and Over 60 days past due.

That one change, aging from the due date rather than the invoice date, is the source of the most common mistake in this lesson. Keep it in mind as you read the example.

Worked example: Harbor Bakery, November 2026

Harbor Bakery has ten unpaid vendor invoices. The report date in I1 is 11/30/2026. Each row holds the vendor, invoice date (B), amount (C), discount % (D), discount days (E) and net days (F). Your job is to fill in the due date in column G and the days past due in column H.

Step 1: the due date

A spreadsheet stores a date as a whole number of days, so adding a number of days to a date gives a later date. The due date is the invoice date plus the net days. In G4:

=B4+F4

Granite Equipment's invoice of 10/22/2026 on net 60 terms is due 12/21/2026. Fill the formula down to G13. If a result appears as a five-digit number, give the column a date format; the value is right.

Step 2: days past due

Days past due is the report date minus the due date. That number is negative for an invoice that isn't due yet, and a bill can't be "minus 21 days late". The fix is to take whichever is larger, the difference or zero. In H4:

=MAX(0,$I$1-G4)

The report date is locked with dollar signs so every row compares against I1 as you fill down; G4 stays relative. Granite Equipment is due on 12/21/2026, 21 days after the report date, so H4 shows 0. Metro Courier was due on 9/5/2026 and is 86 days past due. Two invoices, Granite and Hilltop Printing, are not yet due.

Step 3: aging buckets and totals

The second exercise, for Summit Bike Repair on the same report date, gives you the due dates and asks for a bucket label in column H. Test the bands in order, using "at most" at each boundary:

=IFS($I$1-G4<=0,"Current",$I$1-G4<=30,"1-30",$I$1-G4<=60,"31-60",TRUE,"Over 60")

IFS returns the result for the first test that is true, so each band only catches what the earlier bands missed. The final TRUE is the "everything else" case. Nested IF functions work the same way. Summit's list includes Hilltop Printing at exactly 30 days past due, Delta Office Supply at exactly 60, and Brightline Utilities due on the report date itself (0 days, so Current).

Then total each bucket with a conditional sum. With the bucket names in J4:J7, K4 is:

=SUMIF($H$4:$H$13,J4,$C$4:$C$13)

The four buckets add to $37,754.15, the total of all ten invoices. That is the first thing to check on any aging report.

Step 4: a payment plan that takes discounts

The third exercise turns the report into a plan. Bluebird Veterinary has a planned payment date for each invoice in column H. You work out the discount deadline in G and the amount to pay in I.

The discount deadline works exactly like the due date, using the discount days in column E. In G4: =B4+E4. The amount to pay asks one question: is the payment made on or before the deadline? In I4:

=IF(H4<=G4,C4*(1-D4),C4)

If yes, pay the amount less the discount, which is the amount times (1 − discount %). If not, pay the full amount. Invoices with no discount have 0% in column D, so the formula gives the full amount for them either way. The total in I14 is =SUM(I4:I13).

Three of Bluebird's payments earn a discount. Hilltop Printing ($6,413.00, 2%) is paid on 1/19/2026, a day before its deadline, so Bluebird pays $6,284.74. Ironwood Lumber ($5,036.00, 2%) is paid two days early, for $4,935.28. Kestrel IT Services ($911.00, 1%) is paid on 3/19/2026, exactly on its deadline, which still counts, so the payment is $901.89. Three other discounts are missed because the payments come after the deadline. The plan pays $40,468.91 against $40,707.00 of invoices, a saving of $238.09.

Common mistakes

The exercises check for each of these. The wrong numbers below show what each one does to the example.

Counting days from the invoice date

Subtracting the invoice date measures how old the bill is, not how late it is. Acme Paper Co. would show 68 days instead of 8, and Granite Equipment, which isn't due yet, would show 39. For Summit Bike Repair the effect on the buckets is dramatic: Current and 31-60 come out empty, 1-30 shows $13,179.37 and Over 60 shows $24,574.78. Subtract the due date in column G.

Negative days for bills not yet due

Without MAX, Harbor's Granite Equipment row shows −21 and Hilltop Printing shows −7. A negative number of days past due makes no sense on a report and breaks any later test. Wrap the subtraction in MAX(0, …).

Boundary invoices in the next bucket

Using "less than" instead of "at most" moves every invoice that sits exactly on 30 or 60 days. Summit's Hilltop Printing, at 30 days, would land in 31-60, and Delta Office Supply, at 60 days, in Over 60. The totals become $8,462.48 for 1-30, $5,725.92 for 31-60 and $10,386.38 for Over 60. They still add to $37,754.15, which is why boundary errors are easy to miss: check the boundary rows by hand.

Losing the discount on the deadline

"On or before" includes the deadline day. Writing H4<G4 treats Kestrel IT Services' payment on its deadline as late, so Bluebird pays $911.00 instead of $901.89 and the total rises to $40,478.02. Use <=.

Taking the discount on every invoice

Multiplying every amount by (1 − discount %) ignores the payment dates. Fairway Insurance, Metro Courier and Evergreen Janitorial are paid after their deadlines, so they owe the full amount. Taking their discounts anyway understates the plan by $166.05, to $40,302.86. A vendor would expect the difference.

How to check your report

• The bucket totals should add up to the total of all unpaid invoices.
• No days-past-due figure should be negative, and every not-yet-due invoice should be Current.
• Confirm any invoice at exactly 30 or 60 days past due by hand.
• On the payment plan, every discounted payment should be dated on or before its deadline, and the amount to pay should never exceed the invoice amount.

Related topics: credit purchases that create payables are covered in sales and purchase journal entries, and the locked references used throughout are explained in absolute cell references in Excel.

AP aging and MO-220 practice

This lesson covers the MO-220 objective "Perform routine accounting activities", under accounts receivable and payable: organizing purchase data, building aging reports and planning payments. MO-220 task instructions describe the purpose rather than naming the function. You might read "show 0 for invoices that are not yet due" rather than "use MAX". The exercises are written the same way, so you practice choosing the right tool.

Accounting Exercises is not affiliated with Microsoft or Certiport. These are original MO-220 practice exercises. You work in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks, and each answer is checked by running your formulas. The MO-220 practice page lists every topic.