Discount and Penalties Computation based on a pre-set Payment Date

[dargonaro]

Hello fellow members, greetings from Cebu Philippines!
A Statement of Account (SOA) shows an amount to pay and a deadline for payment.
If the payment is made within a specified period from SOA, the payee gets a discount; if the payment is made after the deadline, the payee gets penalties that accrue every month
How best translate this story into a function, considering that the penalties are accruing every month, I could not just go for a simple conditional formating function?
See Attachment for clarification, thank all in advance

[oeldere]

if your paymant is always 30 days you can use this one:

g9 =if($E$7<$E$9,$E$5*$F$9,0)

g10 = (INT(($E$7-$E$10)/30)+1)*$E$5*$F$10

please also notice that in your example you did not defined what should be done if the date is:

equal to 03-Feb-16 or 03-Mar-16

between 03-Feb-16 and 03-Mar-16


If pay before 03-Feb-16
If pay after 03-Mar-16

[dargonaro]

thanks oeldere, for your guidance. I appreciate your skills.
g9 works well.
g10 output is not as expected because the penalty of 3% is not accrued. In other words, 3% is only re-applied monthly on the due payment (20,000) and not on the [due payment + prior compounded penalty]. Say for a payment on 10 May, your penalty computation would be 1,800, but it should be 1,855
Could you amend g10 function accordingly?
Thanks

[oeldere]

Then please explain how you (manualy) get the result 1855 for 10 may.

Then I will make the excel formula for that.

[dargonaro]

Deadline for payment is march 3
From march 4 to april 3, the penalty of 3% is applied to the amount of 20,000 --> amount to pay in that period is 20,000+600= 20,600
From april 4 to mai 3,the penalty is 3% of 20,600--> amount to pay is 20,600+618 =21,218
From mai 4 to june 3, the penalty becomes 3% of 21,218 --> amount to pay is 21,218 + 636.54 = 21,854.54 (cumulated penalty is 1854.54)
Etc, etc....
In other words, the 3% is applied to the principal (20,000) + the compounded penalty of prior months
Thank you

[oeldere]

if your paymant is always 30 days you can use this one:

Another question:

1) the payment day is 30 days?

or

2) the payment depents on the days in a month?

01-04 30-04 30 days
01-05 31-05 31 days
01-06 30-06 30 days

[dargonaro]

The payment depends of the number of days in a month, as you indicated in 2)
Again thank you for your support.

[oeldere]

j10 =$E$5*(100%+$F$10)^(INT(($E$7-$E$10)/30)+1)-$E$5

see the attached file.

The payment depends of the number of days in a month, as you indicated in 2)

This did not solve the numbers of days, since it is counted with 30 days.

[dargonaro]

Thank you for your reply - my problem is perfectly solved.

[oeldere]

Thanks for the rep.

You're welcome. We appreciate the feedback!

Thanks for marking the question solved.