Tax calculator - I am really stuck!

[Jim Shaw]

Hi,

I am trying to make an excel version of this:

http://www.taxmoney.com/jobit/calcu...42000&taxcode=&hoursperweek=40&holidayweeks=2

I have a table for tax:

Level Percent
4615 (taxfree) 0%
1960 (band1) 10%
30500 (band2) 22%
1000000000 40%

Salary is in cell A2 (named Salary)

For the first 4615 of the salary, I pay 0% tax.
Up to the next 1960 after that I pay 10% tax.
For anything after that up to 30500 I pay 22% tax.
For anything after 30500 I pay 40% tax.

I also have another table for national insurance:

Level Percent
4628 0%
30940 11%
1000000000 1%

For the first 4628 of my salary I pay 0%.
Up to the next 30940 after that I pay 11%.
For anything after that I pay another 1%.

My attempts for the tax part look like this

=IF(Salary<=tax_free,0,IF(Salary<(band1+band2),(Salary-C2)*D3,IF(Salary<=band2,((Salary-(tax_free+band1))*0.22)+band1*0.1)))

I have a feeling I should add a third column to each table and use
arrays - but I can't work it out!

Can anybody help me?

Many thanks,

Jim

 

[Harry Bo]

Jim,

The best way to set this up is by creating a tax table, so that it can be used in future if any of the rates change:
A B C
1 $4,615.00 10% 10%
2 $6,575.00 22% 12% C2 = B2-B1
3 $30,500.00 40% 18% C3 = B3-B2
4
5
6 INC $50,000.00
7 TAX $13,259.50

Formula in B7 = =SUMPRODUCT(--(B6>$A$1:$A$3),(B6-$A$1:$A$3),$C$1:$C$3)

For NI just substitute the $ threshold in column B1:B3 and the % in C1:C3

Harry

 

[Harry Bo]

Oops!

For NI Column B $ should be Col A and Column C % should be Col B

Sorry

Harry

 

[Dick Kusleika]

Jim

You might find this instructive

http://www.dicks-blog.com/excel/2004/04/the_taxman_come.html

 

[AlanE]

Below is a user-defined function for 2003 federal taxes that you could modify & use.

Function FedTax03(AdjGrossIncome, Optional FilingStatus) As Double
'
' Calculates 2003 Federal Tax from Adjusted Gross Income
'
' Filing Status: 1 = Married Filing Jointly (default)
' Filing Status: 2 = Head of Household
' Filing Status: 3 = Unmarried Individuals
' Filing Status: 4 = Married Filing Separately

'set default to married filing jointly
If IsMissing(FilingStatus) Then FilingStatus = 1
If FilingStatus <= 1 Or FilingStatus > 4 Then FilingStatus = 1

If FilingStatus = 1 Then
Select Case AdjGrossIncome
Case Is < 14000
FTax = AdjGrossIncome * 0.1
Case 14000 To 56800
FTax = 1400 + (AdjGrossIncome - 14000) * 0.15
Case 56800 To 114650
FTax = 7820 + (AdjGrossIncome - 56800) * 0.25
Case 114650 To 174700
FTax = 22282.5 + (AdjGrossIncome - 114650) * 0.28
Case 174700 To 311950
FTax = 39096.5 + (AdjGrossIncome - 174700) * 0.33
Case Is > 311950
FTax = 84389 + (AdjGrossIncome - 311950) * 0.35
End Select
End If
If FilingStatus = 2 Then
Select Case AdjGrossIncome
Case Is < 10000
FTax = AdjGrossIncome * 0.1
Case 10000 To 37450
FTax = 1000 + (AdjGrossIncome - 1000) * 0.15
Case 37450 To 96700
FTax = 5117.5 + (AdjGrossIncome - 37450) * 0.27
Case 96700 To 156600
FTax = 21115 + (AdjGrossIncome - 96700) * 0.3
Case 156600 To 307050
FTax = 39085 + (AdjGrossIncome - 156600) * 0.35
Case Is > 307505
FTax = 91742.5 + (AdjGrossIncome - 307050) * 0.386
End Select
End If
If FilingStatus = 3 Then
Select Case AdjGrossIncome
Case Is < 7000
FTax = AdjGrossIncome * 0.1
Case 7000 To 28400
FTax = 700 + (AdjGrossIncome - 7000) * 0.15
Case 28400 To 68800
FTax = 3910 + (AdjGrossIncome - 28400) * 0.25
Case 68800 To 143500
FTax = 14010 + (AdjGrossIncome - 68800) * 0.28
Case 143500 To 311950
FTax = 34926 + (AdjGrossIncome - 143500) * 0.33
Case Is > 307050
FTax = 90514 + (AdjGrossIncome - 311950) * 0.35
End Select
End If
If FilingStatus = 4 Then
Select Case AdjGrossIncome
Case Is < 6000
FTax = AdjGrossIncome * 0.1
Case 6000 To 23350
FTax = 600 + (AdjGrossIncome - 6000) * 0.15
Case 23350 To 56425
FTax = 3202.5 + (AdjGrossIncome - 23350) * 0.27
Case 56425 To 85975
FTax = 12132.75 + (AdjGrossIncome - 56425) * 0.3
Case 85975 To 153525
FTax = 20997.75 + (AdjGrossIncome - 85975) * 0.35
Case Is > 153525
FTax = 44640.25 + (AdjGrossIncome - 153525) * 0.386
End Select
End If

FedTax03 = FTax

End Function

 

[Jim Shaw]

You are a genius

Many, many thanks

Oops!

For NI Column B $ should be Col A and Column C % should be Col B

Sorry

Harry