formula to calcule delinquent interest

[jose123]

hi

Im trying to program a excel formula to calculate the delinquent interest of a loan. I want to know how to simplify the process in a simple math formula.

Im going to explain the process with an example

Lets suppose the client have a loan of USD 100,000, with a monthly interest of 3% and a monthly late payment interest of 5%. So, every month the client have to pay USD 3,000, and USD 150 of late payment interest.

*If the client delay the payment for two months its going to have to pay USD 300 for the first month, and USD 150 for the second month.
*If the client delay the payments only 5 days its going to have to pay the fraction of the month (USD 150 / 30 ) * 5
If the client delay the payment for three and a half month, its going to have to pay USD 450 for the first month, USD 300 for the second, and the fraction of the third month, and so on.

I probably could do this math easier with conditional excel formulas, but for this problem i need to resume everything in a purely math formula.

Thanks!!

 

[tkhunny]

I want to know how to simplify the process in a simple math formula.

What leads you to believe this can be done?

...everything in a purely math formula.

What does that even mean?

3% per month?! That's like Loan Shark territory. Are you sure it's legal? With late fees, it's almost surely not legal in any jurisdiction. Please see "Usury Laws". (Although, in some states, credit card companies can get away with 36% if you read and sign the agreement.)

1 month late: Monthly Interest Due * 5%
2 months late: (Monthly Interest Due * 5%)*2 + Monthly Interest Due * 5% = (Monthly Interest Due * 5%) * 3
3 months late: (Monthly Interest Due * 5%)*3 +(Monthly Interest Due * 5%)*2 + Monthly Interest Due * 5%= (Monthly Interest Due * 5%) * 6
...
n months late: (1+2+3+4+...+n)*(Monthly Interest Due)*5% = ½*n*(n+1)*(Monthly Interest Due)*5%

You'll have to calculate they final piece (last partial month) separately.