I would like a formula that works out the UK tax payments required on a certain sum.
The amount between 0 - 2,230 gets 10% deducted.
Then the amount between 2,231 - 34,600 gets 22% deducted.
Then the amount above 34,600 gets 40% deducted.
Any ideas how I could put that in one formula?
Cheers
Good afternoon Sepia
For easier change every time Uncle Gordon gets his briefcase out (oh no, it's Alistair Darling now isn't it) I would use a UDF. Just change the parameters in the UDF and all formulae would change with them. For the attached code, use the formulae :
=UKTax(A1) or =UKTax(40000)
HTH
Function UKTax(Amount As Long) Dim TaxAmount As Long TaxAmount = 0 If Amount <= 2230 Then TaxAmount = Amount * 0.1 Else TaxAmount = 2230 * 0.1 End If If Amount > 2230 And Amount <= 34600 Then TaxAmount = TaxAmount + (Amount - 2231) * 0.22 Else TaxAmount = TaxAmount + (34600 - 2231) * 0.22 End If If Amount > 34600 Then TaxAmount = TaxAmount + (Amount - 34600) * 0.4 End If UKTax = TaxAmount End Function
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
You'd normally have to also factor in a personal allowance of £5K+ on which no tax is paid but you could calculate what you suggested with this formula
=SUMPRODUCT(--(A1>{0,2230,34600}),A1-{0,2230,34600},{0.1,0.12,0.18})
using the method shown here
....or a slightly different way to get the same result
=A1*40%-MIN(A1,34600)*(40%-22%)-MIN(A1,2230)*(22%-10%)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Sepia 
Register To Reply
Bookmarks