Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Using SUMPRODUCT() to calculate variable rates and commissions

Often commission schedules, tax rates, or schedules of discounts will have rates that vary depending on the income or transaction value.

Calculating total tax in one cell:

Say that the following marginal income tax rates applied:

        Taxable Income                Marginal
  Low Threshold    High Threshold     tax rate
   $     0             $ 6,000           0%
   $ 6,001             $20,000          17%
   $20,001             $50,000          30%
   $50,001             $60,000          42%
   $60,001             and over         47%

and you wanted to find the total tax for a net income. Note that only the amount between $6,001 and $20,000 (inclusive) is taxed at 17%. Only the amount between $20,001 and $50,000 is taxed at 30%.

One way to do it would be to use 4 different cells and calculate the tax owed for each marginal tax band, up to the income value (since the lowest band is taxed at 0%, we can ignore its contribution).

However, the SUMPRODUCT() function allows a little legerdemain to calculate the value in a single cell:

Assume that the taxable income is in cell A1. The total tax can be calculated using the formula

   =SUMPRODUCT(--(A1>{6000;20000;50000;60000}), (A1-{6000;20000;50000;60000}), {0.17;0.13;0.12;0.05})
					
    = $13,480

Note that the tax rates, in the last set of brackets, are differential rates - the amount above or below the previous rate.

  Marginal Rate        Difference       Differential rate
      17%              17% - 0%          17%, or 0.17
      30%              30% - 17%         13%, or 0.13
      42%              42% - 30%         12%, or 0.12
      47%              47% - 42%          5%, or 0.05                   

How it works...

Using tables to make things easier

This method is even easier using tables. Set up a table, say in J1:L5:

        J           K             L
1  Threshold   Marginal Rate  Diff. Rate    
2       6000       17%          = K2          which returns:    17%
3      20000       30%          = K3 - K2     which returns:    13%
4      50000       42%          = K4 - K3     which returns:    12%
5      60000       47%          = K5 - K4     which returns:     5%

Then the formula becomes:

     =SUMPRODUCT(--(A1>$J$2:$J$5), (A1-$J$2:$J$5), $L$2:$L$5)

Now you can change the marginal rate without having to change the formula.

If you name the ranges, say, threshold for J2:J5 and dRate for L2:L5, then the formula becomes

     =SUMPRODUCT(--(A1>threshold), (A1-threshold), dRate)

and adding another tax bracket is as simple as extending the list and redefining the names - no formulae need to be changed. If you use dynamic named ranges, you don't even need to redefine!

Another example: commissions

Say a salesperson had the following commission schedule:

              Sales                    Marginal
  Low Threshold    High Threshold   commission rate
   $        0       $  100,000          10%
   $  100,001       $1,000,000           8%
   $1,000,001       and over            12%   

We again calculate the differential rates,

       J                K               L
1   Threshold       Marginal Rate    Diff. rate
2         0              10%           = K2         which returns:  10%, or  0.10
3    100000               8%           = K3 - K2    which returns:  -2%, or -0.02
4   1000000              12%           = K4 - K3    which returns:   4%, or  0.04                   

then construct a SUMPRODUCT() formula that calculates the commissions in one step:

     =SUMPRODUCT(--(A1>$J$2:$J$4),(A1-$J$2:$J$4), $L$2:$L$4)

Note that unlike the tax example above, since the commission applies to all sales, we need to include the bottom of the lowest band.

For sales of $500,000, the formula returns $42,000:

    Sum(--{TRUE;TRUE;FALSE} * {500,000;400,000;0} * {0.10;-0.02;0.04}), or
    Sum({50,000;-80,00;0}), or
    $42,000

For sales of $2,500,000, the formula returns $262,000:

    Sum(--{TRUE;TRUE;TRUE} * {2,500,000;2,400,000;1,500,000} * {0.10;-0.02;0.04}), or
    Sum({250,000;-48,000;60,000}), or
    $262,000

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

How SUMPRODUCT() works with differential rates

Commission example