Cellwise Start free

Excel guide · 6 min read

How to make an accounts payable aging report in Excel

Quick answerFor each supplier bill, work out the open balance (amount minus payments applied, with SUMIFS) and days overdue (an as-of date in a cell minus the due date, never below 0). Look the days up in a small bucket table with VLOOKUP to get Not yet due, 1-30, 31-60 or Over 60. Then use SUMIFS on the due dates to see what's overdue and what falls due in the next 7 or 14 days for your payment run.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1BillSupplierBill dateDue dateAmountOpenBucketPaid forPaidAs of
2BL-3101Harbor PackagingAug 8, 2026Sep 7, 20261450450Over 60BL-31011000Nov 15, 2026
3BL-3108Metro PowerSep 10, 2026Oct 10, 2026612.8612.831-60 daysBL-31121240
4BL-3112Greenleaf ProduceOct 1, 2026Oct 31, 2026224010001-30 daysBL-3115980
5BL-3115Harbor PackagingOct 9, 2026Nov 8, 20269800PaidBL-3119375
6BL-3119Atlas CleaningOct 20, 2026Nov 19, 20263750Paid
7BL-3122Greenleaf ProduceOct 22, 2026Nov 21, 20261865.51865.5Not yet due
8BL-3126Harbor PackagingNov 7, 2026Dec 7, 202612001200Not yet due
9BL-3130Metro PowerNov 10, 2026Dec 10, 2026590.4590.4Not yet due

Download this sheet as an Excel practice file (.xlsx)

What you need

AP aging is the supplier side of the aging report: who you owe, how much, and how late. Accounts receivable aging asks "who owes us?" and chases; payables aging plans which bills to pay this week without paying anything twice or too early.

You need two lists from your accounting system: open bills with bill date, due date and amount (A:E), and payments applied to them (H:I). Columns F and G are helper columns with live formulas, explained below. Put the as-of date in K2 rather than using TODAY(), so the report you run on Friday still says the same thing on Monday. Here it's 15 November, the day of a mid-month payment run.

Step 1: Open balance per bill

Subtract every payment applied to the bill. SUMIFS handles part-payments and returns 0 for bills with no payment yet. This is column F, copied down.

=E2-SUMIFS(I2:I5,H2:H5,A2)

Result: 450

450: of BL-3101's 1,450.00, you've paid Harbor Packaging 1,000.00.

Step 2: Days overdue and days until due

Payables needs both directions. Days overdue tells you how late you are; days until due tells you what's coming. Overdue is the as-of date minus the due date, with MAX(0, ...) so bills that aren't due yet show 0.

=MAX(0,K2-D2)

Result: 69

69: BL-3101 was due 7 September, 69 days before 15 November.

Days until due

Flip the subtraction. Negative means overdue, so many people show this column only for bills that are still open and not yet due.

=D7-K2

Result: 6

6: BL-3122 for Greenleaf Produce is due on 21 November.

Step 3: Aging buckets from a lookup table

M2:N5 holds the lower bound of each bucket: 0 Not yet due, 1 for 1-30 days, 31 for 31-60 days and 61 for Over 60. VLOOKUP with TRUE finds the largest bound that's less than or equal to the days, so 36 lands in 31-60 days. Keep the table sorted smallest first. Changing the buckets later means editing four cells, not every formula.

Column G wraps it in IF(F2=0,"Paid",...) so settled bills drop out of the buckets. Type the labels as "1-30 days", not 1-30: Excel turns a typed 1-30 into a date.

=VLOOKUP(MAX(0,K2-D3),M2:N5,2,TRUE)

Result: 31-60 days

31-60 days: Metro Power's BL-3108 was due 10 October, 36 days ago, and nothing has been paid.

Totals per bucket

Give SUMIFS the whole list of bucket names and it spills one total per bucket next to them.

=SUMIFS(F2:F9,G2:G9,N2:N5)

Result: spills 4 rows × 1 column

3655.9
1000
612.8
450

3,655.90 Not yet due, 1,000.00 in 1-30 days, 612.80 in 31-60 days and 450.00 Over 60.

Supplier-by-bucket grid (Excel 365 / 2021)

UNIQUE down the side, TRANSPOSE of the bucket names across the top, one SUMIFS for the whole grid. In older versions, use one SUMIFS per cell or a PivotTable with Supplier in Rows and Bucket in Columns.

=SUMIFS(F2:F9,B2:B9,UNIQUE(B2:B9),G2:G9,TRANSPOSE(N2:N5))

Result: spills 4 rows × 4 columns

120000450
590.40612.80
1865.5100000
0000

A 4 x 4 grid: Harbor Packaging 1,200 not yet due and 450 over 60, Metro Power 590.40 not yet due and 612.80 in 31-60, Greenleaf Produce 1,865.50 not yet due and 1,000 in 1-30, Atlas Cleaning all zeros.

Step 4: What to pay in this payment run

This is the view the person approving payments wants: what's already late, and what falls due before the next run. SUMIFS on the due date with the as-of date in the criteria does it. Bills that are fully paid have an open balance of 0, so they add nothing.

=SUMIFS(F2:F9,D2:D9,"<"&K2)

Result: 2062.8

2,062.80 is overdue: 450.00 + 612.80 + 1,000.00.

Due in the next 7 days

Two conditions on the due date: on or after the as-of date and no later than 7 days after it. Change 7 to 14 for a fortnightly run.

=SUMIFS(F2:F9,D2:D9,">="&K2,D2:D9,"<="&K2+7)

Result: 1865.5

1,865.50: only Greenleaf Produce's BL-3122 falls due by 22 November.

Everything to pay by the next run, as a list

If your next run is 29 November, list every open bill due on or before that date, earliest first. FILTER and SORTBY are Excel 365 / 2021; in older versions, filter column D and sort it.

=SORTBY(FILTER(A2:A9,(F2:F9>0)*(D2:D9<=K2+14)),FILTER(D2:D9,(F2:F9>0)*(D2:D9<=K2+14)))

Result: spills 4 rows × 1 column

BL-3101
BL-3108
BL-3112
BL-3122

BL-3101, BL-3108, BL-3112 and BL-3122, oldest due date first.

Total for that run

The same conditions, summed. This is the cash you need in the bank by then.

=SUMIFS(F2:F9,D2:D9,"<="&K2+14)

Result: 3928.3

3,928.30: the 2,062.80 overdue plus the 1,865.50 due on the 21st.

Step 5: Early-payment discount dates

Some suppliers offer a discount for paying early, written as terms like 2/10 net 30: 2% off if paid within 10 days of the bill date, otherwise the full amount in 30. Harbor Packaging's terms are in P2 (rate) and Q2 (days), so they're easy to change and nobody has to edit a formula. Add a discount-date column for those suppliers and check it before each run.

=TEXT(C8+Q2,"d mmm")

Result: 17 Nov

17 Nov: pay BL-3126 by 17 November to take the discount.

Is the discount still available?

Compare the as-of date with the discount date. Returns the saving if there's still time, 0 if it's passed.

=IF(K2<=C8+Q2,E8*P2,0)

Result: 24

24: paying the 1,200.00 bill two days from now saves 24.00.

Step 6: Tie it back

Total open across the report should equal total billed minus total paid, and both should agree with the accounts payable balance in your books on the as-of date. Here: 9,313.70 billed minus 3,595.00 paid is 5,718.70, the same as the bucket totals.

If the check isn't 0, look for a payment keyed to a bill number that isn't in the list, a supplier credit note, or a bill missing from the export. A negative open balance means a bill was paid twice or overpaid: chase the refund or apply it to the next bill. To check the same file for repeated payments, see find duplicates; for a single days-to-pay ratio, see days payable outstanding. Prefer a fill-in layout? The free accounts payable tracker template is set up for this.

=ROUND(SUM(F2:F9)-(SUM(E2:E9)-SUM(I2:I5)),2)

Result: 0

0: the aging ties back to bills minus payments. Without ROUND you can get something like 9.09E-13 instead of 0: floating-point dust from adding decimals, not a real difference.

Common mistakes

Using TODAY() in the report

The numbers change every day and nobody can reproduce what was approved. Put the as-of date in a cell.

Aging from the bill date

For a payment run you care about the due date. Aging from the bill date makes bills on 60-day terms look late when they aren't.

Nested IFs for buckets

Use a small lookup table with VLOOKUP TRUE or XLOOKUP match mode -1. Changing the buckets becomes a four-cell edit.

Forgetting part-payments

Subtract payments with SUMIFS on the bill number, not a single lookup, so two part-payments both count.

FAQ

How do I create an AP aging report in Excel?

List open bills with due dates and amounts, subtract payments with SUMIFS to get the open balance, calculate days overdue from an as-of date, and look the days up in a bucket table. Total by bucket and supplier with SUMIFS or a PivotTable.

What is the difference between AP aging and AR aging?

AR aging lists what customers owe you and how late they are. AP aging lists what you owe suppliers. The formulas are the same; the AP version adds a payment-run view of what falls due soon.

How do I calculate days overdue in Excel?

Subtract the due date from the as-of date and wrap it in MAX(0, ...) so bills not yet due show 0: =MAX(0,$K$2-D2).

How do I find bills due in the next 7 days?

Use SUMIFS with two conditions on the due date: ">="&K2 and "<="&K2+7, where K2 is the as-of date. FILTER with the same conditions lists the bills.

Functions used

Related guides

Updated