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 h...
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))
- Hi,
I copied and pasted what you had above and the error came up that it is missing an opening or closing parentheses. thanks for your help.
KateIt was missing a closing parenthesis at the very end. Did Excel not suggest a correction?
=MIN(10000,IFS(AJ2<=100000,AJ2*0.06,AND(AJ2>100000,OR(AJ2<=233333,AJ2>233333)),(AJ2*0.06)+(AJ2*0.03)))
I just re-read your original post and realized that I may have misunderstood your need. The formula I gave makes the percentage beyond 100,000 add 9%, but what you intended--I believe--is that it be 6% up to 100,000 plus 3% of the excess beyond 100,000. Is this in fact correct? If so, I'll need to correct how it works.
If this new interpretation is correct, it operates almost like the US tax tables, except that they raise the rates as one goes up the basic income ladder. You're reducing. But please confirm before we go further.