Create and Download Late Payment Interest Calculator in Excel

When we take loans from a bank, we have to repay that loan with an interest after a certain period. This article will show you in stages to create and download late payment interest calculator in Excel.


๐Ÿ“ Download Practice Workbook

Download the practice workbook below.


Overview of Late Payment Interest

Late payment interest is the amount that we need to pay when we fail to repay the loan that we took and pledged to give back within a certain date. The late payment interest calculator will allow us to calculate the monthly interest amount and how much we will have to pay for various delayed dates. The formula for finding the late payment interest is as follows:

Late payment interest formula


Learn to Create and Download Late Payment Interest Calculator in Excel with These 2 Suitable Steps

To demonstrate things easily, we will consider a dataset with different names, invoice dates, due amounts, and periods in days within which one must repay their loans. The dataset will also contain the rate of interest per year. Now we will use these data to calculate late payment interests of different users in two steps. The dataset is shown as follows:

late payment interest calculator sample dataset


Step

Step 1: Finding Over Days

We need to find the number of over days in the beginning to calculate late payment interest that we need to pay. To do so, we need to follow these steps:

  • At first, we insert a column before the Per Year Interest column. We select more than one cell, in this instance cell range F7:F9, and press Ctrl+Shift+Plus(+).
  • An Insert popup box will appear. We select the Entire column and press OK.

Shortcut to insert an entire column

  • A new column will be created. We name it Over Days.

Inserting an extra column named Over Days

  • We select the cell G6 and write down the formula below.
=F6-C6-E6

Formula for finding the Over Days

  • Then we press Enter to get the result.
  • After that, we drag Fill Handle at the bottom right of cell G6 and drag it down upto the cell G11.

Dragging the Fill Handle button

  • This will give us the number of days that have gone by after the loan payment deadline under the heading Over days.

Finding the over days to calculate late payment interest

๐Ÿ“• Read More: 2 Approaches to Calculate Interest Between Two Dates in Excel


Step

Step 2: Calculating Interest Due

Now that we have our days that we need to calculate the interests, our next step is to calculate the due interests for each individual. We can complete this step by following these steps:

  • We create a column with the heading Interest Due.

Inserting a column named Interest Due

  • After that, we pick cell I5 and write down this following formula.
=(D6*H6)*G6/365

Inserting Interest Due formula

  • Next, we press Enter.
  • Then we drag Fill Handle at the bottom right of cell I5 and drag it down to cell I11.

Dragging the Fill Handle

  • The final output results will look like the following image.

Calculating the due interests

๐Ÿ“• Read More: 3 Ways to Calculate Accrued Interest on Fixed Deposit in Excel


๐Ÿ“„ย  Important Notes

๐Ÿ–Š๏ธย  The formula for calculating due interest has to be divided by 365 as the interest is calculated per year.

๐Ÿ–Š๏ธย  The Over Days column should be in the General format to be able to calculate the difference in the number of days from the date format.

๐Ÿ–Š๏ธย  The cells with the rate of interest should be in the percentage format.


๐Ÿ“ย  Takeaways from This Article

You have taken the following summed-up inputs from the article:

๐Ÿ“Œ ย We have to find the number of days that we need to count to calculate the late payment interest at first by calculating the number of over days.

๐Ÿ“Œย  We can calculate interest based on the over days by applying the formula for interest to get the final results.


Conclusion

To conclude, I hope that this article has helped you to understand how to create and download a late payment interest calculator in Excel. If you have any queries, reach out to us by leaving a comment below. Follow our page ExcelDen for more informative articles related to Excel.


Related Articles

(Visited 35 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo