• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Download Accounts Receivable With Aging Excel Template

Download Accounts Receivable With Aging Excel Template

The Accounts Receivable Template with Aging is a sophisticated financial instrument available in Excel, Google Sheets, and OpenOffice Calc formats. This template is designed to facilitate the efficient tracking and analysis of a company’s accounts receivable aging process.

This template serves as a comprehensive ledger for recording credit sales of goods or services. It meticulously documents the credit invoices issued by a company to its debtors, providing a clear and organized overview of outstanding payments.

Furthermore, the template offers advanced functionality, enabling users to generate detailed aging reports categorized by debtor, date, or specific time periods.

Table of Contents

  • What is Accounts Receivable?
  • What is Aging?
  • Why Is Accounts Receivable Aging Report Important?
  • Journal Entries for Bad Debts
    • Journal Entry for Bad Debts
    • Journal Entries for Bad Debts Recovery
    • Closing Journal Entry for bad debts
  • Accounts Receivable Template With Aging
    • Contents of Accounts Receivable Template With Aging
  • Frequently Asked Questions

What is Accounts Receivable?

As per Wiki

According to authoritative sources, accounts receivable represents a company’s legally enforceable claim for payment from customers who have received goods or services on credit. These claims typically manifest as invoices issued by the business, stipulating an agreed-upon payment timeframe.

These Accounts Receivables are our assets and listed as a current asset on our balance sheet. Accounts receivable can be further subdivided into two; trade receivables and non-trade receivables.

Trade receivables are from a company’s routine sales activities. Non-trade receivables are all other receivables, such as amounts due from employees against loans, etc.

What is Aging?

The aging process in accounts receivable refers to the systematic categorization of outstanding payments based on the duration they remain unpaid beyond the established credit limit. This limit, typically determined by the company’s credit department, is influenced by factors such as the customer’s financial stability and payment history.

It is worth noting that companies may adjust credit limit periods in response to changing market conditions, allowing for flexibility in managing customer relationships and cash flow.

Why Is Accounts Receivable Aging Report Important?

Asset Valuation: Higher accounts receivable contribute to increased asset value. However, it is imperative to ensure timely collections to minimize bad debts.
Process Monitoring: These reports enable companies to evaluate the effectiveness of their billing and collection procedures, facilitating regular and rigorous follow-ups with clients.
Credit Policy Management: Aging reports assist in defining and refining credit policies, allowing for client-specific evaluation of payment terms and necessary adjustments.
Cash Flow Management: Timely collection of accounts receivable is vital for maintaining healthy cash flows. Regular monitoring of aging reports can significantly reduce the risk of bad debts.
Collateral for Financing: Companies may leverage their accounts receivable as collateral for loans from financial institutions.
Financial Reporting: Aging reports are instrumental in determining appropriate allowances for doubtful debts during the preparation of annual financial statements.

Journal Entries for Bad Debts

Given below are the different Journal entries for reporting Bad Debts.

Journal Entry for Bad Debts

Bad Debts A/C – Debit

Debtor’s A/C – Credit

Journal Entries for Bad Debts Recovery

Debtor’s A/C – Debit

Bad Debts Recovery A/C –  Credit

Closing Journal Entry for bad debts

Profit and Loss A/C – Debit

Bad Debts A/C – Credit

Accounts Receivable Template With Aging

We have created an Accounts Receivable Template with advanced Aging functions. With Accounts Receivable Template you can efficiently and easily record the sales entries.

The Accounts Receivable Template is useful for Accounts Assistant, Accountants, Audit Assistants, etc.

Excel Google Sheets Open Office Calc

Click here to Download All Accounting Excel Templates for ₹299.

Important Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.

You can download other accounting templates like Cash Book, Petty Cash Book, and Purchase Return Book.

Contents of Accounts Receivable Template With Aging

This template consists of two sections: Header Section, Data Input Section, and Accounts Receivable Aging Report Section.

Header Section

In the Header section, the right-hand side consists of the Company name and name of the Ledger of Accounts Receivable.

The left-hand side consists of the Current Balance of Accounts Receivable amount. Thus, Balance amount = Total of Sales Invoice – Payments Received.

Accounts Receivable Template

Data Input Section

Accounts Receivable Template

The data input section consists of the following subheading for data entry of Receivables:

Customer Name: The legal name of the debtor’s company or individual.

Invoice #: A unique identifier for each invoice issued to debtors.

Date: The date on which the invoice was issued.

Invoice Amount: The total amount due, expressed in the desired currency.

Payment Terms: The predefined credit limit period, typically set to 30 days from the invoice date.

Amount Received: A record of all payments received against outstanding invoices.

Date Received: The date on which payment was received.

Due Date: Due date is the number of days allowed in addition to the date of the invoice. If you want to set longer limits are just change the number of in Payment Terms column.

The Formula applied here is as below: =IF(Table2[[#This Row],[Invoice Date]]=””, “”, Table2[[#This Row],[Invoice Date]]+Table2[[#This Row],[Payment Terms]]).

Amount Outstanding: The remaining balance after deducting payments received from the total invoice amount.

The formula here is =Table2[[#This Row],[Invoice Amount]]-Table2[[#This Row],[Amount Received]]

Days Past Due Date: Calculates the number of days exceeding the payment due date.

Formula Applied here is =IF(Table2[[#This Row],[Amount Outstanding]]=0,””,TODAY()-Table2[[#This Row],[Due Date]]).

Remarks: This column shows the status of the debtor. If the number of days past the due date is 0, then the cell will be blank. If the number of days is below 90, the cell will display a message to Call Customer.

Similarly, if the payment time is above the maximum limit or due date, then it will show the message as Bad Debt. In this template, the maximum limit is above 90 days from the due date.

The formula applied here is =IF(Table2[[#This Row],[Days Past Due Date]]=””, “”, IF(Table2[[#This Row],[Days Past Due Date]]<90, “Call Customer”, “Bad Debts”)).

Accounts Receivable Aging Report Section

This template generates customer-wise/debtor wise reports in a few clicks. Click on the drop-down menu and select the name of the debtor you want the report for and click OK. See image below:

Accounts Receivable Template

Accounts Receivable Template

Let’s discuss the template contents in detail.

Note: All the subheadings in the data input section are consist of the filter option using the table function. Thus, it allows you to generate a debtor wise, date wise, etc query/report as required.

You need to simply click on the drop-down button in a subheading and select the criteria of the report.

We thank our readers for liking, sharing, and following us on different social media platforms, especially Facebook.

If you have any queries or questions, share them in the comments below. We will be more than happy to help you.

Frequently Asked Questions

How do I prepare an AR aging report?

To prepare an AR aging report, one must first compile a comprehensive list of customers and their respective outstanding balances. Subsequently, it is necessary to calculate the number of days each balance has been overdue. This process involves determining the invoice due date and comparing it to the current date. It is advisable to utilize accounting software to automate this process, particularly for businesses with a large customer base or high transaction volume.

What is the average age of receivables?

The average age of receivables is calculated using the following formula:

(Accounts receivable in a specific accounting period × 365 days) ÷ Sales revenue collected in that period

This metric provides valuable insight into the efficiency of a company’s collection processes and the overall health of its accounts receivable portfolio.

How do you calculate debtors Ageing?

Groups outstanding invoices by customer and date range. Make outstanding invoices group for 1-30 days overdue, 31-60 days, 61-90 days, 91-120 days, and 120+ days.

Why is an Accounts Receivable aging report needed for an audit?

An Accounts Receivable aging report is crucial for an audit as it facilitates the proper valuation of accounts receivable. During an audit, the auditor will thoroughly analyze this report to:

  1. Assess the collectibility of outstanding balances
  2. Calculate and verify the adequacy of bad debt provisions
  3. Evaluate the effectiveness of the company’s credit policies and collection procedures
  4. Identify potential issues with specific customers or invoice categories
« Download Accounts Payable With Aging Excel Template
Download Budget With Charts Excel Template »

Filed Under: Accounting Templates

You are Here: Home / Excel Templates / Accounting Templates / Download Accounts Receivable With Aging Excel Template

About Shabbir Bhimani

I have worked in Excel and like to share functional excel templates at ExcelDataPro.

Comments

  1. ASHOK KUMAR KAR says

    January 22, 2017 at 10:39 am

    Sir,
    All your templates are very useful. Thank you very much for providing us such templates at free of cost.
    I have a request, can you provide us with a template for maintaining accounts of non-profit earning organizations, e.g. Clubs, Societies, Trusts etc. My suggestion is, Cash Book, Receipts Payments A/c, Income Expenditure A/c and Balance Sheet, all will be in one work book but in separate sheets. One entry to be get populated in all related areas.
    I hope, I shall get your help.
    Thank you again.
    Yours,

    Reply
    • Fahim Lashkaria says

      January 25, 2017 at 2:36 am

      Hi Ashok,
      I m really happy to hear that these templates are useful to you.
      We are working on some similar project as you requested. Once it is complete and published you will be notified by mail.
      Thank you.
      Fahim Lashkaria

      Reply
      • Pandenie says

        November 21, 2018 at 6:18 pm

        Hello

        do you have a worksheet with everything incorporated?

        Reply
        • Fahim Lashkaria says

          November 23, 2018 at 9:01 pm

          What do you mean by everything?

          Reply
      • Jacky Roy says

        June 19, 2019 at 12:19 pm

        Sir,
        Do u have any overdue receivable collection process excel database?

        Reply
        • Fahim Lashkaria says

          June 19, 2019 at 1:39 pm

          Hi Jacky, THis aging report will provide you with an overdue report at the end showing days of overdue. when you collect payment against the said payment it will decrease the same out from overdue amount.

          Reply
  2. Sajib Saha says

    December 18, 2017 at 8:49 am

    Better.

    Reply
  3. Naeem Patel says

    October 23, 2018 at 2:17 am

    Thank You So much for this awesome one click solution for small and medium business. I am happy to say that my all accounts problems are sorted here. Thanks again guys. Keep it up. Cheers!!

    Reply
    • Fahim Lashkaria says

      October 23, 2018 at 5:35 pm

      Thanks for the appreciation, Naeem. Glad to know that it was helpful to you.

      Reply
  4. Joey says

    August 14, 2019 at 6:29 pm

    Hi
    I wold like to know if you have a management account template made asy

    Reply
    • Fahim Lashkaria says

      August 15, 2019 at 4:43 pm

      We have financial analysis templates you can check them here.

      https://exceldatapro.com/templates/financial-analysis-templates/

      Reply
  5. ASMA KHAN says

    September 29, 2020 at 12:49 pm

    Thank you so much for this type of helpful formats in excel its make our work easier and simple withing short time

    Reply
    • Fahim Lashkaria says

      September 29, 2020 at 2:05 pm

      Thanks for the appreciation. Glad to know that it was helpful.

      Reply
  6. Ashley says

    December 12, 2020 at 2:42 am

    How can I add more Rows with the current functions applied?

    Reply
    • Fahim Lashkaria says

      December 14, 2020 at 1:02 pm

      Take the cursor to the last formatted cell. Press tab to insert the rows as needed. Copy the last cell and paste it to the remaining cells and you are done

      Reply
  7. Glenn says

    February 27, 2021 at 5:26 am

    How do I change currency to USD (usa dollars) ???

    Thank you!

    Reply
  8. beena says

    April 19, 2021 at 8:49 pm

    how is the math functions (None,average, sum,count,etc) displayed as dropdown list. kindly advise

    Reply
    • admin says

      May 9, 2021 at 4:41 am

      Not sure I am able to get your query

      Reply
  9. shariehan AL Jabri says

    September 11, 2021 at 1:28 pm

    Dear can you please share me Accounts Receivable With Aging Excel Template
    by email

    Reply
    • admin says

      September 15, 2021 at 6:25 am

      Why not click the link and download it?

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Accounting Templates

  • Accounts Payable Excel Template
  • Accounts Receivable Excel Template
  • Cash Book Excel Template
  • Invoice Excel Templates
  • Expense Report Excel Template
  • Petty Cash Book Excel Template
  • Inventory Management Excel Template
  • Billing Statement Excel Template
  • Purchase Return Book Excel Template
  • Accounts Payable With Aging Excel Template
  • Accounts Receivable With Aging Excel Template
  • Budget With Charts Excel Template
  • Checkbook Register Excel Template
  • Purchase Order Excel Template
  • Purchase Return Book With Tax Excel Template
  • Marketing Budget Excel Template
  • Asset Inventory Tracker Excel Template
  • Depreciation Calculator Excel Template

ExcelDataPro

Free Excel Pro Templates

  • Glossary
    • Accounting Glossary
    • HR & Payroll Glossary
    • Tax Glossary
  • Templates
    • Accounting
      • Financial Analysis
      • Financial Statement
    • Educational
    • Taxation
      • Federal Income Tax
      • GST
      • UAE VAT
      • UK VAT
    • Human Resources
      • HR & Payroll
      • HR Metrics
    • School Management
    • Social Media
    • Sales-Marketing
    • Personal Finance
    • Other
      • Health & Fitness
  • Functions
  • Calculators
  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2025. Content Licensed Under Creative Commons with Attribution Required

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT