How do I calculate accounts receivable age in Excel?
Mia Phillips
Updated on February 08, 2026
Excel: Calculate Receivable Aging
- Which invoices are >30 days past due? Strategy: Subtract the invoice date from the TODAY() function.
- Use TODAY() to calculate days away. You might want to categorize the receivables into 30-day buckets.
- Grouping receivables into buckets.
How do you calculate average age of accounts receivable?
Aging of Accounts Receivables = (Average Accounts Receivables * 360 Days)/Credit Sales
- Aging of Accounts Receivables = ($ 4, 50,000.00*360 days)/$ 9, 00,000.00.
- Aging of Accounts Receivables = 90 Days.
How do I set up an aging formula in Excel?
How to Create an Aging Report in Excel
- Label the following cells: A1: Customer. B1: Order # C1: Date. D1: Amount Due. Enter in the corresponding information for your customers and their orders underneath the headlines.
- Add additional headers for each column as: E1: Days Outstanding. F1: Not Due. G1: 0-30 Days. H1: 31-60 days.
How do you maintain accounts receivable in Excel?
HOW TO CREATE AN ACCOUNTS RECEIVABLE LEDGER IN EXCEL
- Set up your Excel sheet to include “Invoice Dates” in column A, “Invoice Numbers” in column B, and “Due Dates” in column C.
- Add a column for “Total Amount Due” in column E and add the corresponding information.
What is the formula for today in Excel?
Example
| Formula | Description | Result |
|---|---|---|
| =TODAY() | Returns the current date. | 12/1/2011 |
| =TODAY()+5 | Returns the current date plus 5 days. For example, if the current date is 1/1/2012, this formula returns 1/6/2012. | 12/6/2011 |
How do I calculate age in mm/dd/yyyy in Excel?
Open Excel 2013. Type a birthdate into a cell in the format MM/DD/YYYY (if in the United States) or in the format DD/MM/YYYY if your country uses that format. Type =DATEDIF(XX, TODAY(), “Y”) then press Enter on your keyboard. Change the “XX” part of the formula to the cell containing the birthdate.
Is there an Excel template for accounts receivable aging?
This accounts receivable aging is an excel template that makes it easy to age invoices. It’s also been designed to support good controls over the accounts receivable process. It does this by providing a place for a ‘Prepared By’ and ‘Reviewed By’ signature and date. Businesses are not the only ones who can use an accounts receivable aging.
How to calculate the average age of receivables?
The formula to calculate the average age of receivables is Accounts receivable in an accounting period x 365 ÷ sales revenue collected in that period. How do you calculate debtors Ageing?
How do you age an invoice in Excel?
Update the red cells with current information. The only cell that must be completed is the Aging Date – this will be the date that each invoice is aged against. Use the Toggle options, in the upper right hand corner of the spreadsheet, to select between an aging based on Due Date and one based on Invoice Date.
