Using Excel to calculate income tax

E

ExcelBeginner34

New Member
Joined
Mar 2, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am trying to use Excel to calculate income tax. The tax system has a number of rates applied to each band as follows

Lower Limit (H22) Upper Limited (I22) Rate (J22)
Tax Free Allowance (G23) £0 £12,500 0%
Starter Rate (G24) £12,500 £14,549 19%
Basic Rate (G25) £14,549 £24,999 20%
Intermediate Rate (G26) £24,999 £43,430 21%
Higher Rate (G27) £43,430 £150,000 41%
Top Rate (G28) £150,000 £250,000 46%

So if the Salary was £40,000 before tax:

  • The first £12,500 is tax free
  • The next £2,049 (14,549 - 12,500) is taxed at 19%
  • The next £10,450 (24,999 - 14549) is taxed at 20%
  • and so on until you reach the total salary.

I am trying to calculate each bracket i.e £2,049*0.19 = 389.31 in a separate cell

  • Starter Rate E22
  • Basic Rate E23
  • Intermediate Rate E24
  • Higher Rate E25
  • I don't need the top rate as salary is unlikely to ever go above £150k.

The taxable income i.e gross salary less tax free allowance of £12,500 is currently in cell E20. So for example at £40k this would be £27,500.

I have tried using the IF function but I am just a beginner and not having much luck. Any help would be much appreciated.

Many thanks
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi,
maybe a UDF like the below can help you.

Code: 
Function IncomeTax(ByVal dblIncome As Double) As Double
    Dim Tax01 As Double
    Dim Tax02 As Double
    Dim Tax03 As Double
    Dim Tax04 As Double
    Dim Tax05 As Double
    
    Select Case dblIncome
        
        Case 0 To 12500
            IncomeTax = (dblIncome / 100) * 0
        Case 12500 To 14549
            Tax01 = dblIncome - 12500
            IncomeTax = (Tax01 / 100) * 19
        Case 14550 To 24999
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            IncomeTax = Tax01 + Tax02
        Case 25000 To 43430
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            IncomeTax = Tax01 + Tax02 + Tax03
        Case 43431 To 150000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04
        Case 150001 To 250000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            Tax05 = (dblIncome - 150000) / 100 * 46
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04 + Tax05
        Case Else
        
        End Select
        
End Function

you need to check if the rates are correct in the function but something down those lines could get you started.
I guess you only like the result rahter then all the necessary steps.

HTH
 
Upvote 0
silentwolf said:
Hi,
maybe a UDF like the below can help you.

Code: 
Function IncomeTax(ByVal dblIncome As Double) As Double
    Dim Tax01 As Double
    Dim Tax02 As Double
    Dim Tax03 As Double
    Dim Tax04 As Double
    Dim Tax05 As Double
    
    Select Case dblIncome
        
        Case 0 To 12500
            IncomeTax = (dblIncome / 100) * 0
        Case 12500 To 14549
            Tax01 = dblIncome - 12500
            IncomeTax = (Tax01 / 100) * 19
        Case 14550 To 24999
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            IncomeTax = Tax01 + Tax02
        Case 25000 To 43430
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            IncomeTax = Tax01 + Tax02 + Tax03
        Case 43431 To 150000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04
        Case 150001 To 250000
            Tax01 = (14549 - 12500) / 100 * 19
            Tax02 = (24999 - 14550) / 100 * 20
            Tax03 = (43430 - 25000) / 100 * 21
            Tax04 = (150000 - 43430) / 100 * 41
            Tax05 = (dblIncome - 150000) / 100 * 46
            IncomeTax = Tax01 + Tax02 + Tax03 + Tax04 + Tax05
        Case Else
        
        End Select
        
End Function

you need to check if the rates are correct in the function but something down those lines could get you started.
I guess you only like the result rahter then all the necessary steps.


Thanks I'll give this a try.
Click to expand...
 
Upvote 0
[TABLE="width: 1038"]
<colgroup><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD]The first £12,500 is tax free[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Basic Rate (G25) £14,549 £24,999 20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The next £2,049 (14,549 - 12,500) is taxed at 19%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Intermediate Rate (G26) £24,999 £43,430 21%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The next £10,450 (24,999 - 14549) is taxed at 20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Higher Rate (G27) £43,430 £150,000 41%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]and so on until you reach the total salary.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Top Rate (G28) £150,000 £250,000 46%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]M Y T A B L E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]max[/TD]
[TD]cum max[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-17500[/TD]
[TD="align: right"]12500[/TD]
[TD="align: right"]12500[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-15451[/TD]
[TD="align: right"]2049[/TD]
[TD="align: right"]14549[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]389.31[/TD]
[TD="align: right"]389.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-5001[/TD]
[TD="align: right"]10450[/TD]
[TD="align: right"]24999[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2090[/TD]
[TD="align: right"]2479.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13430[/TD]
[TD="align: right"]18431[/TD]
[TD="align: right"]43430[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3870.51[/TD]
[TD="align: right"]6349.82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]106570[/TD]
[TD="align: right"]150000[/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43693.7[/TD]
[TD="align: right"]50043.52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]let salary be[/TD]
[TD="align: right"]30000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]current band tax[/TD]
[TD="align: right"]1050.21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3529.52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]obtained by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=VLOOKUP($D$22,mytable,5)+$J$22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Please check
- brackets
- rates
- calculation with "allowance"

A variety of solutions for your consideration


Excel 2010
ABCDEFGH
1Amount40,000.00BracketsRateRate_DiffIncome Tax
2Fee5,629.5000%0%00
35,629.5012,50019%19%389.50389.50
45,629.5014,55020%1%2,090.002,479.50
55,629.5025,00021%1%3,150.005,629.50
643,43041%20%0.005,629.50
7150,00046%5%0.00
1dd
Cell Formulas
RangeFormula
F2=E2-N(E1)
G3=MAX(0,MIN($B$1-D3,D4-D3)*E3)
H3=H2+G3
B2=SUMPRODUCT(--(B1>D2:D7),B1-D2:D7,F2:F7)
B3=SUMPRODUCT(--(B1>Brackets),B1-Brackets,Rate_Diff)
B4=(B1>D3)*(B1-D3)*F3+(B1>D4)*(B1-D4)*F4+(B1>D5)*(B1-D5)*F5+(B1>D6)*(B1-D6)*F6+(B1>D7)*(B1-D7)*F7
B5=Incometax(B1)
Named Ranges
NameRefers ToCells
Brackets='1dd'!$D$2:$D$7
Rate_Diff='1dd'!$F$2:$F$7
 
Upvote 0
Thanks - I have set the table out as shown, however, in B3 and B5 I am just getting #ref and #name . I think this is to do with the use of Brackets and Rate Diff in the formula. I have set up the defined names (I think!) but where do I put the 1 dd? When I set up the defined names it looked like excel was trying to get me to open a file but I am not sure what I am supposed to do here. Any thoughts? Sorry as the name suggests this is all very new to me.
Thanks
 
Last edited:
Upvote 0
Do you still have the UDF silentwolf provided in the file?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Rob_010101
  • Question Question
UK Tax Brackets - Formulas
Replies
1
Views
2K
etaf
etaf
S
  • Question Question
tax calculation
Replies
6
Views
1K
shamsu203
S
O
  • Question Question
Calculate Average Income Tax Rate Based On Net Income?
2
Replies
15
Views
3K
olimits7
O
K
  • Question Question
Formaul to calculate annual tax rate
Replies
4
Views
3K
brylemartin12
B
N
  • Question Question
UK Income Tax calculation problem
Replies
2
Views
870
Nosmas
N

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top