Inflating tax tables

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

See attached

tax_sample_excelforums.png

Maybe something like this in D4 copied down?

=(VLOOKUP(C4,$I$5:$K$9,3)+((ROWS(B$4:B4)-1)*2)/100)*C4-(VLOOKUP(C4,$I$5:$K$9,2)+((ROWS(B$4:B4)-1)*2)/100)

You have not provided any expected results for guidance ...

If both the income and all amounts in the tax table increase by 2% annually, then as a result the tax will also increase by 2% annually.
In that case try in D5: =D4*1.02 and copy down.

In your sample sheet in post #9 all amounts in the tax table increases 2% annually.
Also the income increases 2% annually.
As results the tax will also increase by 2% annually.

In your example you are calculating the tax of all future years 2024-2028 with the tax table of 2023
If you calculating the tax of the future years with the tax tables of the future years, then you will see that the tax also increases annually by 2%.
In that case, the tax for 2024 is 8,032.50. That is why I indicated that in the case of your example you can calculate the tax by increasing it annually by 2% and you do not need the tax tables of future years.

Originally Posted by martik777

Tax could decrease because the income thresholds in the table would increase. The income I used would go into the 20.5% bracket next year without the 2% indexing, with the indexing the income stays in the 15% bracket "forever"

The income you have used never gets into the higher tax bracket based on the tables you have provided.

You're right, assuming the tax income brackets and income both increase by the same %

I used a constant 2% in the example but this will not always be the case. There are other sources of income that can vary significantly

So you have over-simplified the requirement. Please share a workbook (not screenshot) containing ALL necessary computational data.

I am moving this to the correct forum section for a Google Sheets query.

This differs from POST #1. In Post #1 you wrote

all the incomes and constants for each future year assuming a increase of 2%

If the income increase can differ from the tax increase and all the amounts in the tax table increase is 2% a year and if you want to use 1 tax table, please try and copy down:

Formula:

Please Login or Register  to view this content.

In the attached example the income increase is 5% a year.
I also checked the formaula with using different tax tables. See attachment.

You are a genius!

I don't quite understand how this works. I did not know you could search (match) on the product of multiplying the array by 1.02^x? "MATCH(C5,POWER(1.02,B5-B$4)*$I$5:$I$9)"

I will study tomorrow when I'm more awake

Thanks very much!

Yes you can match on a new calculated array.

Glad to have helped.

Please considder adding reputation to any helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Thanks for the rep .