--- type: Template title: Accounts Payable and Receivable Template (Free Excel) description: A free accounts payable and receivable template for Excel. Track money in and money out on two tabs, with an automatic net cash position. For small business. resource: https://accounting.events/resources/accounts-payable-and-receivable-template/ tags: [accounts payable and receivable template, accounts payable and receivable template excel, ap ar tracker, money in money out template] timestamp: 2026-10-19 author: Harry P. Smart fact_checked_by: Julie Turnbridge fact_checked: 2026-10-26 --- AP and AR Template # Accounts payable and receivable template A free Excel template that tracks both receivables (money in) and payables (money out), with an automatic net cash position. By Harry P. Smart, CPA and Lead Reviewer. Fact-checked by Julie Turnbridge on October 26, 2026. Published June 9, 2026. Updated October 19, 2026. 5 min read. Quick answer **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](https://accounting.events/resources/accounts-receivable-excel-template/). Preview Xaccounts-payable-and-receivable-template.xlsxAccounts Payable and ReceivableRECEIVABLES (IN)$18,260PAYABLES (OUT)$11,540NET POSITION$6,720Customer / SupplierDue dateAmountOutstandingStatusNorthwind Trading (AR)2026-06-014,2000SettledHarbor Logistics (AR)2026-05-153,2002,200OverdueApex Supplies (AP)2026-06-105,0005,000OpenTwo tabs (Receivables, Payables) plus an automatic net cash position. Free AP and AR template Excel (.xlsx) with Receivables and Payables tabs and an automatic net cash position. No sign-up. [Download Excel](https://accounting.events/resources/accounts-payable-and-receivable-template/accounts-payable-and-receivable-template.xlsx) **Video:** [Accounts Payable and Receivable Template (Free Excel)](https://www.youtube.com/watch?v=ZN_aL9AvKKk) ### 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. ## Key facts about AP and AR tracking - Net cash position is total receivables minus total payables. - Under the accrual method, [the IRS](https://www.irs.gov/publications/p538) 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](https://www.gov.uk/late-commercial-payments-interest-debt-recovery) and a £40, £70 or £100 recovery sum. ## 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](https://accounting.events/reviews/best-accounts-receivable-software/). ## 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](https://accounting.events/reviews/best-accounts-receivable-software/). ## Related reading Independent software reviews and guides from Accounting.Events. [Template ### AR tracking spreadsheet A deeper receivables-only tracker. Read more →](https://accounting.events/resources/accounts-receivable-excel-template/)[Guide ### AR vs accounts payable How the two sides of the ledger differ. Read more →](https://accounting.events/resources/accounts-receivable-vs-accounts-payable/)[All resources ### Resources, templates and calculators Every free AR guide, template and calculator we publish. Read more →](https://accounting.events/resources/) **About the author: Harry P. Smart, CPA and Lead Reviewer.** Harry P. Smart is a CPA based between London and New York City. Harry writes and leads the review of the software guides on Accounting.Events. **Fact-checked by Julie Turnbridge, Freelance writer and bookkeeper**, on October 26, 2026. Checked prices, review counts, awards, integrations and dates against each primary source. Last updated October 19, 2026. This guide is general information, not accounting, tax, or financial advice. ## Sources Sources checked by Julie Turnbridge on October 26, 2026: 1. [the IRS](https://www.irs.gov/publications/p538) (irs.gov/publications/p538) 2. [statutory interest of 8% plus the Bank of England base rate](https://www.gov.uk/late-commercial-payments-interest-debt-recovery) (gov.uk/late-commercial-payments-interest-debt-recovery)