How to Calculate Aging of Accounts Receivable in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 uses to identify which invoices are overdue for repaying.

Read More: How to Use Ageing Formula for 30 60 90 Days in Excel


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.

aging of accounts receivable in excel


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.

IF Function to calculate aging of accounts receivable in excel

  • 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.

IF Function to calculate aging of accounts receivable in excel

  • Finally, auto-fill up to H22.

IF Function to calculate aging of accounts receivable in excel


Similar Readings


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)

VLOOKUP Function to calculate aging of accounts receivable in excel

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.

VLOOKUP Function to calculate aging of accounts receivable in excel

Read More: How to Calculate Days with Aging Formula in Excel


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.

PivotTable to calculate aging of accounts receivable in excel

  • 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.

PivotTable to calculate aging of accounts receivable in excel

  • 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.

PivotTable to calculate aging of accounts receivable in excel

  • 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.

PivotTable to calculate aging of accounts receivable in excel

Read More: How to Do Aging Analysis in Excel


Things to Remember


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.


Related Articles

Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo