Calculation Based on Date Criteria

Hello. Before I ask the question, let me explain this. In our country, when we receive traffic fines, if we pay it within 15 days, we receive a 25% discount. If the payment is later than 15 days, we have until 1 month after we are notified to pay the full amount. After 1 month, for each month we don't pay the fine, we get +5% surcharge.

I created a formula that makes this calculation based on the payment date I would enter. However, it's very long and might force the computer unnecessarily when applied to an entire column (we receive about 100 each month, so there is a lot of rows). I attach a sample excel in which I added the formula for you to check it out. I was wondering if there might be a way to simplify it, make it shorter and more practical.

Originally Posted by kvsrinivasamurthy

In E2 then drag down.

=IF(D2<=C2,A2*0.75,IF(D2<=EDATE(B2,1),A2,A2*(1+(0.05*(MONTH(D2)-MONTH(B2)-IF(D2<=EDATE(B2,MONTH(D2)-MONTH(B2)),1,0))))))

This works unless the payment date is in another year. I love the logic in this formula, but I see it only considers the months of the dates. I tried something on my own, but couldn't quite figure it out.. Can you think of a way to overcome the year issue?

Originally Posted by Bo_Ry

Please try

=A2*(1+DATEDIF(B2,D2,"m")*0.05-(D2<=B2+15)*0.25)

Works flawlessly! Thanks so much!