Need help creating tiered commission formula

[JulesD]

I'm sure this has been asked a million times and I've tried searching but can't seem to figure this one.

I am trying to create a formula that will calculate commissions for an individual based on monthly sales but using a cummulative sales amount to tier him up to the next level.

Here is the commission structure:

$0 to $4,166,667 we will pay commission of 0.1%
$4,166,667 to $8,333,333 pays 0.2%
$8,333,333 to $12,500,000 pays 0.3%
over $12,500,000 pays 0.4%

Problem I'm having is the months that he is over the previous cap you would calculate the difference between the cap and the amount over the cap at different rates. Say his commissionable sales in March are $1.5 million and that puts his cummulative at $5,007,000, his bonus should be $840,333 * 0.002 (the amount over $4,166,667) and 659,667 * 0.001 (the amount that was under $4,166,667) for a total commission of $2,340.33.

Of course, I could do all this manually but I'm trying to automate the process to avoid errors in the future.

 

[shg]

       --A-- ------B------ ---C----
   1             Sales      Comm%  
   2                               
   3                 $-        0.1%
   4           $4,166,667      0.2%
   5           $8,333,333      0.3%
   6          $12,500,000      0.4%
   7                               
   8   Month     Sales       Comm  
   9   Jan     $2,000,000   $2,000 
  10   Feb     $2,000,000   $2,000 
  11   Mar     $2,000,000   $3,833 
  12   Apr     $2,000,000   $4,000 
  13   May     $2,000,000   $5,667 
  14   Jun     $2,000,000   $6,000 
  15   Jul     $2,000,000   $7,500 
  16   Aug     $2,000,000   $8,000 
  17   Sep     $2,000,000   $8,000

The formula in C9 and copied down is

=SUMPRODUCT((SUM(B$8:B9) > $B$3:$B$6) * (SUM(B$8:B9) - $B$3:$B$6) * ($C$3:$C$6 - $C$2:$C$5)) - SUM(C$8:C8)