Help with IF, OR formula

[CLaird]

I'm not sure how to explain this but I'll try my best. I'm attempting to write formulas to configure interest due for late invoices using 2 different interest rates, showing amount due at each rate, if applicable. For example, balances not paid after date due are charged 1.5% for the first 30 days and are charged 5% for 31 or more days. So, if customer A's unpaid balance is $1046 and is 45 days late, the formula would need to configure the interest at the 1.5% for 30 days, add that to beginning balance, then configure the interest at 5% for the number of days greater than 30 (in this case, 15 days). But if customer B's balance due is 10 days late, they would only be charged the 1.5%.

So, the formula would need to be able determine whether the total days late are less than or greater than 30 for each. The interest amount (without the original balance due) at each rate level needs to be displayed in separate cells.

[#2]

Hi @CLaird,

I'm not very good with interest calculation but something like this should work. 

=IF(D2<31,A2*0.015*D2,(A2*0.015*D2)+((D2-30)*A2*0.05))

See attached spreadsheet to have the formula in context.

[#3]

@Bennadeau 

Thanks! I'll give that a shot and hope it works the way I need it to.

[#4]

@CLaird unfortunately I believe there is an error in @Bennadeau  formula because >30 days will charge the 1.5% interest rate for the full duration (assuming D2 is the days overdue).  Simply replacing D2 with 30 will correct that.

=IF(D2<31,A2*0.015*D2,(A2*0.015*30)+((D2-30)*A2*0.05))

Alternatively you can simplify or expand that.  Since you said "The interest amount (without the original balance due) at each rate level needs to be displayed in separate cells." I think you want 2 formulas:

=MIN(30,D2)*A2*0.015

=MAX(0,D2-30)*A2*0.05

You can also replace D2 with MAX(0,TODAY()-B2) where B2 is date due

[#5]

@mtarler is right. Thanks for fixing my formula.

[#6]

@mtarler 

Thanks for the reminder about the max & min formulas. I completely forgot about using those which might work better than the IF, AND, OR formulas I originally thought of using.

I will certainly leave a response once I get this figured out, for those interested & future reference.