Working out accruing %'s in excel

[ClaireCat]

I'm trying to work out how much money I'm owed back as a rebate from a company I do business for.

They give me an anual rebate based on my monthly turnover:

£0-£24,999k = 1%
£25k - £49,999 = 2%
£50k - £74,999 = 3%
£75k - £99,999 = 4%
£100k+ = 5%

The rebate is worked out in stages, so if i make £48k one month, I get 2% on £23,001 and 1% on the £24,999.
As I get the rebate annually, i wanted to make a spreadsheet with a column for every months turnover, a total turnover and a rebate due column. Is it possible to make a formula to show the total rebate due, based on all the above?

Its been confusing me all day.

 

[Airfix9]

Edit this to suit your workbook and cell references:

=IF(A1>=100000,((A1-100000)*0.05+2500),IF(A1>=75000,((A1-75000)*0.04+1500),IF(A1>=50000,((A1-50000)*0.03+750),IF(A1>=25000,((A1-25000)*0.02+250),A1*0.01))))

 

[shg]

Another way:

      ----A----- ---B---- C ----D----- ---E----
  1    Turnover  Schedule    Turnover   Rebate 
  2                          $ 25,000    $ 250 
  3         $ 0        1%    $ 26,000    $ 270 
  4    $ 25,000        2%    $ 50,000    $ 750 
  5    $ 50,000        3%    $ 51,000    $ 780 
  6    $ 75,000        4%    $ 75,000  $ 1,500 
  7   $ 100,000        5%    $ 76,000  $ 1,540 
  8                         $ 100,000  $ 2,500 
  9                         $ 101,000  $ 2,550

The formula in E2 and down is

=SUMPRODUCT( (D2>$A$3:$A$7) * (D2-$A$3:$A$7) * ($B$3:$B$7-$B$2:$B$6))