Forum Discussion
Tiered Calculation Formula Help
HI! I am trying to apply a tiered calculation to a column of values in an excel sheet to determine the Total Fee by Dollar. I need it to be one formula. Please see attached. Any help would be...
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 you
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 you
Mach1351 See attached. Does that help?
