Forum Discussion
creating a fee calculator for tiered percentages of rent savings
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.
Kate
It 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.
Yes, you are correct. the fee structure is 6% of the first $100,000 in savings for a max of $6000 and then an additional 3% of any amount in excess of $100,000 through $233,333 for a max of $4000 ($10,000 fee mat in total) . I'm unable to share the workbook becuase of confidentiality issues. In my current chart
cell AJ2 contains the dollar amount of rent savings and cell is the rent savings fee where I need the formula for the fee. Thanks so much for your help!
regards,
Kate
I certainly hope you see this, that you figure out we had miscommunicated--more precisely that I had misinterpreted your original request.
You don't say what version of Excel you're working with, but here's a much more fancy solution. It happens to require a current version of Excel, ideally a subscription to Microsoft 365.
I've attached a file that uses this solution. I also wrote a specific recommendation in a text box on that spreadsheet -- basically that you copy over the named ranges and this formula in their entirety.
If you need help, post a copy of your actual workbook and I'll implement this set of tables and the formula in your sheet.
In the meantime, here's the formula, with some explanation
=LET( enables us to name variables
feerow,MATCH(A2,Up_to,1), sets feerow as the row to use below
feebase,INDEX(BaseFee,feerow), sets feebase as the basic savings from BaseFee table
Pct2Add,INDEX(SavPct,feerow), sets Pct2Add as the Percent from SavPct table
overage,INDEX(over,feerow,1), sets100000 as base for calculating amnt over 100,000
MIN(MaxFee, starts MIN formula, to yield lesser of 10,000 or
feebase+((A2-overage)*Pct2Add) the result of feebase + (Pct of A2 - overage)
) end of MIN formula
) end of LET function
- Hi, I am using an older version of excel. its Microsoft office home and business 2016