Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how to calculate the Aging of Accounts Receivable in Excel.
Introduction to Aging of Accounts Receivable
Aging of Accounts Receivable is a report that details how long an invoice has been unpaid. It is useful in determining the allowance for doubtful accounts. There are some segments in the report (often 0-29,30-59, and so on), and organizations record receivable collections according to the segments. This report is the primary tool that collection personnel use to identify which invoices are overdue for repaying.
Calculate Aging of Accounts Receivable in Excel: 3 Easy Methods
This is the dataset for today’s article. There are some orders with due dates and amounts. I will calculate the Aging of Accounts Receivable using this dataset.
1. Apply IF Function to Calculate Aging of Accounts Receivable in Excel
In the first method, I will show you how to calculate the aging of accounts receivable using the IF function. Let’s do this step by step.
Steps:
- First of all, calculate the Days Overdue. To do so, go to F7 and write down the following formula
=$C$4-E7- Then, press ENTER to get the output.
- Change the format to Number.
- After that, use Fill Handle to AutoFill up to F22.
- Now, to get the aging status, go to H7 and write down the following formula
=IF(F7>90,"90+ Days",IF(AND(F7>=60,F7<=90),"60-90 Days",IF(AND(F7>=30,F7<=59),"30-59 Days",IF(AND(F7>0,F7<=29),"1-29 Days","Not Due"))))
Formula Explanation:
The logical tests are
- F7>90
- AND(F7>=60,F7<=90)
- AND(F7>=30,F7<=59)
- AND(F7>0,F7<=29)
For F7 (108), the first test is TRUE. So, the corresponding output is 90+ Days.
- Now, press ENTER to get the output.
- Finally, auto-fill up to H22.
2. Use VLOOKUP Function to Calculate Aging of Accounts Receivable in Excel
Now, I will show another method. This time, I will use the VLOOKUP function. But, for this method, you have to modify the dataset a bit.
Steps:
- Now, it’s easy. Go to H12 and write down the formula
=VLOOKUP(F12,$F$5:$G$9,2,TRUE)
Formula Explanation:
- Excel will look for F12 in the array F5:G9.
- TRUE indicates that the match is an approximate one, not the exact one.
- 108 is the closest to 90. So the return is 90 or More.
- Then, press ENTER to get the output.
- After that, use Fill Handle to auto-fill up to H27.
3. Apply PivotTable Feature to Calculate Aging of Accounts Receivable in Excel
Now, I will show another method to calculate the aging of accounts receivable in Excel. This time, I will use the PivotTable feature.
Steps:
- Select the entire table.
- Then, go to the Insert
- After that, select PivotTable.
- Finally, choose From Table/Range.
- A box will appear. Excel will automatically set the range.
- Click OK.
- As a result, Excel will create a PivotTable.
- Then, 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.
- After this, you will get the following output.
- Now, to get the aging of accounts receivable, you have to group the Days Overdue.
- To do so, select any cell from the row representing the Days Overdue.
- Then, right-click your mouse to get the context bar.
- After that, select Group.
- A Grouping box will appear.
- Write 0 as the starting point.
- Then, keep 307 as the ending point.
- And, 30 as the grouping range.
- Finally, click OK.
- Excel will return the aging of the accounts receivable.
Things to Remember
- Use absolute referencing to lock a cell.
- The nested IF function tests the logical tests one by one.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have explained how to calculate the aging of accounts receivable in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.
<< Go Back to Ageing | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
