An accounts payable and receivable template tracks both sides of your cash: the money customers owe you (receivables) and the money you owe suppliers (payables), in one workbook. Seeing both together shows your net cash position at a glance.
This free template has a Receivables tab and a Payables tab, each tracking invoices or bills with due dates, amounts, outstanding balances and status, plus a Cash position tab that subtracts total payables from total receivables. For deeper receivables tracking, pair it with our AR tracker.
Key facts about AP and AR tracking
- Net cash position is total receivables minus total payables.
- Under the accrual method, the IRS counts income when earned and expenses when incurred.
- In the UK, late business payments carry statutory interest of 8% plus the Bank of England base rate and a £40, £70 or £100 recovery sum.
Accounts Payable and Receivable Template (Free Excel) (1:23)
Video chapters and transcript
- 0:00 Short answer
- 0:17 Free Excel workbook, also opens in Google Sheets
- 0:43 Status updates from today's date: Open, Overdue, Settled
- 0:58 Separate AP and AR duties where you can
How do you track accounts payable and receivables together? An accounts payable and receivable template tracks both sides of your cash: the money customers owe you, and the money you owe suppliers, in one workbook. Seeing both together shows your net cash position at a glance.
The free template on accounting.events is one Excel workbook. It also opens in Google Sheets, and the formulas work the same. It has three tabs.
Receivables is money in: customer invoices, due dates, what is outstanding and the status. Payables is money out: supplier bills, tracked the same way. Cash position subtracts total payables from total receivables.
The outstanding amount and the status, open, overdue or settled, calculate automatically from today's date. A positive cash position means more money is due in than out. Timing still matters, so watch the due dates on both sides.
Where you can, give accounts payable and accounts receivable to different people. One person controlling cash in and cash out weakens segregation of duties. A small team that cannot separate them should add a manager review.
What should an AP and AR template include?
An AP and AR template needs one place for money in, one place for money out, and a total that nets them off. Each line needs a due date and a status so you can see what is overdue on both sides. Five parts cover it:
- Receivables tab: customer invoices, amounts and outstanding balances.
- Payables tab: supplier bills, amounts and outstanding balances.
- Due dates: on every invoice and bill, so timing is visible.
- Status: Open, Overdue or Settled, calculated from the due date.
- Net cash position: total receivables minus total payables.
What is inside the accounts payable and receivable template?
The workbook has 3 tabs: Receivables for money in, Payables for money out, and Cash position, which subtracts total payables from total receivables to show the net position.
| Tab | What it tracks |
|---|---|
| Receivables | Money in: customer invoices, due dates, outstanding, status |
| Payables | Money out: supplier bills, due dates, outstanding, status |
| Cash position | Total receivables minus total payables, your net position |
Outstanding and status (Open, Overdue, Settled) calculate automatically on both tabs from today's date.
How do I track accounts payable and receivable together?
Use one workbook with 2 tabs, one for each side: receivables for money customers owe you and payables for money you owe suppliers. Compare the two totals to see your net cash position. The template calculates outstanding and status for you.
What is the net cash position?
The net cash position is your total receivables minus your total payables: a quick view of whether more money is due in than is due out. A positive figure is reassuring, but timing matters, so watch due dates on both sides. Intuit QuickBooks' 2025 Late Payments Report puts the amount the average US small business is owed in unpaid invoices at $17,500.
Should the same person handle AP and AR?
Different people should handle accounts payable and accounts receivable where possible, because one person controlling both incoming and outgoing cash weakens segregation of duties. Small teams that cannot separate them should add manager review of payments and receipts.
Does it work in Google Sheets?
The template works in Google Sheets: import the Excel file, and the SUM, IF and TODAY formulas across the 3 tabs calculate the same way as in Excel.
Example: net cash position with numbers
| Side | Name | Outstanding ($) | Status |
|---|---|---|---|
| Receivable | Blue Mountain Cafe | 1,960 | Open |
| Receivable | Harbor Logistics | 2,200 | Overdue |
| Receivable | Vertex Builders | 6,500 | Overdue |
| Payable | Apex Supplies | 5,000 | Open |
| Payable | Delta Print | 3,400 | Open |
| Payable | Ridge Utilities | 1,800 | Overdue |
Receivables total $10,660, payables $10,200 and the net is $460. Overdue receivables are $8,700 (81.6%); without Vertex Builders' $6,500 the net is -$6,040.
Is a combined template enough for a small business?
A combined template is enough for a small business with a manageable number of invoices and bills. As volumes grow, automated reminders and reconciliation save time, which is when dedicated software helps. See our AR software guide.
What mistakes should you avoid with this template?
- Treating all receivables as cash. $8,700 of the $10,660 is overdue; without Vertex's $6,500 the $460 net is -$6,040. Fix: check Overdue lines first.
- Ignoring overdue bills. Ridge Utilities' $1,800 is 17.6% of the $10,200 payables. Fix: filter Payables for Overdue weekly.
- Entering the invoice total. Harbor Logistics paid $1,000 of $3,200, so entering $3,200 overstates receivables by 9.4%. Fix: enter the amount paid.
Frequently asked questions
What is the difference between accounts payable and accounts receivable?
Receivables are money owed to you and count as an asset. Payables are money you owe and count as a liability.
Is net cash position the same as profit?
No. Net cash position nets open receivables and payables on one date. Profit compares income and expenses over a period.
How often should I update the template?
Weekly, and whenever you issue an invoice or receive a bill.
Bottom line: This template suits a small business with one person to update it weekly. Move to software when reminders and reconciliation take longer than the spreadsheet saves. See our AR software guide.
People also read
Independent software reviews and guides from Accounting.Events.
Sources
2 sources, checked by Julie Turnbridge on October 26, 2026
- the IRS irs.gov/publications/p538
- statutory interest of 8% plus the Bank of England base rate gov.uk/late-commercial-payments-interest-debt-recovery
Last updated October 19, 2026. This guide is general information, not accounting, tax, or financial advice.
