Thread: Calculating Variable Tax rates

Pay spreadsheet 2.jpg

According to the IRS's website, I can figure my withholding tax based on the following:

If the adjusted gross (G21, H21, or I21) is over $51 but not over $198, the tax is a simple 10%.
If it's over $198 but not over $653, the tax is 15% + $14.70.
If it's over $653 but not over $1,533, the tax is 25% + $82.95.

So, the flat rate, J23, will either be $14.70 or $82.95 depending on the figure in G21, H21, or I21.

How can I say that?

I changed your thread title.....

Originally Posted by amx1165

If the adjusted gross (G21, H21, or I21) is over $51 but not over $198, the tax is a simple 10%.
If it's over $198 but not over $653, the tax is 15% + $14.70.
If it's over $653 but not over $1,533, the tax is 25% + $82.95.

I think that the 10% will only apply to anything over $51, so the tax on $100 for instance, will be $4.90, i.e. 10% of the amount over $51.

You don't actually need the flat rates because they simply equate to the taxation from the previous band, i.e. $14.70 just represents 10% of $198-$51 and $82.95 is $14.70+15%*($653-$198).

You can use a formula that will calculate the total tax in one go without using those flat rates, e.g.

=IF(G21="","",ROUND(G21*25%-MIN(G21,653)*(25%-15%)-MIN(G21,198)*(15%-10%)-MIN(G21,51)*10%,9))

or this formula will give the same result

=IF(G21="","",SUMPRODUCT(--(G21>{51,198,653}),G21-{51,198,653},{10,5,10})/100)

using the method outlined here

Note: You don't say what happens after 1533. My suggested formulas don't take account of that, they simply keep deducting 25% indefinitely. Presumably the rate rises again after that, or aren't you concerned with higher amounts?

Note2: it's generally easier to replace band thresholds and rates in formulas with cell references, [see link] so that you can easily adjust when these change.

Edit: If you do want to include the thresholds you can do that like this

=IF(G21="","",IF(G21>653,82.95+(G21-653)*25%,IF(G21>198,14.7+(G21-198)*15%,IF(G21>51,(G21-51)*10%,0))))

All three formulas should give the same results

Thanks much to whoever came up with such an appropriate title

I think the formula will be the same when I get to K23 - it's gonna be either 10%, 15% or 25% depending on what's in G21, H21, or I21. So (in my mind anyway) J23 and K23 will have to:

scan G21, H21, and I21 to see which cell contains a value; determine what the value is; J23 will display either $14.70 or $82.95; and K23 will calculate 10%, 15% or 25% of whatever's in G21, H21, or I21.

You see, if I drive between 1 and 1,700 miles, I get paid 28 cents per mile. If I drive between 1,701 and 2,599 miles, I get paid 30 cents per mile.
If I drive over 2600 miles, I get paid 34 cents per mile.

Depending on my input elsewhere in the spread, my gross pay shows up in G20, H20 or I20. Then, since my part of the insurance premium ($27) is a pretax deduction, cell G21, H21, or I21 subtracts that amount from the gross and display the value as adjusted gross in the appropriate cell.

And I want J23 and K23 to figure the taxes - the flat rate and the % respectively. The total income (only) tax is displayed in G23.

G24 displays the FICA portion - Social Security and Medicare (C24 and E24)
G25 figures my state taxes.

I'm sure I'll have problems/questions when I get to these portions of the spread, but for now I have to tackle one problem at a time

Edit: You beat me to the post daddylonglegs

...or aren't you concerned with higher amounts?

I wish!

As far as the calculations go, I'd like to stick to what I've got going so I can learn more of the basics. I'm a pretty simple minded female