0

Here is the link that relates to my question:

https://docs.google.com/spreadsheets/d/1dnWuREv3Rf2EWyDQDnyHyZWZ8ItbyKt_EfxPQwWhTnA/edit?usp=sharing

Cell C13 is a Retirement Annuity contribution of my choosing.

Column E is taxable income at various marginal tax rates.

I can reduce my taxable income by the value in Cell C13.

Currently I am manually allocating the value in Cell C13 into Column H.

In this example I have manually inserted $88999 into H21 and then calculated H20.
I would like this somehow to be dynamic.

The allocated $89999 to H21 because I need to max out the amount in E21 before allocating to the next cell.

Changing Gross Income in Cell C8 dynamically populates Column E so this makes my problem all the more challenging.

In a nutshell, the only cells that should be editable are yellow. I would like Column H NOT to be yellow.

1
  • Hi Tedinoz, thanks for your guidance. I hope this is he correct place to post my follow up question:) Can you perhaps take a look at my revisions to show me where I'm going wrong. Here's the link: – docs.google.com/spreadsheets/d/…
    – Brian
    Mar 14 at 6:33

1 Answer 1

0

You want a formula to calculate the tax payable on any given value of taxable income.

Try this formula In Cell C11 (and then copy to Cell C14):

=VLOOKUP(C11,$A$19:$D$25,4,TRUE)+round((C11-(VLOOKUP(C11,$A$19:$D$25,1,1)-1))*(VLOOKUP(C11,$A$19:$E$25,5,TRUE)),0)

The elements of the formula are:

  • VLOOKUP(C11,$A$19:$D$25,4,TRUE) - base tax for the applicable threshold
  • (C11-(VLOOKUP(C11,$A$19:$D$25,1,1)-1)) - the taxable income that applies to the final threshold
  • VLOOKUP(C11,$A$19:$E$25,5,TRUE) - the marginal tax rate that applies to this threshold.

Changes required

To do this, you need to structure the tax rates along the following lines.

Tax Rates

taxrates

The key to this table is that the calculation of the "Base Tax" for the previous threshold, and the marginal tax rate for each threshold.


Tax Calculations

taxable income

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.