Calculating weekly income tax on gross income using marginal (bracket) tax rates

Hi,

I am trying to create a formula that will allow me to calculate the income tax payable on a gross weekly salary as I need to know how much tax to hold back each week for the tax department. I have created a SUMPRODUCT (F1) that calculates the tax on total gross income using the marginal tax brackets (H3:J6), however, I need to try and work out what is owed each week I get paid. Using the example (D4:D11), week 1 and 2 are easily calculated (for example only) at 10.5%, however, week 3 crossed into the next bracket and a portion of the income ($4,000.00) is taxed at 10.5% while the remainder ($999.99) is taxed at the next bracket 17.5%, how could I represent this as a formula? I need the formula to carry through and capture the entire tax rates so weekly income is taxed at the correct bracket relative to the overall gross income.

Please feel to add columns/data as needed to assist with calculating the formula.. File attached.

Thanks,

Maybe this:

Formula:

Please Login or Register  to view this content.

Note: E3 must not be number

Hi Zbor,

Thanks for your quick response, the formula works well until you reach the $48,001-$70,000 @ 30% and higher brackets where it appears to over calculate the amount of Tax to be withheld. Looking at version 2 of the attached sheet, the formula suggest $19620.00 be held back for tax, while cell ref H2 suggest $15,670 be held back. I have checked H2 results using the tax department calulator and it is correct, tax calculator results below -

Income tax rate Income Tax
Income up to $14000 taxed at 10.5% $14,000.00 $1,470.00
Income over $14000 up to $48000 taxed at 17.5% $34,000.00 $5,950.00
Income over $48000 up to $70000 taxed at 30% $22,000.00 $6,600.00
Remaining income taxed at 33% $5,000.00 $1,650.00
Total $75,000.00 $15,670.00

Any thoughts on calculating the weekly tax when gross income crosses into the higher brackets?

Thanks,

So what would be correct result in F13 and F14?

Different approach.

Hi Zbor,

F13 =($3,000*17.5%)+($7,000*30%) = $2,625.00. As total gross income in D4:D12 = $45,000, a portion of the income earned in week 10 needs to be taxed at 17.5% up to the $48,000 threshold, therefore $3,000 * 17.5%. The remainder of the income earned in week 10 - $7,000 – pushes the total income into the next tax bracket and is taxed at $7,000*30%.

Similarly F14 = ($15,000*30%)+($5,000*33%). Gross income in D4:D13 now equals $55,000, therefore the first $15,000 earned in week 11 is taxed at 30% with the remainder of $5,000 taxed at the highest bracket of 33%.

I guess what I’m trying to achieve is an understanding of what the weekly tax deductions are relative to the overall gross income. As gross income increases through the tax bands, weekly income needs to be taxed accordingly. Using the example, week 12 onward would be a straight forward calculation since the total gross income is now in the highest tax threshold and income earned week would be taxed at 33%, however, need to know what the weekly tax deductions are through the entire tax table.

Hope this helps.

Thanks,

Hi AB33,

Many thanks, that looks like exactly what I need. Impressive formula, am not familiar with hurdle rates so I appreciate your assistance. Thanks,

Zbor, please feel to expand, otherwise I can mark the request complete.

Thanks to you both for sharing your time and knowledge.

Puni,
I used named range, so you can see what these names mean.
Hurdle is your From to TO
Mrate is the Differential % between two rates. In other words, the marginal increment as you go from one rate to next one.

Ah, understood, thanks!