Accounts Receivable and Payable Excel Template

This accounting template enables users to track money owed and due as well as how overdue invoices are, i.e., aging buckets. Though simple to use, there are a lot of summaries and reports this tool can produce.

Latest Excel Version (see Google Sheets version below):

$45.00 USD

The template can be downloaded immediately after purchase. Three templates are included (a Google Sheet version with the link in a Word file download and two Excel versions (macro and non-macro) 100% compatible with Office 365 and earlier Excel versions. This is included in the Accounting Templates for Excel bundle.

accounts payable tracking

How it works?

There are a few versions for entering data.

The first is an Excel version that uses a macro and an input card. The user can enter receivable invoices in one input card and payables in another. 

There is also a non-macro version included. On this version, the user can enter invoice data directly on the database tabs (one tab for receivables and one for payables) and bypass the input card.

There is also a direct database entry version included, which was built in Google Sheets. You will have all the necessary fields needed so that automatic tracking can happen. This version also utilizes some automated dynamic array formulas that are not available in Excel.

Note, when you go to clear out the example data in the database tabs, make sure you don't overwrite the last two columns in the database tabs where each invoice record resides because they are formulas that define the aging buckets each invoice is in.

Tracking payables and receivables is likely to improve your cash conversion cycle.

Both versions are included in the one-time price and are included on the download page after purchase.

Accounts Receivable

On a per customer and per invoice basis, the template will automatically display what money has been collected, what money is yet to be collected, and what the remaining balance owed to you is. This allows the breakdown of multiple invoices for the same customer.

Aging receivable reports also automatically populate based on the data entered. The buckets include:
  • Current
  • 1-30  Days
  • 31-60 Days
  • 61-90 Days
  • 91-120 Days
  • 121+ Days
These can be edited on the 'validation' tab to be any day ranges that you want. The template will display the total value of invoices that reside in each aging bucket.

Additionally, you will be able to see a 15 year summary by month on the amount owed/paid/balance for each of your customers as well as on an overall basis.

Accounts Payable

The same tracking and data analysis that is on the AR module is included for the AP module. The template will track how much money you owe / have paid out / and current balance on a per invoice and per vendor basis as well as the amount owed by aging bucket. To lighten up the file, you can remove some of the tracking years if not needed.

There is some VBA (one button per AR/AP and one button to update invoices across the board). This reduces errors and ensures you enter the data in a structured and organized way.


You will have three charts. One to track monthly P&L, one for running Accounts Receivable (total amount owed to you/paid/running balance and one for Accounts Payable showing total amount owed out, total amount actually paid out, and total running balance by month. All of these visuals go for one year. This can easily be changed to 2/3/4/5/10 years if desired.


You will be able to set up to 250 customers / 100 vendors to start. This can be expanded to more than 5,000 if needed and Excel probably hits a limit around 10,000 unless you have a more powerful version of excel (Microsoft charges somewhere around $100 extra dollars for that and it can handle 100's of millions of rows quickly and efficiently.

As far as the number of total records you can have in regular Excel, it will probably hit a max around 30,000 to 40,000 before things start to die out unless you have the more advanced version. The workaround is if you just have a single spreadsheet for each year and at the beginning of the next year you import / manually add any invoice that still has a balance.

Added a Google Sheets Specific Version (included in the $45 purchase above):

If you find this accounts receivable and payable spreadsheet tracker useful, you may enjoy using this inventory tracking template and this FIFO compliant COGS inventory valuation tool in excel

NOTE, I no longer offer two free hours of service with the purchase of this template.

You might also like: