Variable tax,

[dennismckay]

I have been a frequent visitor of this site and always could find the solution for my Excel challenge by searching through old topics. However, the solution for the challenge I have now seems not to been handled yet.

I hope someone in the forums can help me with the following:

I have a file which contains the following rows (totally 30 years):
Calendar Year 2011 2012 2013 2014 2015...
Operating year 1 2 3 4 5....
Earnings before tax (0.782) (1.205) (0.247) 0.710 1.667 2.624...

I want to determine the income tax, based on the following policy:
1. If earnings before tax are =< 0, tax percentage is 0%.
2. If earnings before tax are > 0, tax percentage is 0% in the first 04 years STARTING FROM THE YEAR WHERE THE EARNINGS BEFORE TAX WERE >0, and 5% until year 11. The complete tax policy is shown below:

Beginning of year Income tax rate Ending of year
1 0% 4
5 5.0% 11
12 10% 15
16 28% 30

I have been unable to create a working formula for this, mainly because the tax policy applies from the first year there is earnings before tax.

Please advice a formula I could apply in this case. I can also share the spreadsheet involved for further clarification.

 

[Saagar]

Assuming the "Earnings before Tax" values appear in Row3 starting from Column B and on, you may extend and use this formula:
=CHOOSE(COUNTIF($B$3:B3,">0")+1,0,0,0,0,0,0.05,0.05........)

-The Red parameter above returns the number of years that the earnings have been positive + 1.
- The Blue parameter is the tax rate of "0%" when earnings are not yet positive.
- The four green parameters are the tax rates of "0%" for the first four years when the earnings are positive.
- The last two "0.05" parameters are tax rate of 5% for years 5 and 6 with positive earnings.

Expand the formula and add the tax rates for the remaining years as desired.

Put this formula in cell B4 and copy it to the rest of the cells in the row to get the desired tax rates.

Here, I am assuming earnings don't go zero or negative once they become positive. If that is not the case, this formula will not work.

Try it and let me know if it works.

 

[dennismckay]

Thank you it works, i noticed that CHOOSE only allows 30 arguments but that is sufficient in this case.

I am just amazed that this formula is so simple. I expected I would need to use VLOOKUP etc!

 

[Saagar]

If it allows only 30 arguments, then the formula works for only upto 28 years. Did you verify? We will need to improvise if you need it to work for up to 30 yrs.

 

[dennismckay]

Yes indeed, it should work for thirty years.

Now the formula looks like this:
=CHOOSE(COUNTIF($C$18:C18,">0")+1,0,0,0,0,0,0.05,0.05,0.05,0.05,0.05,0.1,0.1,0.1,0.1,0.1,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28,0.28)

It seems to work OK as long as the taxable income does not get above 0 in the first few years.

 

[SydneyGeek]

This doesn't have the 28 item limit but gives the same result:

=SUMPRODUCT((COUNTIF($C$18:C18,">0")>{4,11,15})*({0.05,0.05,0.18}))

Denis

 

[Saagar]

As you said, it's working for you because the first few years are negative. If the first years are positive it wont work.

Also the arguments don't match your tax policy. You have 0.05 rate only for years 5-9 not 5-11.

 

[dennismckay]

I have just applied the formula of SydneyGeek. It works very well!