Create Late Payment Interest Calculator in Excel and Download for Free

Get FREE Advanced Excel Exercises with Solutions!

Are you late in paying the due amount of your invoice? And worried about the late payment calculation? Then you have come to the right place. Hope this article will be a quick guide to make a late payment interest calculator in an Excel worksheet including a Free Template to download.

The above image is an overview of the late payment interest calculator.


Download the Calculator

You can download the free Excel template from here and practice on your own.


What is Late Payment Interest

The late payment interest is the extra amount of money that you will have to pay if you fail to pay the amount according to the contracted date while taking a loan or for an invoice with a selected interest rate.

  • A very simple formula to calculate the late payment interest is:
Late Payment Interest = (Owed Amount)×(Interest Rate)×(Delayed Days/ 365)

Uses of Late Payment Interest Calculator

  • The late payment interest calculator will help you to know your monthly interest amount.
  • You will be able to know how much interest you’ll pay for different delayed dates.
  • It will help you to manage your other plans according to your late payment.

How to Make a Late Payment Interest Calculator in Excel

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

How to Make a Late Payment Interest Calculator in Excel

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

How to Make a Late Payment Interest Calculator in Excel

Now let’s find the over days first.

  • Activate Cell G5
  • Type the following formula in it-
=F5-C5-E5
  • Later, hit the Enter button to get the output.

How to Make a Late Payment Interest Calculator in Excel

We got the first buyers over days.

  • To get the other buyers over days just 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

All the over-days are now calculated. Now let’s go for the due interests using the formula mentioned above.

How to Make a Late Payment Interest Calculator in Excel

  • In Cell I5 write the following formula-
=(D5*H5)*G5/365
  • Then just press the Enter button.

And soon after, you will get the first buyers’ due interest.

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

Then you will get all the buyers’ due 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.

Conclusion

I hope the procedures described above will be good enough to make a late payment interest calculator in an Excel worksheet. Feel free to ask any question in the comment section and please give me feedback.


Further Readings


<< Go Back to Interest Calculator | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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