Create Late Payment Interest Calculator in Excel and Download for Free

Here’s an overview of the late interest calculator we’re building in the article. You can follow along or download the template to use immediately.


Download the Calculator

You can download the free Excel template from here.


How to Make a Late Payment Interest Calculator in Excel

We’ll use the following dataset that represents some buyers’ invoice details-their invoice dates, due amounts, contacted payment periods, contacted payment dates, and interest rates.

How to Make a Late Payment Interest Calculator in Excel

We have added two new columns to find the over-days and due interest.

How to Make a Late Payment Interest Calculator in Excel

Let’s start with the overdue duration.

  • Select cell G5.
  • Copy the following formula in it:
=F5-C5-E5
  • Hit Enter to get the output.

How to Make a Late Payment Interest Calculator in Excel

  • Drag down the Fill Handle icon over the cells G6:G9 to copy the formula.

How to Make a Late Payment Interest Calculator in Excel

Now let’s go for the due interests using a formula.

How to Make a Late Payment Interest Calculator in Excel

  • In cell I5 copy the following formula:
=(D5*H5)*G5/365
  • Press the Enter button.

  • Drag down the Fill Handle icon for the cells I6:I9 to get all the other due interests.

  • You will get all the interests.

Read More: Create a Simple Interest Loan Calculator with Excel Formula


Things to Remember

  • Don’t forget to divide the day number by 365 because the interest rate is given in per year format.
  • Make sure that the cells containing rates are in Percentage format.

Further Readings


<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. Sir I have question about this. If my customer is giving late payment for example his 60 days.but after 60 if he has Give me 30000.but he is giving me 4000 then after 5 days he is giving me 3000 like this he is giving then I hv charge for interest for this payment

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo