Hello I’m a basic Excel user. I have an invoice in Excel. Now how do I calculate the days that have passed if the customer fails to pay by due date? Also if we have a penalty clause of 18% per annum how can I calculate the value of the inoice on the late payment date? I would like to know how to implement this feature in my invoice in Microsoft Excel. I have searched on the Internet without success.
- First we find out the due date from the invoice
- Next we detrmine the date on which the customer can pay make the payment. We can call that date TODAY
- Since MS Excel treats the dates as numbers we can subtract the due date from the date on the day the customer pays and our formula would be =TODAY()-Due Date or TODAY()-A2 assuming that the due date of invoice payment is in cell A2
- Before we can perform the subtraction we must ensure that payment is being made after the due date and therefore we use the IF function to determine the situation and write our formula as =If (TODAY()>A2, TODAY-A2,””). We read the formula as: If the date of payment is greater than the due date then give us the difference else let the cell be blank
- To make the resultant entry of the formula more readable we also add a remark using the ‘&’ or concatenate function to add some friendly text like: & ” days past due date” so that the display in cell C2 looks something like this: 133 days past due date
- Normally it is a business practice to allow a few days grace or credit like 30 or 45 days to our business partners for the payment. We therefore deduct that credit period from the total number of days calculated and then perform the interest calculations on the amount due.
- Taking into account the % interest per year agreed upn with our business partner on delayed payments and the effective delay in payment we can calculate the total payment including interest as: (B2*18%*D2/365)+B2 where we have the amount due in cell B2, 18% is the per year penalty interest, cell D2 has the number of days delayed and 365 represents the number of days in a year. Once the interest is calculated the amount due is also added to arrive at the final amount due.
Watch the Excel training video below about calculating amount due on delayed invoice payments: