Calculating Variable Tax rates

[amx1165]

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?

[shg]

Please read the Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

[amx1165]

I'm sorry, but I don't see the edit button on that post anymore. And again I apologize, but I'd be grateful if you'd change the title for me. I honestly don't know how it should be reworded.

[daddylonglegs]

I changed your thread title.....

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

[amx1165]

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

[daddylonglegs]

and K23 will calculate 10%, 15% or 25% of whatever's in G21, H21, or I21.

Unfortunately I don't think its quite as simple as that....

If, for example the adjusted gross is $1000 then you don't take 25% of the whole $1000, just the amount over $653, i.e.

=(G21-653)*25%+82.95 = $169.70

so even if you have the correct flat rate in J23 you're going to have quite a complex formula, still, to calculate the total income tax in G23...

One way round that would be to add some more "intermediate" calculations in row 23, e.g. in J23 for the flat rate, assuming only 1 cell in G21:I21 will contain a value.

=LOOKUP(SUM(G21:I21),{0,198,653},{0,14.7,82.95})

in K23 for the correct threshold

=LOOKUP(SUM(G21:I21),{0,51,198,653})

and in L23 for the correct tax rate

=LOOKUP(SUM(G21:I21),{0,51,198,653},{0,0.1,0.15,0.25})

format this cell as percentage

Then G23, for the total income tax can be

=(SUM(G21:I21)-K23)*L23+J23

[amx1165]

Maybe I've bitten off more than I can chew for the moment. Thanks much daddylonglegs for your time and consideration. I appreciate it more than you know. When my brain stops smoking I'm going to dive back into this thing. It's so interesting and challenging, but it makes my hair hurt at this point.