How to Calculate Aging of Accounts Receivable in Excel

Here is the dataset for today’s article. There are some orders with due dates and amounts. We will calculate the Aging of Accounts Receivable using this dataset.

Method 1 – Apply the IF Function to Calculate the Aging of Accounts Receivable in Excel

Steps:

• Calculate the Days Overdue by inserting the following formula in F7:

• Press Enter to apply.
• Change the cell format to Number.

• Use the Fill Handle to AutoFill up to F22.

• To get the aging status, go to H7 and insert the following formula:

• Press Enter to get the output.

• AutoFill up to H22.

Method 2 – Use the VLOOKUP Function to Calculate the Aging of Accounts Receivable in Excel

We will modify the dataset a bit to include an aging table.

Steps:

• Go to H12 and insert the following formula:

• Press Enter to get the first result.

• Use the Fill Handle to AutoFill up to H27.

Method 3 – Apply the PivotTable Feature to Calculate the Aging of Accounts Receivable in Excel

Steps:

• Select the entire table.
• Go to the Insert tab.
• Select PivotTable.
• Choose From Table/Range.

• A box will appear. Excel will automatically set the range. Click OK.

• Excel will create a PivotTable.

• From the PivotTable Fields, drag Customer ID to Rows, Days Overdue to Columns, and Amount to Values.
• Excel will, by default, show the sum of the amount.

• You will get the following output.

• To get the aging of accounts receivable, you have to group the Days Overdue.
• Select any cell from the row representing the Days Overdue.
• Right-click to get the context menu.
• Select Group.

• A Grouping box will appear. Write 0 as the starting point.
• Keep 307 as the ending point.
• Use 30 as the grouping range.
• Click OK.

• Excel will return the aging of the accounts receivable.

Download the Practice Workbook

Download this workbook and practice while going through the article.

<< Go Back to Ageing | Formula List | Learn Excel