Accounts Payable Aging Report in Excel (Formulas + Free Template)

What an AP aging report shows

It is the mirror image of an accounts receivable aging report. Receivables aging tracks money customers owe you; payables aging tracks money you owe suppliers. The columns are the same idea, but the questions are different:

• Which bills are late? Late bills can cost late fees, a credit hold or a supplier relationship.
• How much cash do I need, and when? Bills due in the next 7, 14 and 30 days drive the payment run.
• Do the payables agree with the books? The aging total must equal the Accounts Payable balance in the general ledger at the same date.

Step 1: List every unpaid bill with its terms

Start with one row per supplier bill, from your accounting system’s open bills list or your bill folder. You need the vendor, bill number, bill date, payment terms in days (30 for Net 30), the bill amount and anything already paid. Put the report date in C3: the as-of date, usually the month end.

Step 2: Add the due date and the open balance

Because Excel stores dates as numbers, adding days to a date gives a date. In E6 and H6:

=C6+D6

=F6-G6

The due date is the bill date plus the terms. The open balance is what is still owed after part payments. Copy both down. Everything after this step works on the open balance, so a bill that is paid in full (GLS-301) drops out automatically.

Step 3: Calculate days past due

=IF(H6=0,0,MAX(0,$C$3-E6))

The as-of date minus the due date is the number of days the bill is late. MAX(0, …) turns a negative result (a bill not due yet) into 0, and the IF returns 0 for paid bills so they never look late. The $ signs lock the as-of date when you copy the formula down.

Prefer a live report? Put =TODAY() in C3. For a month-end report that must match the ledger, type the date so the report does not change tomorrow. To flag late bills visually, use a conditional format that turns late dates red.

Step 4: Spread the open balance into aging columns

Payables aging reports usually show each bill’s balance in the column for its age, so each column can be totaled. In J6 to N6:

The ranges do not overlap and have no gaps, so every bill lands in exactly one column. Total each column in row 18 and show its share of the open balance in row 19.

Tie the aging to the general ledger

An aging report that does not agree with the books is not finished. Enter the Accounts Payable balance from the general ledger (or your trial balance) at the same date in H21, then add two checks:

=IF(H22=0,"Ties","Check")

=IF(ROUND(SUM(J18:N18)-H18,2)=0,"Ties","Check")

H22 is the difference between the aging and the ledger, =ROUND(H18-H21,2). The first check proves the report agrees with the books; the second proves no bill fell between the buckets. If the ledger check fails, look for bills posted in the books but missing from the list, payments recorded on one side only, credit notes, or a bill entered twice.

Step 5: Summarize by vendor with SUMIFS

Owners and suppliers think in vendors, not bills. On the By Vendor sheet, list each vendor once and total every aging column with SUMIFS:

=SUMIFS('AP Aging'!K$6:K$17,'AP Aging'!$A$6:$A$17,$A11)

The mixed references let one formula fill the whole grid: the column moves across, the vendor row moves down. Add a total column and a share of total, then check that the grand total equals the AP Aging open balance.

The vendor summary is also the starting point for supplier statement reconciliations: compare each vendor’s total with the statement they send, and investigate the differences.

Step 6: Plan the cash and the discounts

The Cash Plan sheet turns the aging into a payment schedule by due date. Past due or due today:

=SUMIFS(open balance, due date, "<="&C6)

And each window, for example due in the next 1 to 7 days, with the window’s first and last date in B7 and C7:

=SUMIFS('AP Aging'!$H$6:$H$17,'AP Aging'!$E$6:$E$17,">="&B7,'AP Aging'!$E$6:$E$17,"<="&C7)

The windows add back to 22,853.05, so nothing is missed. A COUNTIFS next to each amount shows how many bills it covers (see how to count overdue items with COUNTIFS). Feed these amounts into a 12-month cash flow forecast so the payment run never surprises the bank balance.

The discount block checks an early payment discount. Terms of 2/10 net 30 mean 2% off if you pay within 10 days, otherwise the full amount in 30. For HRM-6120 (5,500.00, billed Oct 26, 2026), paying by Nov 5, 2026 saves 110.00, so you pay 5,390.00. The annual cost of skipping the discount:

=D17/(1-D17)*365/(D19-D18)

That is 2% / 98% for 20 extra days, or 37.24% a year. If your cash allows it, or you can borrow for less than that, taking the discount usually wins.

How to use it each month

1. Set the as-of date to the month end.
2. Add the new bills and enter the payments made, from the accounts payable ledger or bank statement.
3. Check that the aging ties to the Accounts Payable balance in the ledger.
4. Review the past-due bills first, then the Cash Plan windows, and choose what to pay in the next run.
5. Save a copy of the report with the month-end date for your records.

If you also track customer invoices, the invoice payment tracker covers the receivables side.

Frequently asked questions

Final checklist

• Every open supplier bill is listed once, with the date its terms start.
• Payments and credit notes are entered, so the open balance is current.
• Days past due count from the due date, using a fixed as-of date for month-end reports.
• The bucket columns add back to the open balance.
• The aging total ties to Accounts Payable in the general ledger.
• The cash plan windows add back to the same total.