+ Reply to Thread
Results 1 to 7 of 7

Calculating Variable Tax rates

  1. #1
    Registered User
    Join Date
    06-28-2008
    Location
    Alabama
    Posts
    15

    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?
    Last edited by amx1165; 07-05-2008 at 09:45 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658
    Please read the Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

  3. #3
    Registered User
    Join Date
    06-28-2008
    Location
    Alabama
    Posts
    15
    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.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    I changed your thread title.....

    Quote 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
    Last edited by daddylonglegs; 07-06-2008 at 08:08 AM.

  5. #5
    Registered User
    Join Date
    06-28-2008
    Location
    Alabama
    Posts
    15
    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
    Last edited by amx1165; 07-06-2008 at 08:35 AM.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by amx1165
    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

  7. #7
    Registered User
    Join Date
    06-28-2008
    Location
    Alabama
    Posts
    15
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1