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

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:

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}

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}

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.

This page last updated

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Back to Using SUMPRODUCT()...