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

[Puni]

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,