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?
