Sumproduct tiered rates

Dear all

I am struggling with what I believe is a fairly simple solution. I have a number of fee bands and rates at which they are charged. I want to be able to calculate the blended rate for different amounts by changing cell D1.

For example, for a value of 4,000,000, the fee should be 41,000. I understand that the formula is a SUMPRODUCT, but I cant get the figures to match!

Example attached.

Greatly appreciated.

Nick

Deleted the Post.

Take a look at John McGimpsey's site, here:

http://www.mcgimpsey.com/excel/variablerate.html

Hope this helps.

Pete

Hi Shukla

The rates are tiered. The first mio is charged at 1.25%, the next 2mio is charged at 1% and the next 1mio is charged at 0.85%. Total fee is 41k.

Thanks
Nick

See attached:

The SUMPRODUCT formula is

=SUMPRODUCT(($D$1>=$F$3:$F$9)*($D$1-$F$3:$F$9),$G$3:$G$9)

Table in F3:G9 which uses the increments (differences) in % (negative in your case!)

Another method,

=SUMPRODUCT(D$3:D$9,B$3:B$9)

In D3:

=IF(SUM(C$3:C3)<$D$1,C3,$D$1-SUM(D$2:D2))

See the attached file.