Forum Discussion
Tiered Calculation Formula Help
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.
I was also looking for this same formula for a similar application and was enthused to find your post. But when I apply your attached spreadsheet and its formulas, my results are slightly different than when I calculate them myself.
I had to change the values slightly to match my fee schedule, but the same principle applies:
0-$100,000 1.5%
the next $150,000 1.35%
the next $250,000 1.25%
the next $1,500,000 1%
Beyond at .75%
- I accidentally submitted before I finished.
In this example, someone with $250,000 would have an annual fee of $3,525. ($100,000*1.5%=$1500 + $150,000*1.35%=$2,025. $1500+$2025=$3525). The spreadsheet you provided gives me $3,425
The difference becomes greater the higher up the values become.
Example: At $1,180,000, the fee based on manual calculations comes out to $13,450. (($100,000*1.5%)+($150,000*1.35%)+ ($250,000*1.25%)+(the remaining $680,000 at 1%) or $1500+$2025+$3125+$6800).
This ends up being a blended rate of 1.14%, which makes sense logically, given the numbers.
Your spreadsheet shows a total fee of $12,725 or a blended rate of 1.08%. I'm not smart enough to figure out why it's off so much, but looking at the blended rate of 1.08%, it's pretty easy to see that it is off somewhere, given that almost half of the account is charged a fee of 1.25% or higher.
Can you help me understand what I am missing?
Thank youMach1351 See attached. Does that help?
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.
That's an interesting notion, Riny_van_Eekelen. I wonder if financial advisors do that in practice? Because, of course, presumably it IS more work managing a $20 million account than one worth "only" $1.5 million. Or $4 million.
Nevertheless, a worthy question to ask of such a service provider.
A further alternative would be to have an extended Fee Table in which the percentage declined for, say, every $5 million, gradually approaching zero above (you name it; but I think it'd be higher than $3 million)....
mathetes Capping fees may not be common in financial services industry. But that's not my area of expertise 🙂
When applying these kind of calculations to employee incentive plans, for example, it's quite common in the industries I've worked in (in Europe, that is), that such incentives aren't unlimited but capped at a number of times of target incentives. Obviously, the rates table will look "slightly" different as the incentive rate usually goes up for incremental revenues/order intake (or whatever the basis may be for the incentive).
Anyhow, just wanted to demonstrate that you can do these kind of calculations in one single formula with one helper column that holds the differential rate from the current to previous tier.
Thank you for your input! Riny_van_Eekelen
This was the original path I was headed down.. just couldn't quite get there. You and mathetes have solved my sleepless nights.
