Fee Grid Calculation

[John S]

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

 

[Eduardo]

Hi,
Let's assume that you enter the number in cell A24 in B24 enter

=SUMPRODUCT(--(A24>{0;10000;100000;500000}),A24-{0;10000;100000;500000},{0.01;-0.005;-0.00025;-0.00015})

Change cell to fit your needs

if this helps please click yes thanks

 

[Brad]

One method using no if statements - this assumes the number in question is in
cell E7.

=MIN(E7,10000)*0.01+MIN(90000,MAX(0,E7-10000))*0.005+MIN(400000,MAX(0,E7-100000))*0.0025+MIN(500000,MAX(0,E7-500000))*0.001

 

[John S]

This is perfect - Thanks so much - do you know where I can find a write up
that explains what is going in the formula so I may understand better????

 

[Eduardo]

Hi,
take a look here, different uses of sumproduct


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Gord Dibben]

=LOOKUP(B1,{0,10001,100001,500001,1000001},{0.01,0.005,0.0025,0.001})*B1

Will return #N/A for any number above 1,000,000


Gord Dibben MS Excel MVP