Home › Templates › Accounts Receivable Aging Template
Accounts Receivable Aging Template
An aging report for up to 300 open invoices and 50 customers. It ages each invoice by days past due, totals by customer, and calculates the allowance for doubtful accounts from rates you set.
Free. Excel .xlsx format, no macros, no registration.
Contents
| Tab | Purpose |
|---|---|
| Instructions | Purpose, steps, a guide to each tab, the colour key and a list of every built-in check with its result. |
| Inputs | Company details, dates and the source data. All entry cells are shaded. |
| Schedule | Open invoices with due date, open balance, days past due and aging buckets, followed by a summary by customer with credit limit checks. |
| Journal Entry | The entry produced from the schedule, with a check that debits equal credits. |
| Reconciliation | Schedule balances compared with the general ledger, with differences flagged. |
| Lists | Account mappings and dropdown values, editable to match your chart of accounts. |
Features
- Buckets: current, 1-30, 31-60, 61-90 and over 90 days past due.
- Payment terms and credit limits are looked up by customer, with a default for customers without terms.
- The required allowance uses a loss rate for each bucket, with an optional specific rate for any single invoice.
- Customer summary shows each balance by bucket, its share of receivables and whether it exceeds the credit limit.
- The journal entry adjusts the allowance to the required balance, and the reconciliation ties the aging to the control account.
Instructions
- On the Lists tab, enter customers, payment terms and credit limits.
- On the Inputs tab, set the aging date, loss rates and general ledger balances.
- On the Schedule tab, enter open invoices and payments received.
- Review the Journal Entry tab for the allowance adjustment.
- Explain any difference on the Reconciliation tab.
Notes
Invoices are aged from their due date. Loss rates should be based on your own collection history.
Related calculators
- Allowance for Doubtful Accounts CalculatorCalculate the allowance for doubtful accounts with the aging method: receivables by age bucket times an expected loss rate for each.
- Days Sales Outstanding (DSO) CalculatorCalculate days sales outstanding and receivables turnover from accounts receivable and credit sales.
- Invoice Late Fee CalculatorCalculate interest on an overdue invoice from the amount, the annual rate and the number of days late.
- Early Payment Discount CalculatorCalculate the annualized return of taking an early payment discount such as 2/10 net 30, and the dollar saving on an invoice.
- Invoice Factoring CalculatorCalculate the advance, the fee and the effective annual cost of factoring an invoice.
More accounting templates
- Depreciation Schedule TemplateFixed asset register with monthly depreciation, disposals, roll-forwards by class, journal entries and general ledger reconciliation.
- Loan Amortization Schedule TemplatePayment-by-payment amortization with extra principal, journal entries for payment, accrued interest and the current portion, and a lender reconciliation.
- Bank Reconciliation TemplateTwo-sided bank reconciliation with outstanding items, book adjustments and the adjusting journal entry.
- Month-End Close Checklist TemplateClose checklist with owners, business-day due dates and status, a recurring entry register and an account reconciliation tracker.
- Prepaid Expense and Accrual Schedule TemplatePrepaid amortization and accrued expense schedules with both journal entries and ledger reconciliations.
- 13-Week Cash Flow Forecast TemplateWeekly receipts and disbursements for one quarter, with closing cash, minimum balance alerts and forecast-to-actual tracking.
Need a version built for your process? Request a custom template quote.
To report an error or request a calculator or template, send feedback.
Templates are provided for general information. They are not accounting, tax or legal advice. Review the formulas and results before relying on them.