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

Download Accounts Receivable Excel Template

Download Accounts Receivable Excel Template

Accounts Receivable Template is a ready-to-use template in Excel, Google Sheet, and Open Office Calc to manage customer invoices and payments.

Just insert your company name at the top and start using the template.

Additionally, you can record payments and also get invoice-wise outstanding and total accounts receivable outstanding.

Furthermore, it consists of multiple payment columns against each invoice so that you can also record part payments against invoices.

Table of Contents

  • What is Accounts Receivable?
  • Role of Accounts Receivable
  • Accounts Receivable Process
  • Accounts Receivable Journal Entries
  • Accounts Receivable Template
    • Contents of Accounts Receivable Template
  • Frequently Asked Questions

What is Accounts Receivable?

As per Wiki

Accounts receivable is a legally enforceable claim for payment held by a business for goods supplied and/or services rendered that customers/clients have ordered but not paid for. These are generally in the form of invoices raised by a business and delivered to the customer for payment within an agreed time frame.

In simple terms, accounts receivable ledger consists of the list of debtors to whom we have rendered a service or supplied goods along with details like invoice number, date of invoice, date wise payments received and outstanding receivables from a particular client or all in general.

Role of Accounts Receivable

Accounts receivable handles the following functions:

  1. Issue proper invoice against goods or services rendered.
  2. Record invoices into the respective customer account.
  3. Match the prices with the Purchase order or Delivery Challan.
  4. Report mismatch if any and issue debit/credit note against it.
  5. Ensure timely collection against invoices as per payment terms or contracts.
  6. Prepare the list of outstanding invoices that are due for long.
  7. Report settle invoices against collection done.
  8. Reconcile invoices and payments statements between company and customer.

In addition to the above, the accounts receivable department also verifies purchases and prepares aging analysis reports apart from customer management.

Accounts Receivable Process

Accounts receivable is a 4 step process:

  • Establishing credit practices.
  • Invoicing customers.
  • Tracking Accounts Receivable.
  • Accounting for Accounts Receivable.

In addition to the above, the accounts receivable department coordinates with the Sales department to ensure a smooth collection of payments as well as saving the company from bad debts or long outstandings.

Accounts Receivable Journal Entries

Journal Entries for Sales

Account Receivable A/C – Debit

To Sales A/C – Credit

Journal Entries For Payments Received

Cash or Bank A/C – Debit

Account Receivable A/C – Credit

Journal Entries For Payments Received With Discounts

Cash or Bank A/C – Debit

Sales Discount A/C – Debit

Account Receivable A/C – Credit

The sum of both credit amounts must be equal to the debit amount.

Journal Entries For Sales Return

Sales Return A/C – Debit

Accounts Receivable A/C – Credit

Accounts Receivable Template

We have created a simple and easy Accounts Receivable Template with predefined formulas and formating. Record date wise invoice and their respective payments and it’s done. The template shows invoice outstanding as well as total outstanding accounts receivable at any given point of time.

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.

Furthermore, when invoices are outstanding for longer periods, they refer to accounts receivable aging. You can download and use our ready-to-use Accounts Receivable Template With Aging.

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

Let us understand the contents of the template in detail.

Contents of Accounts Receivable Template

This template consists of 2 sections: the Header section and the Data Input section.

Header Section

Header section consists of the following:

Row 1 = Enter Company/Business Name. Cells A1 to K1. Merged cells.

Row 2 = Heading of the sheet. Cells B2 to K2. Merged cells. Headings can be put as Accounts Receivable Ledger or Debtors Ledger.

Row 3 = Multiple headings as numbered below:

  1. The subheading for the Current Date in cell A3.
  2. Next cell B3 will reflect the current date of the system using the formula =TODAY(). The date will automatically change according to the system date. You don’t need to change it every time and this saves your time.
  3. The subheading for Total Receivable in E3.
  4. In F3, the total receivable amount to date is displayed from the data below. The formula used here is =SUM(F5: F23).

Data Input Section

Dat Input section consists of the following:

Row 4 = This row consists of subheadings for data input. For example, see the table below:

table-1

Invoice Date, Invoice number, Customer name, Total Amount of Bill, payment 1, and payment 2 and their respective date are details to be put while entering data.

For the Due date, you can set a function that automatically calculates several days for you and show the exact date of payment.

For this, you need to set a function like this = cell address which contains date + Number of days for allowed. Here in our sheet, it is 60 days.

Thus, the formula entered is =A5+60. Copied to all cells below by simple copy-paste, this will apply the same settings to all cells.

Moreover, it calculates the balance by deducting the total amount of invoices minus the total of all the payments. It uses the following formula: =D5-SUM(H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5+AB5+AD5)

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

If you have any queries please share in the comment section below. We will be more than happy to assist you.

Frequently Asked Questions

What are 3 classifications of receivables?

You can classify receivable in the following 3 categories:

  • Accounts Receivable
  • Notes Receivable
  • Other Receivables

Are accounts receivable an asset?

Yes, Accounts Receivable is an asset. It is the amount a customer owes to a business and a business can convert it into cash upon due date. Moreover, Accounts Receivable are reported as a current asset in the balance sheet.

What does it mean by the term Creditor Days?

Creditor days is the average time it takes for a business to settle its debts with its trade suppliers.

What type of account is bad debt recovery?

Bad debt recoveries are the payments a business receives for the written-off debt which were considered uncollectible. Moreover, you need to report these receivable under the category of Other Accounts Receivable.

How do you record bad debt?

Debit the Bad Debt and Credit the Allowance for Uncollectible Accounts.

Is bad debt liability or asset?

Bad debt is a contra asset. It reduces the accounts receivable amount and the business reports these bad debts on the Income Statement under the Operating Expenses.

What are the two methods of accounting for bad debts?

You can account for Bad Debts by any of the following two methods:

  1. Direct Write-off Method
  2. Allowance Method
« Download Accounts Payable Excel Template
Download Cash Book Excel Template »

Filed Under: Accounting Templates

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

About Shabbir Bhimani

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

Comments

  1. KOTAY ZAR says

    February 28, 2017 at 12:58 pm

    electronicexperimentation@gmail.com

    Reply
    • Fahim Lashkaria says

      February 28, 2017 at 4:15 pm

      Kotay… thanks for the subscription and your email has been added to the list.

      Reply
      • Juliuskamwendo says

        March 23, 2018 at 11:05 pm

        I would like to be assisted on how to use excel spreadsheets

        Reply
        • Fahim Lashkaria says

          March 24, 2018 at 3:13 pm

          What assistance you want.

          Reply
          • peter says

            June 24, 2019 at 4:11 pm

            how do u get the fields calculation to refelct on all the rows

          • Fahim Lashkaria says

            June 24, 2019 at 4:18 pm

            All formulas used are given in the article. If you have any specific query please write the query mentioning the cell reference or elaborate your query to understand better and give you the exact possible solution.

  2. Muhammad Arsalan Yasir says

    November 6, 2018 at 5:46 pm

    How to change currency sign in invoice amount ?

    Reply
    • Fahim Lashkaria says

      November 9, 2018 at 5:37 pm

      You can do it in two ways:
      1. Select the range, go to the home tab -> Navigate to Numbers -> Select your desired currency.
      2. Select the range – Right Click -> Select Format Cells -> Under the Number tab select Currency and choose your desired Currency.

      Reply
  3. anwer hussain surti says

    August 26, 2019 at 2:41 pm

    Dear Fahim Lashkaria Sab,
    Assalam O Alikum

    Hope you are good, i am facing problem in parties ledger ageing can you help me in this matter

    Reply
    • Fahim Lashkaria says

      August 26, 2019 at 3:51 pm

      Dear Anwer,

      Please elaborate your query in detail.

      Reply
  4. Syed Arshin says

    January 27, 2020 at 11:46 am

    Can you share with me invoice sheet

    Reply
    • Fahim Lashkaria says

      January 27, 2020 at 4:02 pm

      Click on the link below it consists of 10 invoice templates and you can directly download the one you want by clicking the green download box:
      https://exceldatapro.com/invoice-template/

      Reply
  5. Travis says

    March 11, 2020 at 9:00 pm

    Fahim,

    This template is great! I need to make one change though which is causing me trouble.

    I do not want the Balance to change until the invoice has been paid.

    I have added another column titled simply “received” which i will then indicate either Yes or No.

    How can I change the formula to not change the balance amount until my “received” column states Yes??

    Thank you,
    Travis

    Reply
    • Fahim Lashkaria says

      March 12, 2020 at 1:02 pm

      It is possible with the IF statement.

      Reply
  6. Zeeshan says

    August 4, 2020 at 5:28 pm

    Kindly add my email address for new updates and changes

    Reply
    • Fahim Lashkaria says

      August 5, 2020 at 11:43 am

      For regular updates click on the link below:
      https://exceldatapro.com/blog/

      Reply

Trackbacks

  1. Download Invoice / Bill Microsoft Excel Template - Excel Data Pro says:
    October 18, 2016 at 10:01 am

    […] Invoice is a document issued by a seller to the buyer for a transaction. It consists of details of services or goods supplied along with rates of services provided or per unit price of goods. An Invoice is issued to the receiver of goods or services for payment, and the record is maintained in accounts receivable ledger. […]

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