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.
Often commission schedules, tax rates, or schedules of discounts will have rates that vary depending on the income or transaction value.
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
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!
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
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.
