1.ods (19.5 KB)
How can i calculate the penalties based on a compound formula?
A formula like this one: value*(1+p)^days.
If the loan is on 01/12/2025, then it is due on 31/12/2025, 01/01/2026 is the first day of penalties, so until then the amount should be 0 in this example.
I included multiple loans just to see how the formula works with complex situations. My situation is even more complex, i have 22 columns for which to apply this kind of formula.
Very important is that the penalties are not applied to themselves, it is illegal.
The power in the formula is only for the days that passed the due time, not including the first 30 days, and the amount should’t show the original amount. So for the first one, 31/12 being the last day of payment:
on 01/01: (3000*(1+0.3%)^1)-3000 (1 means it’s the first day of penalty)
on 02/01: (3000*(1+0.3%)^2)-3000 (2 means it’s the second day of penalty) and so on.
My main problem is that having multiple loans, on 02/01 it’s the first day of penalty for the second loan but the second day of penalty for the first loan.
I’m looking for a formula that applies to all cells on the D column for a whole year.
Thank you.
@emy1: a lot of: situation like this … lets say like thattopics recently ??
neither your screenshot, your 1.ods or your description tells some information to the reader!
please stop this spam.
@karolus
What do you mean? I did my best to describe the situation i’m encountering. What’s missing?
I can’t upload my file because of sensitive information, so i had to substitute it with an example.
anything!
- how to calculate Penaltie[s] ( from which values and date[s] ) and whats a penaltie at all in the context?
All I see is a prototypical compound interest formula`value*(1+p)^days but I can’t make any reasonable connection to your example figures and designations.
No, but I can do math… with you, it seems to be the other way around!
( and as non-english-native I know »penaltie« only from ice-hockey )
@karolus
Ok, let me explain. Penalties are the amounts of money to be paid extra on loans and their interest if the payments are not made on time.
Let’s say you took a 1000$ loan due in 30 days, but you didn’t make the payment in 30 days. From the 31st day there’s a % increase applied to what remained to be paid, and this % is applied every day of delay.
What i uploaded is an example of multiple loans, and i was wondering how can i write a function that fulfills what i’m looking for. What i’ve uploaded is just for example, to learn it. I can’t upload the file i’m working on, it has many more columns and sheets.
I’m also a non-english native.
OK, but where are the outstanding amounts and where is the interest rate per day?
Also: what
- Calendar Table (A);
- Value;
- Value2;
means in this?
Â
[due date] = [loan date + 30 days]
so 4 columns:
[loan date] [due date] [payment date] [$ to pay = applying the formula]
So, your outlined formula value*(1+p)^days suggests that the penalty be a percentage of a given amount (the B or C column?) calculated daily and immediately capitalizing the penalty. Is that right?
Assuming that due payment is the C column, due date is the A column and daily penalty is a flat rate of 0.5%. The required formula for cell D2 in the uploaded file would then be:
=(C2*((1.005)^MAX(0;TODAY()-A2)-1)
@karolus
I didn’t want to include all the complexity of a situation like this because there are many elements that complicate things much more.
@CRDF
The two columns is just an example of loans and other commissions that are subject to penalties. I didn’t want to include the interest to keep the things simple. That one is calculated differently.
@keme1
After 30 days have passed, not immediately. The percentage is of both columns if 30 days have elapsed.
I’m interested for a formula for the whole column, which goes for a full year.
To use a 30-day shifted due date, and sum the columns:
=((B2+C2)*((1.005)^MAX(0;TODAY()-(A2+30))-1)
If the due date is the 30th day, the shift you need may be 29, not 30. This depends on the specifics of your lending agreement.
This formula can be copied down the column. You may need to make adjustments to cater for your actual data structure, which (according to some comments) appears to differ from the sample given.
Note that the mathemathical solution of an exponential formula calculates the entire fraction. Financial calculations may use transactional logic, where each step is rounded “to the penny”. This may yield deviations from expected penalty. Such deviations may be significant if payment becomes long overdue.
@keme1
I tried both formulas on the file i uploaded. The problem with the formulas is that they calculate from day 1 and stop at day 4, from day 5, the result is 0.
Makes sense to me.
I assumed that each row was about a particular loan.
The uploaded file only shows amounts for the first 4 days.
Based on my assumptions, row 5 with zero due amount carries zero penalty.
What did I not understand?
Value = the loan principal;
Value2 = other commissions.
But what is the meaning of Calendar Table?
Are the days due dates?
Â
Also: apparently there are 4 loans in the sheet: B2:C5
Do you want to add up values from days 1, 2, 3, 4, and from December 5 on compound them all together?
Is it?
@keme1
@CRDF
If the loan is on 01/12/2025, then it is due on 31/12/2025, 01/01/2026 is the first day of penalties, so until then the amount should be 0 in this example.
I included multiple loans just to see how the formula works with complex situations. My situation is even more complex, i have 22 columns for which to apply this kind of formula.
Very important is that the penalties are not applied to themselves, it is illegal.
Not added together, because the loan on 01/12 is due on 31/12, the loan on 02/12 is due on 01/01 and so on.
So, assuming p = 0.000333
Penalties for 31/12 will show up 3535.13, right?
Â
And for 1/1 must it show up only 2323.08?
No add up of previous (3500) loan + (power = 31 in the formula)
the power in the formula is only for the days that passed the due time, not including the first 30 days, and the amount should’t show the original amount. So for the first one, 31/12 being the last day of payment:
on 01/01: (3000*(1+0.3%)^1)-3000 (1 means it’s the first day of penalty)
on 02/01: (3000*(1+0.3%)^2)-3000 (2 means it’s the second day of penalty) and so on.
My main problem is that having multiple loans, on 02/01 it’s the first day of penalty for the second loan but the second day of penalty for the first loan.
^1… 2 of course. My mistake.
But the question remains as of what must be
Penalty for 2/1
That’s the question now having you multiple loans on one sheet.
I know how it works, just that i don’t know how to write it in calc.