1.ods (19.5 KB)
How can i calculate the penalties based on a compound formula if passed more than 30 days?
A formula like this one: value*(1+p)^days.
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.
1 Like
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?
Your description with 3 date columns does not correspond to the uploaded file/image. 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.