# How to Calculate Aging of Accounts Receivable in Excel

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.

## 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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF