3 Formulas To Calculate Excel Aging 30, 60, 90 Days

Managing accounts receivable and payable is a crucial aspect of maintaining a healthy financial status for any business. One effective way to keep track of outstanding invoices and payments is by using an aging report, which categorizes these transactions based on the number of days they have been outstanding. Excel, with its robust formula capabilities, is an excellent tool for creating and managing these aging reports. Here, we will explore three formulas to calculate Excel aging for 30, 60, and 90 days, enhancing your financial management and analysis capabilities.

Understanding Aging Reports

Before diving into the formulas, it's essential to understand the concept of an aging report. An aging report, or accounts receivable aging report, is a document that categorizes a company's accounts receivable according to the length of time an invoice has been outstanding. It typically groups invoices into categories such as current (not due yet or less than 30 days past due), 30 days past due, 60 days past due, 90 days past due, and over 90 days past due. This report helps businesses identify potential cash flow problems and prioritize collections.

Formula 1: Using the IF Function

The first formula utilizes the IF function in Excel, which is handy for creating conditions and returning values based on those conditions. The IF function can be used to categorize invoices into different aging buckets based on their due dates and the current date.

=IF(TODAY()-A2<=30,"Current",IF(TODAY()-A2<=60,"30 Days",IF(TODAY()-A2<=90,"60 Days","Over 90 Days")))

• TODAY() returns the current date.
• A2 is the cell containing the invoice due date.
• The formula checks the number of days between the due date and the current date, categorizing the invoice accordingly.

Formula 2: Employing the VLOOKUP Function

The VLOOKUP function is another powerful tool in Excel for looking up values in a table. It can be used to automate the aging process by referencing a table that defines the aging buckets.

1. First, set up a table with the aging buckets:

   Days	Category
   0-30	Current
   31-60	30 Days
   61-90	60 Days
   91+	Over 90 Days

2. Then, use the VLOOKUP function to categorize the invoices based on their age:

   =VLOOKUP(TODAY()-A2, aging_table, 2, FALSE)
   

   • aging_table is the range of the table created in step 1.
   • TODAY()-A2 calculates the age of the invoice.
   • 2 specifies that the function should return the value in the second column of the table (the category).
   • FALSE ensures an exact match.

Formula 3: Combining Functions for Dynamic Aging

For a more dynamic approach that can automatically update the aging buckets based on the current date, you can combine functions. This approach involves creating a helper column to calculate the age of the invoices and then using the CHOOSE function in combination with the calculated ages to determine the aging category.

1. Calculate the age of the invoice:

   =TODAY()-A2
   

2. Use the CHOOSE function with the calculated age to determine the category:

   =CHOOSE(IF(B2<=30,1,IF(B2<=60,2,IF(B2<=90,3,4))),"Current","30 Days","60 Days","Over 90 Days")
   

   • B2 is the cell containing the calculated age of the invoice.
   • The nested IF functions determine the position of the category in the list provided to the CHOOSE function.

Gallery of Excel Aging Report Examples

Excel Aging Report Gallery

These formulas provide a solid foundation for managing and analyzing aging reports in Excel. By implementing these solutions, businesses can better track their accounts receivable and payable, making informed decisions to improve cash flow and financial stability. Don't hesitate to explore and adjust these formulas to fit your specific needs, ensuring your financial management processes are optimized for success.