Excel Formula Q: Tiered Fee Structure %

Water Cooler
#1

I’m having a helluva a time with an =ifstatement in excel. I’m trying to calculate fees using the following tiered fee structure. If any Excel champs want to take a shot that would be awesome!

Assume Contribution of: $4,500,000

First $500,000: 1.50%

$500,000-$1,000,000: 1.25%

$1,000,000 - $5,000,000: 1.00%

Over $5,000,000: 0.75%

Thanks!

#2

Disclaimer: Not completely out of influence at the moment.

Since there are so many variables, do yourself a favor and don’t nest these formulas in one cell.

#3

Well done Ohai!

#4

Nice Ohai, Thank you very much! I was trying to get fancy by nesting everything.

#5

Nice job. I often use that structure to figure out taxes (because of tiered rates).

#6

I’ve also done that with with a VLOOKUP. Here’s an example.

http://www.excel-university.com/income-tax-formula/

#7

So I’m working on a Comp Analysis and I need to consolidate some Fee Breaks into a single table. What consolidated Fee Break Table works best with these levels. I’m analyzing 6 firms and all don’t use the same fee breaks. Thanks for you help

  1. First $500,000
  2. Next $500,000
  3. Next $1,500,000
  4. Next $2,499,999
  5. $1,000,000 - $5,000,000
  6. $5,000,000
  7. Next $29,000,000
  8. Next $70,000,000