Formula for a decreasing level of interest on a decreasing value.

I need to create a formula for the following (don't worry about the currency at this stage):

• 4% of the slice of the average monthly payment volume up to €5 million;

• 2.5% of the slice of the average monthly payment volume above €5 million up to €10
million;

• 1% of the slice of the average monthly payment volume above €10 million up to
€100 million;

• 0.5% of the slice of the average monthly payment volume above €100 million up to
€250 million; and

• 0.25% of the average monthly payment volume above €250 million.

To illustrate this:

If I input a value of 12 million, this would require a calculation of 4% of 5 million, 2.5% of 5 million, 1% of 2 million.

I am at a crossroads with how to prevent the 2.5% calculation going above the 10 million threshold and so on.

Any help would be greatly appreciated thank you.

one option

=SUMPRODUCT(--(B2>={0,5000000,10000000,100000000,250000000}),B2-{0,5000000,10000000,100000000,250000000},{0.04,-0.015,-0.015,-0.005,-0.0025})

above would generate 375k based on sample value of 15m

That is a really helpful start thank you.

I would need to have a breakdown per percentage however.

So,

Value: 15m
4%=
2.5%=
1%=
0.5%=
0.25%=
Sum=375k

if you need to split out

4.00%: =MAX(0,$A5*MIN($G$1,$B$2))
2.50%: =MAX(0,$A7*(MIN($H$1,$B$2)-$G$1))
1.00%: =MAX(0,$A9*(MIN($I$1,$B$2)-$H$1))
0.50%: =MAX(0,$A11*(MIN($J$1,$B$2)-$I$1))
0.25%: =MAX(0,$A13*($B$2-$J$1))

Thank you very much for your assistance.