Please help Excel idiot. Formula question.

[cfrjwr]

I'm in the investment industry. I'd like to put a fee schedule formula into a cell so that I can calculate a client's fee based upon the assets in the client account. For example, our fee schedule reads that a client pays us .005 on the first $500,000; .0045 on the next $1,000,000; .0035 on the next $1,000,000; and .0025 on the remainder.

If I have a cell showing the client's account value, e.g., cell B1 has $675,000, how would I write the formula in the next cell to produce an accurate result of the client's annual fee based upon the amount in B1 and taking into account the fee schedule above?

Thanks for any help.

Charles

 

[Domenic]

Try...

=SUMPRODUCT(--(B1>{0,500000,1500000,2500000}),B1-{0,500000,1500000,2500000},{0.005,-0.0005,-0.001,-0.001})

Hope this helps!

 

[njimack]

The below formula assumes your rate is in A1:

=IF(A1<=500000,A1*0.005,IF(A1<=1500000,((A1-500000)*0.0045)+(500000*0.005),IF(A1<=2500000,((A1-1500000)*0.0035)+(1000000*0.0045)+(500000+0.005),((A1-2500000)*0.0025)+(1000000*0.0035)+(1000000*0.0045)+(500000*0.005))))

Of course, this could be vastly simplified by entering your rules into some cells which the formula links to, i.e

Min Max Rate
0 500,000 0.005
.
.
etc.