Tiered Fee Calculations

[lynchbro]

I have to perform a tiered fee calculation as follows:



$0 - $1,000,000,000 = 0.535% (Cell K1)
$1,000,000,001 - $2,000,000,000 = 0.485% (Cell K2)
$2,000,000,001 - $3,000,000,000 = 0.435% (Cell K3)
Over 3,000,000,001 = 0.385% (Cell K4)

Net Assets = 2,000,452,222 (Cell C1)

The fee will be charged in each designation. How can I write in a single cell how to calculate the fee based on the table above? Is it an IF statement nested?

[gebobs]

I would use a lookup to get the fee from a table based on one of the tier endpoints (minimum or maximum). It would be much easier to write and debug than the nested if it would require.

[BobBridges]

It would also be easier to maintain; you have a few rows and columns on a worksheet somewhere designating just what you said in your original post, and the VLOOKUP returns the correct percentage in one step, but if you change the ranges and/or percentages later then you need change only that one table and the rest of your references to it are updated automatically.

[gebobs]

Originally Posted by BobBridges

It would also be easier to maintain

Right. That is actually the biggest advantage and I'm so glad you pointed it out since it slipped my mind.

Also, I suggested to use one of the tier endpoints, either minimum or maximum. Minimum would be best since the minima are all specific.

See attached.

[lynchbro]

This does not work as you need to charge the fees at the levels of assets. IE: first billion .535 the next billion .485 and so on. I think I got it, see what I did attached.

[gebobs]

OK...I don't really understand I guess. :-)

But if you got it figured out, excellent. Cheers!