How to Create a Debtors Ageing Report in Excel Format

Debtors Ageing is a tool by which business companies or financial institutions track the status of their accounts receivable. This is usually presented in a report format that groups outstanding invoices by customer and date range. Ageing starts when the payments get delayed upon this credit limit.


  • This image shows our sample dataset.

debtors ageing report in excel format

STEP 1 – Find Due Date

  • Press Enter.
  • Use AutoFill to complete the rest.

Read More: Make Credit Card Debt Reduction Calculator for Excel

STEP 2 – Determine Outstanding Amount


  • Select cell J5.
  • Insert the formula:
  • Press Enter.
  • Apply AutoFill.

debtors ageing report in excel format

STEP 3 – Calculate Days Past Due Date


  • Select cell K5.
  • Input the formula:
  • Press Enter.
  • Choose the Number format for cell K5.
  • The days past due date is returned numerically.

debtors ageing report in excel format

  • Use AutoFill to complete the rest.
  • Press Delete for the K7 cell to clear the cell value as the customer has cleared their debt.
  • 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 4 – Add Remarks


  • Select cell L5.
  • Enter the formula:
=IF(K5="","",IF(K5<90,"Contact Customer","Issue Warning"))
  • Press Enter to return the remark.
  • Use AutoFill to complete the rest.

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

Final Output

The following figure is our final output.

Download Practice Template

Download the following template to practice by yourself.

Related Articles

<< Go Back to Finance 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