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 greatly appreciated!

pfmje 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 as1.37% on the first 100K1.1% on the next 400K0.96% on the next 500K0.68% on all the rest? Or is there some other pattern expected?

pfmje 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.00the remaining $28,340 is charged 1.10% = 311.741370+311.74= 1681.74 Any thoughts on how to adjust?

mathetes Yes! That is correct. So.. For an account that is $85000 the fee would be $1164.50and an account that is $147000 the fee woudl be $1887.00 Thank you for your help!

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.

Tiered Calculation Formula Help | Microsoft Community Hub

mathetes
Silver Contributor
Apr 01, 2020
pfmje

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.
- pfmje
  Copper Contributor
  Apr 01, 2020
  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?
  - mathetes
    Silver Contributor
    Apr 01, 2020
    pfmje
    
    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?

Forum Discussion

Tiered Calculation Formula Help

Share

Resources