Nov 06 2020 11:29 AM
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.
Nov 06 2020 12:07 PM
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.
Nov 06 2020 12:31 PM
Thanks! I'll give that a shot and hope it works the way I need it to.
Nov 06 2020 01:03 PM
@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
Nov 06 2020 02:17 PM - edited Nov 06 2020 02:18 PM
@mtarler is right. Thanks for fixing my formula.
Nov 09 2020 08:04 AM
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.