Forum Discussion
creating a fee calculator for tiered percentages of rent savings
Hello,
I'm seeking help to create a formula in excel to calculate a fee for 6% of rent savings up to $100,000 then adding 3% of rent savings of $100,001-$233,333 with a maximum fee of $10,000. Any help would be appriciated!
I've attached a spreadsheet containing a straight-forward formula that accomplishes the desired result. It could be refined further by adding named variables for those 100,000 and 233,333 cutoffs. I do use named variables for the percentages and max.
=MIN(MaxFee,
IFS(
A2<=100000,A2*BasicFee,
AND(A2>100000,OR(A2<=233333,A2>233333)),(A2*BasicFee)+(A2*SuppFee)
)
)
Thank you for your quick repley. This is the formula I used but it came back with an error code. Would you mind checking my work for accuracy? The rent savings is in cell AJ2 and is $500,000 so ideally the formula would generate the max fee of $10,000.
=MIN(10000,IFS(AJ2<=100000,AJ2*0.06,AND(AJ2>100000,OR(AJ2<=233333,AJ2>233333)),AJ2*0.06)+(AJ2*0.03))
I think it's what I highlighted in red belo=MIN(10000,IFS(AJ2<=100000,AJ2*0.06,AND(AJ2>100000,OR(AJ2<=233333,AJ2>233333)),(AJ2*0.06)+(AJ2*0.03))
