#### Mac XL

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

## How SUMPRODUCT and differential rates work

Lets take a look at the variable rate tax formula

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

which applies to these marginal tax bands:

```        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%
```

The formula looks imposing, but it's actually not too complicated. Here's how we can break it down:

#### Evaulation of the first argument

The first argument,

`    --(A1>{6000;20000;50000;60000})`

creates a 4-element array that compares the top of each of the first 4 brackets to A1, returning TRUEs or FALSEs for each comparison. For example, if A1=15000, then the array would be evaluated as

`    {A1 > 6000; A1 > 20000; A1 > 50000; A1 > 60000}`

or

`   {TRUE;FALSE;FALSE;FALSE}`

Similarly, if A1=55000, the array returned would be {TRUE;TRUE;TRUE;FALSE}.

When a boolean value is used in a math function, XL coerces TRUEs to 1 and FALSEs to 0. The double unary-minus (-) operators are the fastest way to do the coercion. So

`     --{TRUE;FALSE;FALSE;FALSE}`

is first evaluated as

`     -{-1; -0; -0; -0}`

which then resolves to

`     {1;0;0;0}`

#### Evaluation of the second argument

The second argument,

`    (A1-{6000;20000;50000;60000})`

creates a 4-element array that subtracts the top of each of the first 4 brackets from A1, to return the amount in A1 above that bracket. If A1=15000, the array would be evaluated as

`    {15000 - 6000; 15000 - 20000; 15000 - 50000; 15000 - 60000}`

or

`    {9000;-5000;-35000;-45000}`

and if A1=55000, the array would be evaluated as

`    {55000 - 6000; 55000 - 20000; 55000 - 50000; 55000 - 60000}`

resulting in

`    {49000;35000;5000;-5000}.`

When these two arrays are multiplied within SUMPRODUCT(), the result is the amount to be taxed at the next highest rate. For A1=15000:

`    {1;0;0;0} * {9000;-5000;-35000;-45000}`

which is multiplied as

`    {1 * 9000; 0 * -5000; 0 * -35000; 0 * -45000}`

which results in the array

`    {9000;0;0;0}`

For A1=55000:

`    {1;1;1;0} * {49000;35000;5000;-5000}`

is multiplied as

`    {1 * 49000; 1 * 35000; 1 * 5000; 0 * -5000}`

which results in the array

`    {49000;35000;5000;0}`

#### Evaluation of the third argument

Taking the latter case, since the first two values are the total amounts above the 0% and 17% brackets, respectively, we can't just multiply \$49,000 by 17% and add \$35,000 times 30%, because \$35,000 of the \$49,000 would then be taxed at 17% + 30% or 47%.

The trick is to multiply each amount by the change in marginal tax rates. That way, all \$49,000 has the 17% applied, but only \$35,000 of that has the additional 13% applied to make a total of 30%. Extending that, the third argument is made up of

`    {17% - 0%; 30% - 17%; 42% - 30%; 47% - 42%} = {0.17;0.13;0.12;0.05}`

(when entered directly, the values must be entered in decimal form, not percentages). Note that if the marginal tax rate for a higher income bracket was lower than that of the previous bracket (!) the array value would be negative.

SUMPRODUCT() now completes the multiplication:

`   {49000;35000;5000;0} * {0.17;0.13;0.12;0.05}`

multiplies as

`   {49000 * 0.17; 35000 * 0.13; 5000 * 0.12; 0 * 0.05}`

resulting in the array

`   {8330;4550;600;0}`

and then sums them

`    8330 + 4550 + 600 + 0`

for a total tax of \$13,480.