Interest calculations on delayed payments in Excel
One of the frequently asked questions by small businesses is how to calculate the interest payment on overdue bills. We enter the following headers in an Excel worksheet: Invoice Date, Amount due, credit period in days that we have offered our customer, date of payment by the customer. Next we calculate the days overdue. This is done by substracting the invoice date from the actual payment date and the number of credit days. You need to ensure that your ‘date’ entries are properly formatted. In case you receive a date value, just click on the cell and format it to a number. Based on the amount due, the overdue days, the number of days in a year (365) and the interest per year that the customer has agreed to pay, we calculate the interest due. The total amount now due from the customer will be the invoice amount plus the calculated interest. In Excel, as you can see in the training video, this is quite straightforward.
Calculating the late payment interest