Tiered Discount Calculations

Hello, below is a table highlighting the overview of the tiered pricing and the respective discounts and I would like to use a formula that could help calculate the total discount amount based on the expenditure.

Expenditure($) Mechanics
250,000
500,000 1st 250,000 = 5%; Next 250,000 = 6%
1,000,000 1st 250,000 = 5%; Next 250,000 = 6%; Next 500,000 = 8%
2,000,000 1st 250,000 = 5%; Next 250,000 = 6%; Next 500,000 = 8%; Next 1,000,000 = 10.5%
> 2,000,000 1st 250,000 = 5%; Next 250,000 = 6%; Next 500,000 = 8%; Next 1,000,000 = 10.5%; 2,000,000 onwards =13%

I have used both 'IF' and 'SUMPRODUCT' functions but the amount does not tally as the figures exceed $500,000. In case you require, below are the formulas for both functions.

=IF(D2>=250000, (250000*0.05)+((D2-250000)*0.06), IF(D2>=500000, (250000*0.05)+(250000*0.06)+((D2-500000)*0.08), IF(D2>=1000000, (250000*0.05)+(250000*0.06)+(500000*0.08)+((D2-1000000)*0.105), IF(D2>=2000000, (250000*0.05)+(250000*0.06)+(500000*0.08)+(1000000*0.105)+((D2-2000000)*0.13)))))

OR

=SUMPRODUCT(--(D2>{0,250000,500000,1000000,2000000}), (D2-{0,250000,500000,1000000,2000000}), {0.05,0.01,0.02,0.025,0.025})

Any idea what is the error for the above formulas?

Thank you for your assistance in advance!

Hello, I have attached the worksheet with the formulas.

Hello, thanks for the advice. This is the first time I am using either formulas for the calculations; hence, I was a bit confused. By using the sumproduct formula, there are some discrepancies between the manual calculations vs the Excel calculations.

For example, for $800,000 expenditure, the discount amount should be $45,500 instead of $51,500. Will you be able to advise what information you will require in order for me to rectify the formula?

Thank you!