• 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

Accounts Receivable Template With Aging is a ready-to-use template in Excel, Google Sheet, and OpenOffice Calc that find your Accounts receivable Aging.

This template records the sale of services or goods by a company made on credit. In other words, Account receivable Ledger records the credit invoices of a company to its debtors.

Moreover, you can also generate debtors-wise, date wise, period wise aging reports with this template.

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

Accounts receivable means enforceable claim for payment that a business holds for supply of goods or services to customersand not paid for. These are generally in the form of invoices that a busines raises and delivers it to the customer for payment within an agreed time frame.

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 total amount of accounts receivable which a company allows to a debtor/customer is often limited by a credit limit. This limit is set by the company’s credit department. The payments delayed upon this credit limit is called aging.

These limits are based on the finances of the buyer and its payment history. You can increase or decrease the credit limit period according to market conditions.

Why Is Accounts Receivable Aging Report Important?

The higher the accounts receivable the higher our assets will be. But Just making good sales is not enough. Collections should also be timely and will help decrease our bad debts. Thus, it must be kept in mind that we must have a minimum amount of bad debts.

Moreover, it helps us to monitor our billing and collection process. This includes regular and strict follow-ups with clients for payments.

The aging report also helps us to define and manage credit policies. This includes client-wise evaluation of payment terms and making necessary changes as and when required.

The collection of Accounts Receivable defines the cash flow of a company. Delays in accounts receivable collections can lead to lower cash flows. Monitoring of aging reports at regular levels can help us to decrease the chances of Bad Debts.

Furthermore, a company can use its accounts receivable as collateral for loans from banks or other non-banking finance corporations.

Moreover, it is also helpful in defining the Allowances for Doubtful Debts at the time of preparing yearly 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: Name of Debtor’s company or individual.

Invoice #: Invoice number of the Debtors.

Date: Date of invoice issued by Debtor

Invoice Amount: Amount of invoice in the desired currency.

Payment Terms: Predefine the credit limit period. Here it is mentioned 30 days from the date of invoices issued.

Amount Received: Record all the receipts of receivables in this cell.

Date Received: Date of payment.

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: This column shows the balance amount after deducting payments received from the total amount of invoice.

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

Days Past Due Date: This column shows the number of days past to the Due date of payment or end of credit limit time. Thus, it calculates by deducting today’s date from the 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 aging report, you need to first list the customer’s name and list their outstanding balances. After this count the delay days since it has become overdue.

What is the average age of receivables?

The formula to calculate the average age of receivables is Accounts receivable in an accounting period x 365 ÷ sales revenue collected in that period.

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?

Accounts Receivable Aging Report helps to determine the proper valuation of our accounts receivable. An auditor will analyze this report and calculate the bad debts.

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

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
  • FAQ’s
  • Blog
  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2023. 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