Tax rate calculation

[stefanaalten]

I'm having difficulty coming up with a calculation to work out income tax based on some simple tax bands. I can do it in several steps using different columns to store intermediary results but I would really like to stick the calculation in a single column. My data is in rows, one row per pay period, with columns for pay, tax, etc.

For example:

Earnings between : Tax rate
---------------------------
<=100 : 0% on the earnings below 100 (call this T1)
>100, <=1000 : 10% on the earnings between 100 & 1000 (call this T2)
>1000, <=5000 : 30% etc.
>5000, <=10000 : 40% etc.
>10000 : 50% etc.

So if earnings are, say, 2400, then it would be:
0% * 100 + 10% * 900 + 30% * 1400 + 40% * 0 + 50% * 0 = 510

I just break earnings into bands (in individual columns) and then apply the tax rate accordingly (in yet more columns) and sum the whole lot up at the end, i.e. T=T1+T2+...

Like this:

T2 = IF(AND(PAY>100,PAY<=1000),0.1*PAY,IF(PAY>1000,0.1*(1000-100),0))
and ditto for the other tax bands.

If I try to combine the calculations and stick into single cell I get a very long complicated entry with lots of IFs and brackets. Is there a smarter way of doing this? Array function possibly? I need to allow for changes in the tax bands and rates from one pay period to another.

Many thanks in advance!

Stefan

 

[Gerald Higgins]

See if this gives you what you need.

I've put the bands into a little table like this, in the range A1:B4
0...............0%
100............10%
1000..........30%

and so on.

I'm then specifying the target value (e.g. 2400) in cell C1.

=(MAX(C$1-A2,0)*(B2-B1))+(MAX(C$1-A3,0)*(B3-B2))+(MAX(C$1-A4,0)*(B4-B3))

This only deals with rates up to 5000, on row 4, but you should be able to extend it for however many bands you have.

You can then change the bands or the rates whenever you like.

 

[shg]

Another way:

      ---A--- -B-- ---------------------------C----------------------------
  1         0   0%                                                         
  2      100   10%                                                         
  3    1,000   30%                                                         
  4    5,000   40%                                                         
  5   10,000   50%                                                         
  6                                                                        
  7   Income  Tax                                                          
  8    7,000  2090 B8: =SUMPRODUCT((A8>A2:A5) * (A8-A2:A5) * (B2:B5-B1:B4))