Tiered Calculation Formula Help

Riny_van_Eekelen
Platinum Contributor
Apr 03, 2020
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.
- Mach1351
  Copper Contributor
  Feb 26, 2022
  Riny_van_Eekelen
  
  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%
  - Mach1351
    Copper Contributor
    Feb 26, 2022
    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
- mathetes
  Silver Contributor
  Apr 03, 2020
  Riny_van_Eekelen
  
  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)....
  - Riny_van_Eekelen
    Platinum Contributor
    Apr 03, 2020
    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.
mathetes
Silver Contributor
Apr 03, 2020
pfmje

I woke up this morning realizing that the solution to your request is to emulate the way the tax table works. So I've extended your table (and given it the range name "FeeTable" in the formula that results. Here's the new table:
And here's an example of the formula and an explanation of the components. Cell A11 contains the Account Balance for which you're going to calculate the appropriate fee and it is used in each of the VLOOKUP formulas, in each case just retrieving one of the data elements in that row of FeeTable.
=VLOOKUP(A11,FeeTable,3,1)
+
(VLOOKUP(A11,FeeTable,5,1)
*
(A11-VLOOKUP(A11,FeeTable,6,1)))
The first VLOOKUP instance retrieves the info from column C, the "base fee" based on reaching that tier
the second VLOOKUP retrieves the percentage to be applied
and the third VLOOKUP retrieves the number from column F, which is then subtracted from the balance itself
The formula then concludes by ;multiplying the excess $ by the applicable percentage and then adding that to what I've called the "base fee"

I've attached the resulting spreadsheet. Try it out. Have fun. Let me know if you have any questions.
- pfmje
  Copper Contributor
  Apr 04, 2020
  Thank you! mathetes
  
  These tiered fee schedules are common for financial companies. I thought a VLOOKUP could be a solution.
  
  Appreciate the help!
  - mathetes
    Silver Contributor
    Apr 04, 2020
    pfmje
    
    These tiered fee schedules are common for financial companies.
    
    And are they commonly computed in this fashion as well?
    
    I have dealt with financial companies myself. (I am a retiree, living on my IRA.) I just always assumed that the lower fees were it (the way I first approached this). I don't recall anybody ever explaining the proposed fee as "Well, John, we charge x.xx% on the first $100,000, a shade less on the next $400,000, another shade less up to a million, and so on. Any questions?" And in articles on the subject, there's just the reference to "At that level, people are usually paying x.xx%" rather than (again) some convoluted explanation of how they get there.
    
    On the other hand, I can see how it makes sense. The net effect of doing it as we've now done it (as you always wanted it) is that the fee very gradually declines, on a dollar by dollar basis, as the total asset base increases.
    
    Or increases as the asset base declines. Or does it? It certainly would if you followed it mechanically. But may I ask you a customer relations question? Say I'm one of your clients, living (as I've already said) on my IRA's Required Minimum Distribution, and that as I approach my 80s, my asset base has started to decline. But I've been a client of yours for decades. And at its peak, my IRA deserved a nice low fee rate. I'm wondering if there's a clause in your contracts that entitles long-time customers to that lowest fee rate attained, even as their IRA (plus whatever other total assets you might be managing) starts to decline.
    
    Just curious. It's kind of the converse of the "Cap" that @Riny_van_Eekelen incorporated in his ingenious solution.
mathetes
Silver Contributor
Apr 02, 2020
pfmje

Before I take any more time delivering the formula (more likely formulas, plural) that you seem to be asking for, I want to make absolutely sure of the business requirement.

Each example you've given has been for account levels at the lower end of the spectrum. The question I asked you most recently had to do with a figure at the high end. Your answer, "yes," that it was to apply each successive level's percentage, in sequence, seemed to me to be unlikely (or "unwise"; take your pick). Why wouldn't that person with the multi-million dollar account just expect--and be given--the lowest rate?

I wrote my question thinking it was an absurd interpretation of "tiered calculation," so, frankly, was surprised when you said "Yes! That is correct." And then, the fact that you responded with examples that were at the bottom end, not taking my $7,654,321 figure and working it through the maze of laddered/tiered percentages, I wondered if you'd really read my question and thought about the implications of doing it that way.

The closest example that comes to mind of another kind of "tiered calculation" is that of the table in our Federal Income Tax instructions where it shows how to take your taxable income and figure the taxes owed. The table (there are various ones for single married, etc, but they all have a similar form not unlike the one you have presented), looks something like the example in the attached sheet. The good thing about that is that you're able (were you to want to write your own formula) to do a single VLOOKUP to go from your taxable income [comparable to your "account value" in this example] to a base rate plus additional percentage for anything above. That is, it's a single lookup value that delivers the entire set of info needed to finish the calculation.

You (your company, your organization) seems to be making this far more complicated than it needs to be, because you're asking for a computation at each level [along with the slightly constricting "it must be a single formula" expectation) when the objective is simply to come up with a descending percentage--the converse of the tax table's increasing percentage--based on an incoming figure to base it on.

So that's why I'm coming back. It IS possible to do what you're asking, but I wonder if it's really truly what you should be asking. I hope that makes sense. And if it really is what you're asking, I'd like to hear "why"--why is somebody insisting on doing it that way? To what extent has thought been given to doing it in a way more comparable to the tax table example attached? It IS far simpler to explain to clients AND far simpler to implement and maintain.
TaxTableExample.xlsx10 KB

Forum Discussion

Resources