Forum Discussion
Tiered Calculation Formula Help
Here's a screen capture that includes a formula. You really don't need the "Max" column to be there, as VLOOKUP with the "True" or "1" as the last entry, will always go to the next highest number and then fall back one.
Thank you for the response! mathetes
However, it is slightly off - the Total fee should equal $1681.74
the first 100,000 is charged 1.37% = $1370.00
the remaining $28,340 is charged 1.10% = 311.74
1370+311.74= 1681.74
Any thoughts on how to adjust?
Yes, I have an idea, but lets get the whole process clear then. (It wasn't clear to me that this was what you meant by "tiered" calculation. Maybe that's a technical term, and known to people in finance, or some other discipline; it seemed to me a perfectly clear interpretation could be that there are different percentages applied to different tiers based on account value. Full stop.)
If the account value is 7,654,321 is the fee calculated as
1.37% on the first 100K
1.1% on the next 400K
0.96% on the next 500K
0.68% on all the rest
?
Or is there some other pattern expected?
Yes! That is correct.
So.. For an account that is $85000 the fee would be $1164.50
and an account that is $147000 the fee woudl be $1887.00
Thank you for your help!
pfmje As an alternative, the attached workbook contains an example of a tiered calculation for whatever. Fees, bonus plans. You name it. Doesn't really matter what you calculate, as long as it's tiered. But you need to add a column (in this example, column C). Then you can use the formula in F to calculate the fee for the amount entered in E, in one, not very straight-forward but manageable, formula. For the fun of it, I also introduced a cap on the fee, so that fee will not keep increasing after the 3 million limit.
