Excel guide · 6 min read
How to make an accounts payable aging report in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Bill | Supplier | Bill date | Due date | Amount | Open | Bucket | Paid for | Paid | As of | ||
| 2 | BL-3101 | Harbor Packaging | Aug 8, 2026 | Sep 7, 2026 | 1450 | 450 | Over 60 | BL-3101 | 1000 | Nov 15, 2026 | ||
| 3 | BL-3108 | Metro Power | Sep 10, 2026 | Oct 10, 2026 | 612.8 | 612.8 | 31-60 days | BL-3112 | 1240 | |||
| 4 | BL-3112 | Greenleaf Produce | Oct 1, 2026 | Oct 31, 2026 | 2240 | 1000 | 1-30 days | BL-3115 | 980 | |||
| 5 | BL-3115 | Harbor Packaging | Oct 9, 2026 | Nov 8, 2026 | 980 | 0 | Paid | BL-3119 | 375 | |||
| 6 | BL-3119 | Atlas Cleaning | Oct 20, 2026 | Nov 19, 2026 | 375 | 0 | Paid | |||||
| 7 | BL-3122 | Greenleaf Produce | Oct 22, 2026 | Nov 21, 2026 | 1865.5 | 1865.5 | Not yet due | |||||
| 8 | BL-3126 | Harbor Packaging | Nov 7, 2026 | Dec 7, 2026 | 1200 | 1200 | Not yet due | |||||
| 9 | BL-3130 | Metro Power | Nov 10, 2026 | Dec 10, 2026 | 590.4 | 590.4 | Not 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.
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.
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.
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.
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.
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.
Result: spills 4 rows × 4 columns
| 1200 | 0 | 0 | 450 |
| 590.4 | 0 | 612.8 | 0 |
| 1865.5 | 1000 | 0 | 0 |
| 0 | 0 | 0 | 0 |
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.
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.
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.
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.
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.
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.
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.
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
SUMIFSAdds with several conditionsMAXLargest numberVLOOKUPVertical lookupUNIQUERemoves duplicates, spillsTRANSPOSEFlips rows and columns, spillsFILTERKeeps matching rows, spillsSORTBYSorts by other columns, spillsTEXTNumber to formatted textIFPicks a result from a testROUNDRounds to digits
Related guides
- How to build a 13-week cash flow forecast in Excel
- How to make an accounts receivable aging report in Excel
- How to calculate days payable outstanding (DPO) in Excel
- How to calculate due dates and days overdue in Excel
- How to do a bank reconciliation in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to make a dependent drop-down list in Excel
Updated