Does anyone have a formula that I can use to calculate the gross pay from a net figure.
I want to put this into a spreadsheet to show different amounts of tax and NI based on different levels of weekly pay.
If you do not have such a formula does anyone know where I can get one?
Simon Lever
Related resources
Replies (7)
Please login or register to join the discussion.
There is a goal seeker spreadsheet that I set up
HERE
But that's not really what you want.
It should be feasible to build a user defined function that will do what you want, but would need some thought on my part later tonight if noone else has a quick answer
Thanks - but a bit of a problem!
Alan
Thank you and the spreadsheet looks very good.
However I have noticed a small problem. My client wishes to pay casual staff around £80 - £120 per week. This is right on the edge of the limits where tax and NI become payable.
Looking at you spreadsheet it does not seem to account for pay below the tax code.
If a tax code is put in of 489 and a weekly net salary of £90 is required it actually gives a gross salary of £89.53!
I am sure this is a simple matter to alter but thought you should be aware of it.
How about this as two possible user defined functions for Tax &
Very much E&OE. Not tested on all scenarios, but I think its about right. No liability accepted etc...
'code starts
'TAX FUNCTION
Function GetTax(PayPeriods As Byte, Salary As Double, _
CodeNo As Integer, CodeLtr As String, _
LRBand As Integer, LRpcnt As Single, _
BRBand As Integer, BRpcnt As Single, _
HRpcnt) As Double
Dim tSal As Double
Dim tCode As Integer
Dim Taxable As Long
Select Case PayPeriods 'expected to be 1 = Annual, 12 = monthly, 52 = weekly
Case 1
tSal = Salary
Case 12
tSal = Salary * 12
Case 52
tSal = Salary * 52
End Select
tCode = CodeNo * 10 + 9
If CodeLtr = "K" Then tCode = tCode
Taxable = CLng(tSal) - tCode
Select Case Taxable
Case Is < 1
GetTax = 0
Case 1 To LRBand
GetTax = Round(Taxable * LRpcnt, 2) / PayPeriods
Case LRBand + 1 To LRBand + BRBand
GetTax = Round(LRBand * LRpcnt, 2)
GetTax = (GetTax + Round((Taxable - LRBand) * BRpcnt, 2)) / PayPeriods
Case Is > LRBand + BRBand
GetTax = Round(LRBand * LRpcnt, 2)
GetTax = GetTax + Round(BRBand * BRpcnt, 2)
GetTax = (GetTax + Round((Taxable - LRBand - BRBand) * HRpcnt, 2)) / PayPeriods
End Select
End Function
'NIC FUNCTION
Function GetNIC(PayPeriods As Byte, Salary As Double, _
NICLEL As Integer, NICLR As Single, _
NICUEL As Integer, NICHR As Single) As Double
Dim tSal As Double
Dim Taxable As Long
Select Case PayPeriods
Case 1
tSal = Salary
Case 12
tSal = Salary * 12
Case 52
tSal = Salary * 52
End Select
Taxable = CLng(tSal) - NICLEL
Select Case Taxable
Case Is < 1
GetNIC = 0
Case 1 To NICUEL
GetNIC = Round(Taxable * NICLR, 2) / PayPeriods
Case Is > NICUEL
GetNIC = Round((NICUEL - NICLEL) * NICLR, 2)
GetNIC = (GetNIC + Round((Taxable - NICUEL) * NICHR, 2)) / PayPeriods
End Select
End Function
'code ends
You will need cells with all of the Tax & NIC bands & rates set out to refer to in the function.
Thanks again
Thanks Alan.
The fix seems to work (but it should be posted to c12 not c13).
As for your next posting - WOW - this is way beyond my excel knowledge. I do appreceiate the time and effort and hopefully someone else can profit from your work.
If it is OK with you I will send the amended spreadsheet to my client and she can pull off the figures she needs.
If you need to contact me direct my e-mail addess is: [email protected]
Thanks once again.
No probs
but like I say E&OE and remember that the sheet was put together as a proposed answer to an earlier post, has been tested in a number of cases, but not all (as indicated by the quirk you found yesterday)
So it does
Switch the sheet protection off - there's no password - and change the formula in c13 to =MAX(0,SUM(C10:C11))