Calculating Monthly Interest Charges

Nelja,
Re.
"I am trying to figure out the formula for overdue payments. All clients have a due date of Jan/31/2020. We charge a late fee of $5 monthly once someone is 10 days past the due date, and charges the 10th of each month thereafter. We also charge an annual 10% interest starting 20 days after the due date, and compounds monthly on the 20th of each month (interest calculated includes original amount due, plus late fees, plus any prior interest). I am trying to figure out this formula in excel. Any help would be greatly appreciated!"

start point for what seems, at a first look to be a simple process
within the row? for the client's details:
you will have the date the agreement took effect,
the amount loaned
The date of the last payment made on time
the date of the last payment made
the date of the last calculation of interest and charges

So - update the last calculation date, to today's date - note not just = today()
you have the last payment - so if that date was more than 10 days past the due date - there is 1 month's $5.00 fee due
and if that date is more than a "month" after the duedate+10 days
use date(y, m, d) on last payment and the just set date of the calculation, or maybe datediff () depends on how your process is set to count months -
you can add more $5.00 charges.

Similarly do the interest payments re the 20th of the month,

Now - there are problems that - interest on a daily basis is not 10%/365
and "months" may have different numbers of days ,
and what about months where the close of business on the 19th or is it the 20th, or the 21st is not usable as that day is not a banking day !
and the amount owed will change as penalties are applied, and unpaid interest due accumulates.

So - maybe you should be maintaining a running total of the debt, decreased by payments, and increased by interest on the debt, and penalties levied, and interest on them.

So - a row in a sheet detailing each debt owed.

and another sheet with each charge and payment having individual date entries -
The will allow the debt owed sheet to do sumproduct() of the number of balance-to-date of the debt as each new payment or charge is added
That does, however mean that there has to be an entry for every change in the balance owed.
And the interest owed can be the total of the charges, and individual interest calculations - to as many decimal places as you want,
and from that you subtract the total amount paid ( which will be in cents)
then you need to either round the difference in charged, and paid to the nearest cent - either up, or down - your choice.

You then have the problem of determining what is actually an overdue for payment amount - depending on the actual due date of the interest payment against the date that there was a payment last made,
and how the late fees will be included within that.

Why are you showing calculations that don't work for about 25% of the years ?
No rounding to values that a bank will process,

if the interest is to be a daily rate, then the daily rate that equates to the declared 18% needs to be calculated, and then that interest rate compounded for the number of days the interest is being calculated for
as in - for a leap year it's 366 days not 365
and the 18% - well what daily rate - in that year will (1+rate)^366 give 0.18
- or maybe use the built-in Excel formulas for that

and when would the 366 rather than the 365 apply -
as in what is the "year" start and end that applies for the period the interest is being calculated for -

as in period 1st Feb to 29th Feb of the year probably needs the 366, but what about 1st Jan to 31st Jan of the same calendar year

and - then 1st Feb 2023 to 1st Feb 2024, or 2025
And 1st Feb 1900 to 1st Feb 1901

Also - for many calculations if payments are made on Jan 1st is that to be applied to the calculation as reducing the balance on the 1st Jan, the 2nd Jan, or maybe not until the 3rd Jan ?
different countries have different official "bank/public holidays"

Also - re "$5 monthly " what actually constitutes a "month"
remembering that only 7 "months" in the "Christian calendar " actually have 31st days, and what about those where the elapsed month would be on the 29th, 30th, or 31st day of the following calendar month, if that "month" had that many days -

And ..
there is the problem of fractional amounts of the minimum currency portion managed by a bank ( as in what you can demand be paid)
and the rounding that would need to be applied - being done on a daily calculated, and rounded amount, or the monthly calculated amount rounded, or the 20 days interest rounded on that 20th day -
and, are all loans made on the 30th January to be repaid on the 31st of January, or the 31st of any month, or the last day of any month
And are fractional amounts of interest to be carried forward and included in the next period's interest before that is rounded
( can lead to a 30 day month's charge being more than the charge for a 31 day month - maybe only the penny - but that's bound to get queried by someone, and disputed - with a request to courts that the loan agreement be declared void due to the inappropriate payment amounts demanded.

If it ain't in the agreement ... .... ......

Hi Allen

Thanks for writing this post. This has provided me a starting point for my scenario.

However, I am struck as my case is little different. I give my customer a credit of 30 days and I charge simple daily interest from the 31st day.

Can you please help me with a tip for my case.

My Columns are

Col A - Date
Col B - Party Name
Col C - Purchase Amount
Col D - Payment Amount

HELP!

I am trying to figure out the formula for overdue payments. All clients have a due date of Jan/31/2020. We charge a late fee of $5 monthly once someone is 10 days past the due date, and charges the 10th of each month thereafter. We also charge an annual 10% interest starting 20 days after the due date, and compounds monthly on the 20th of each month (interest calculated includes original amount due, plus late fees, plus any prior interest). I am trying to figure out this formula in excel. Any help would be greatly appreciated!