Sliding Scale Calculation

[Bradley09]

Hi All,

Currently working on something and need some assistance.

assume we have a client with $700,000 of investments and we charge a percentage of that.

I need the following to be calculated;

First $250,000 @1%

Next $250,000 to $500,000 at 0.70%


The residual value of above $500,000 at 0.50%


I know the answer is $5,250. I just can't figure out the formula to place in excel.

Thanks in advance

Brad

 

[AhoyNC]

Try:

Formula in B1

=SUMPRODUCT(--(A1>{0;250000;50000}),(A1-{0;250000;500000}),{0.01;-0.003;-0.002})

See this link which will explain the formula.
McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

Row/Col	A	B
1	700000	5250

<tbody>
</tbody>