This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates 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()...