---
type: Template
title: Accounts Receivable Excel Template (Free Tracker)
description: Free accounts receivable Excel template: tracks every invoice, calculates outstanding, status and days overdue, plus aging and 13-week forecast tabs.
resource: https://accounting.events/resources/accounts-receivable-excel-template/
tags: [accounts receivable excel template, accounts receivable template, accounts receivable spreadsheet template, tracking accounts receivable in excel, accounts receivable google sheets template, accounts receivable forecasting, accounts receivable forecast template]
timestamp: 2026-10-12
---
AR Excel Template

# Accounts receivable Excel template

A free, ready-to-use spreadsheet to track every invoice. Outstanding, status and days overdue calculate themselves, in Excel or Google Sheets.

[Download Excel (free, no sign-up)](/resources/accounts-receivable-excel-template/accounts-receivable-tracker-template.xlsx)

By [**Accounting.Events editorial team**](/about/)Updated **October 12, 2026****8 min read**

Quick answer

**An accounts receivable Excel template is a spreadsheet that logs every customer invoice and automatically works out what is still outstanding, whether it is overdue, and how many days late it is.** You enter the customer, invoice number, invoice date, due date, amount, and amount paid, and the template calculates the rest.

The free template below tracks invoiced, paid and outstanding totals, flags each invoice as Paid, Open or Overdue against today's date, and shows your overdue total at a glance.

It opens in Excel or Google Sheets and uses common functions such as TODAY, IF, SUMIF, SUMIFS and VLOOKUP. Replace the sample rows with your own invoices and you have a working accounts receivable tracker in minutes.

Two forecast tabs turn the same rows into an aging collection-rate view and a 13-week receipts forecast, so the tracker also shows when the cash should arrive.

Preview

Xaccounts-receivable-tracker-template.xlsxAccounts Receivable TrackerCustomerInvoice #Due dateAmountOutstandingStatusNorthwind TradingINV-10012026-06-014,2000PaidBlue Mountain CafeINV-10022026-06-091,9601,960OpenHarbor LogisticsINV-10032026-05-153,2002,200OverdueVertex BuildersINV-10042026-03-226,5006,500OverdueSunrise RetailINV-10052026-06-242,4000PaidTOTAL18,26010,660Outstanding, days overdue and status calculate automatically. Excel and Google Sheets.

Free accounts receivable tracker

Excel (.xlsx) with the tracker, an aging collection-rate tab and a 13-week forecast tab, or a plain CSV of the tracker. Holds 25 invoices and 10 customer averages. No sign-up.

[Download Excel (.xlsx)](/resources/accounts-receivable-excel-template/accounts-receivable-tracker-template.xlsx)[Download CSV](/resources/accounts-receivable-excel-template/accounts-receivable-tracker-template.csv)

Spreadsheets are still the fastest way for a small business to keep on top of who owes what. The trick is letting formulas do the aging and status for you so the sheet stays current without manual updates.

## How do you forecast accounts receivable in Excel?

**To forecast accounts receivable in Excel, estimate when each open invoice will be paid from the customer's payment history, then total the expected receipts by week.** The quickest method uses Days Sales Outstanding: AR forecast = DSO x (sales forecast / days in period). The aging and 13-week tabs in this template run the detailed methods for you.

### What does an accounts receivable forecast tell you?

An accounts receivable forecast tells you how much cash your open invoices and planned credit sales will bring in, and in which week or month, dated by each customer's payment history.

Forecasts that rely on due dates alone miss the lag: Xero Small Business Insights puts the average small business invoice [8.3 days late in the UK](https://www.xero.com/resources/small-business-insights/latest-united-kingdom) and [8.5 days late in the US](https://www.xero.com/resources/small-business-insights/latest-united-states). The forecast tabs in this template add that lag customer by customer.

### Which accounts receivable forecasting methods work in a spreadsheet?

Five methods cover most businesses, and the first four run in Excel or Google Sheets; in the example below, the aging method expects 135,400 from 155,000 of open invoices.

This template builds the aging and invoice-level methods for you; the DSO and collection-curve methods need your monthly credit sales and payment history. The 13-week rolling forecast is the weekly frame that holds the results of the five methods.

| Method | How it works | Best use |
| --- | --- | --- |
| **DSO method** | Forecast credit sales x DSO / days in period gives the receivables balance. | Balance sheet forecasts and budgets. |
| **Aging collection rate** | Group open invoices into [aging buckets](/resources/accounts-receivable-aging-report-template/), multiply each by its collection %, and add the results. | Cash from today's open invoices; bad-debt allowance. |
| **Invoice-level expected pay date** | Due date + that customer's average days to pay past the due date, summed by week. | Weekly cash planning from today's open invoices. |
| **Collection curves** | Each month's credit sales split by the month it gets paid. | Cash from sales you have not invoiced yet. |
| **Predictive models** | Statistical or machine-learning models score each invoice's likely pay date. | Large ledgers, inside software. |
| **13-week rolling cash forecast** | Weekly receipts for 13 weeks, re-forecast every week. | Cash planning for the next quarter. |

To build a collection curve, group invoices by the month you issued them, measure the share paid in that month, the next month and the month after, and average the last 12 months.

One example curve is 20% in the month of sale, 50% the next month, 25% after that and 5% never collected; apply it to planned billing to forecast cash from sales not yet invoiced.

### What are the accounts receivable forecast formulas?

Four formulas do the work in any accounts receivable forecast template: DSO, the AR forecast, expected collections and the roll-forward; with the example figures, a DSO of 42 days on 24,000 a month gives 33,600. The expected collections and roll-forward formulas run live in this file's two forecast tabs.

The DSO route runs in three steps. Step 1: forecast credit sales for the period. Step 2: calculate DSO. Step 3: multiply DSO by forecast credit sales per day.

- **DSO** = accounts receivable divided by total credit sales, times the days in the period. The [turnover calculator](/resources/accounts-receivable-turnover-calculator/) gives you last period's figure; see also [how to reduce DSO](/resources/how-to-reduce-dso/).
- **AR forecast** = DSO x (forecast credit sales / days in period).
- **Expected collections** = SUM(invoice amount x collection probability), with the probability set by aging bucket or by customer.
- **Roll-forward**: Opening AR + credit sales - cash collections - write-offs = Closing AR. The [AR ledger template](/resources/accounts-receivable-ledger-template/) holds the running balance this reconciles to.

### Accounts receivable forecast example with numbers

A business with 24,000 of monthly credit sales and a DSO of 42 days should expect to carry 33,600 in receivables: 24,000 / 30 x 42 = 33,600. If DSO falls to 35 days the balance drops to 28,000 and releases 5,600 of cash.

The aging method works on today's open invoices instead. The collection percentages below are illustrative and the amounts are in your own currency; set yours from cash collected per bucket over the last 12 months.

| Aging bucket | Outstanding | Expected collections | Collection % |
| --- | --- | --- | --- |
| Current | 80,000 | 78,400 | 98% |
| 1 to 30 days overdue | 40,000 | 36,000 | 90% |
| 31 to 60 days overdue | 20,000 | 15,000 | 75% |
| 61 to 90 days overdue | 10,000 | 5,000 | 50% |
| Over 90 days overdue | 5,000 | 1,000 | 20% |
| Total | 155,000 | 135,400 | 87.4% |

135,400 is the cash to expect from today's ledger. The 19,600 gap is cash you should not count on yet; compare it with your write-off history before you set a bad-debt allowance.

Applied to 100,000 of planned billing in one month, the example curve gives 20,000 in the month of sale, 50,000 the next month, 25,000 the month after and 5,000 never collected.

### Why forecast by payment behavior, not by payment terms?

Payment behavior forecasts cash better than payment terms because terms give the due date and behavior gives the date the money arrives.

A customer on Net 30 who has paid on day 52 all year is likely to pay around day 52 again, about 22 days after the due date. An invoice dated October 1 is due on October 31 and the cash lands on November 22.

Expected receipt date = due date + that customer's average days to pay past the due date. The 13-week tab applies it to every open invoice, using the average days to pay you enter for each customer.

### What are the best practices for an accurate AR forecast?

Five habits keep an accounts receivable forecast accurate, from segmenting customers to running base, upside and downside scenarios.

1. **Segment customers.** Forecast large or slow accounts one by one and the long tail by aging bucket.
2. **Update weekly.** Roll the 13 weeks forward with the latest payments and new invoices.
3. **Measure forecast vs actual variance.** Enter actual collections next to the forecast in the 13-week tab, read the variance column (actual collections minus forecast collections for the week), and watch DSO and the other [credit control metrics](/resources/credit-control-metrics/) on the [AR dashboard template](/resources/accounts-receivable-dashboard-template/).
4. **Separate committed from uncertain receipts.** A remittance advice is a committed receipt. An estimate from average days to pay is uncertain.
5. **Run base, upside and downside scenarios.** Shift average days to pay 7 days each way and read the closing AR.

Choose a spreadsheet forecast if someone can refresh it weekly and your invoice list fits the file's 25 rows. Choose software if you want expected receipt dates built from each customer's payment history as invoices sync in; Paidnice does this for Xero and QuickBooks Online, and the [independent software guide](/reviews/best-accounts-receivable-software/) covers the other options.

## What is inside the template

One tab tracks your invoices, two forecast tabs build an aging collection-rate view and a 13-week receipts forecast from it, and a fourth explains how to use the tracker. The tracker columns are:

| Column | What it does |
| --- | --- |
| **Customer, Invoice #, Invoice date, Due date, Amount, Amount paid** | You enter these |
| **Outstanding** | Amount minus amount paid, calculated |
| **Days overdue** | Days past the due date, from today, calculated |
| **Status** | Paid, Open or Overdue, calculated |

The TOTAL row sums invoiced, paid and outstanding, and a separate cell shows your overdue total so you know exactly how much is past due right now.

The Aging and collection rate tab pulls the open balances into five aging buckets with SUMIFS, applies the collection % you enter and shows expected collections.

The 13-week forecast tab gives every open invoice an expected receipt date (due date + that customer's average days to pay past the due date), sums the receipts by week and rolls Opening AR + credit sales - collections - write-offs forward to Closing AR, with a column for actual collections and the variance.

You enter average days to pay for up to 10 customers, and every other customer uses a default you set.

## How do I track accounts receivable in Excel?

Create a row per invoice with the customer, invoice number, invoice date, due date, amount and amount paid, then use formulas to calculate outstanding (amount minus paid), days overdue (=MAX(0, today - due date)) and a status flag. The template above already contains these formulas, so you only enter the invoice details.

## What should an accounts receivable spreadsheet include?

At a minimum: customer, invoice number, invoice and due dates, amount, amount paid, outstanding balance, days overdue and a status. Totals for invoiced, paid and outstanding, plus an overdue total, turn the list into a usable tracker.


## Does the template work in Google Sheets?

Yes. Open the Excel file in Google Sheets with File, then Import, then Upload, or use the CSV. Every function in the workbook, including SUMIFS, VLOOKUP and WEEKDAY, works the same in Excel and Google Sheets.

## How do I calculate overdue invoices automatically?

Use the due date and today's date. Days overdue is =MAX(0, TODAY() - due date) when an invoice is still outstanding, and the status is Overdue when today is past the due date and the balance is above zero. The template flags these for you.

## What are the five accounts receivable forecasting methods?

The five accounts receivable forecasting methods are the DSO method, the aging collection rate, invoice-level expected pay dates, collection curves and predictive models. The methods table above shows how each works and when to use it, and a 13-week rolling forecast combines them into one weekly view.

## What is a 13-week cash flow forecast?

A 13-week cash flow forecast is a weekly view of cash in and cash out for the next quarter, re-forecast every week so a new week 13 rolls in as week 1 drops off. The receivables side is expected receipts from open invoices plus collections on new sales, which is what the 13-week forecast tab in this template produces.

Thirteen weeks is the usual [operational horizon](https://hub.treasurers.org/bedrock-of-treasury-cash-flow-forecasting/) for treasurers, and the forecast is a weekly frame that holds the results of the five methods above rather than a sixth method.

## How does an AR forecast differ from a cash flow forecast?

An accounts receivable forecast predicts only the cash that customer invoices will bring in. A cash flow forecast adds the cash going out, so the AR forecast feeds the receipts row of a cash flow forecast.

## What is the best KPI for an accounts receivable forecast?

DSO (days sales outstanding) is the headline accounts receivable KPI, read alongside the collection effectiveness index. For a forecast, also track the weekly variance between actual and forecast collections. The [credit control metrics](/resources/credit-control-metrics/) guide defines DSO and the collection effectiveness index.

## Is there a free accounts receivable forecast template in Excel?

The download at the top of this page is a free accounts receivable forecast template for Excel and Google Sheets, with an aging and collection rate tab and a 13-week forecast tab. Both tabs use live formulas and sample rows, and there is no sign-up. Open it in Excel or Google Sheets and replace the sample invoices with your own.

## Is a spreadsheet enough, or do I need software?

A spreadsheet is fine for a small, manageable book of invoices. As volumes grow, reminders, late fees and reconciliation become time-consuming by hand, which is when teams move to dedicated accounts receivable software. See our [independent software guide](/reviews/best-accounts-receivable-software/). Teams on Xero or QuickBooks Online that outgrow a spreadsheet can compare the options there, including Paidnice.

## Related reading

Independent software reviews and guides from Accounting.Events.

[Template

### AR aging report template

Group unpaid invoices into aging buckets automatically.

Read more →](/resources/accounts-receivable-aging-report-template/)[Template

### AR ledger template

A running customer ledger with auto balances.

Read more →](/resources/accounts-receivable-ledger-template/)[All resources

### Resources, templates and calculators

Every free AR guide, template and calculator we publish.

Read more →](/resources/)

AE

[Accounting.Events editorial team](/about/)

The Accounting.Events editorial team researches and maintains the software comparisons on this site. Rankings are built from published vendor pricing, verified third-party reviews on the Xero App Store, G2 and Capterra, and primary-source statistics. Every guide carries the date it was last checked. Figures here are drawn from public sources; always confirm with your accountant or the linked source before acting.

Last updated October 12, 2026. This guide is general information, not accounting, tax, or financial advice.