How to Create Debtors Ageing Report in Excel Format

Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel Functions that we can use to create formulas. Many banks and other financial institutions use Excel files to store important data. They need to calculate various outputs and it’s easier to do that in Excel. This article will show you the step-by-step procedures to create a Debtors Ageing Report in Excel Format.

Introduction to Debtors Ageing

The tool by which business companies or financial institutions track the status of their accounts receivable is called Debtors Ageing. This is usually presented in a report format that groups outstanding invoices by customer and date range. Again, by accounts receivable, we mean the value of everything a debtor owes to a business in exchange for goods and/or services the business supplies. Moreover, companies set a credit limit and during this limit, debtors are supposed to pay their debts. Ageing starts when the payments get delayed upon this credit limit.

Step by Step Procedures to Create Debtors Ageing Report in Excel Format

In this article, we’ll show you the easy step-by-step guidelines to create a debtors ageing report in an Excel worksheet. We’ll apply some useful functions to make our report dynamic. Therefore, go through the below steps carefully to complete the task.

STEP 1: Input Data in Excel

  • First, we need to input accurate information about Customers.
  • In this example, we’ll insert names and other essential data.
  • Look at the below picture where you’ll see the dataset.

debtors ageing report in excel format

STEP 2: Find out Due Date

  • Now, we’ll calculate the Due Date with formula.
  • For this purpose, select cell I5.
  • Then, type the formula:
  • Subsequently, press Enter.
  • Next, use AutoFill to complete the rest.
  • Thus, it’ll return the exact due dates.

Read More: Make Credit Card Debt Reduction Calculator for Excel

STEP 3: Determine Outstanding Amount

Similarly, you need to find the outstanding amounts. The debt amount that is yet to be paid is the outstanding amount.

  • Firstly, click cell J5.
  • After that, insert the formula:
  • Consequently, press Enter.
  • Lastly, apply AutoFill.
  • Hence, you’ll get the outstanding amounts.

debtors ageing report in excel format

STEP 4: Calculate Days Past Due Date

In this step, we’ll determine the total number of days that have passed since the due date. We will insert the TODAY function here.

  • To do that, choose cell K5.
  • Here, input the formula:
  • Return the output by pressing Enter.
  • Afterward, choose the Number format for cell K5.
  • Therefore, it’ll return the number of days.

debtors ageing report in excel format

  • Subsequently, use the AutoFill tool to fill out the rest of the series.
  • However, press Delete for the K7 cell to clear the cell value.
  • This is because the customer has already paid his debts.
  • So, the dataset will look like the one displayed below.

debtors ageing report in excel format

Read More: Create Pay off Credit Card Debt Calculator in Excel

STEP 5: Add Remarks

Moreover, we can add remarks to make the report more practical. The concerned persons take decisions according to these remarks. Hence, follow the below process to add remarks. This will make the report dynamic.

  • First of all, select cell L5.
  • Then, type the formula:
=IF(K5="","",IF(K5<90,"Contact Customer","Issue Warning"))
  • After that, press Enter to return the remark.
  • Next, use AutoFill to complete the rest.

NOTE: The IF function returns blank if K5 is empty. If K5 is below 90, it’ll give out Contact Customer. Otherwise, you’ll see the Issue Warning.

Final Output

In this way, we can create a Debtors Ageing report in Excel format. The following figure is our final output.

Download Practice Template

Download the following template to practice by yourself.


Henceforth, you will be able to create the Debtors Ageing Report in Excel Format following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

<< Go Back to Debt TemplateFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

  1. very simple, good

  2. Good…
    How about if customer has more than one invoice with different dates

    • Hlw Jon S M,

      Thanks for your comment.
      If a customer has more than one invoice with different dates, you have to include multiple rows for that customer in the dataset. Each row will represent a separate invoice entry.
      The information related to that customer, such as their name, will be repeated in each row for their respective invoices. But the dates, invoice ids and invoice amounts will be different. The rest of the process is similar with this article.

      If you have other queries let me know in the comment.
      Sajid Ahmed

Leave a reply

Advanced Excel Exercises with Solutions PDF