Thanks again, MDM. I do all my financial tracking in Google Sheets for the ease of cloud access from multiple devices, so I created my own payroll sheet based on your template. Got it figured out to where it works to within a few pennies of what my employer's statements show. It is a little more simple than yours, though, as I ignored the cap on wages subject to payroll tax (hopefully someday it will be relevant to me) and I just created a formula using the relevant wage bracket in the percentage method to calculate my withholding rather than an all-encompassing formula. Where does that =fed_withhold formula come from anyway? I was unable to replicate it in Sheets or Excel. Something you created or am I just not as adept with Excel?
Within a few pennies is great and so is simplicity - well done! Rest assured the first version we had was much simpler than the posted template. Years of changing income and working with others to help them understand their situation did add complexity.
The fed_withhold formula is something I did to force myself to learn simple Visual Basic. You can find the code in Excel 2013 under Developer>Visual Basic. Don't know enough about Sheets to know where (if anywhere) it stores VB code. The menu path in earlier versions of Excel is something like Tools>Macro>Visual Basic Editor. See the end of this note for the code itself.
It is certainly possible to do the calculation without creating a user-defined function in VB. E.g., see the tax calculations in the MMM
case study spreadsheet, where cell L30 in that spreadsheet has something like*
=VLOOKUP(L13,$P$4:$R$10,2,1)+(L13-VLOOKUP(L13,$P$4:$R$10,1,1))*VLOOKUP(L13,$P$4:$R$10,3,1)
There L13 is taxable income, and cells P4:R10 hold the tax brackets and rates.
*"something like" means the actual formula is more complex because it allows for single, married filing jointly, and head of household filing status.
Function Fed_withhold(salary)
brkt_7 = 39454
brkt_6 = 35008
brkt_5 = 19921
brkt_4 = 13317
brkt_3 = 6958
brkt_2 = 2254
brkt_1 = 717
rate_7 = 0.396
rate_6 = 0.35
rate_5 = 0.33
rate_4 = 0.28
rate_3 = 0.25
rate_2 = 0.15
rate_1 = 0.1
base_1 = 0
base_2 = base_1 + ((brkt_2 - brkt_1) * rate_1)
base_3 = base_2 + ((brkt_3 - brkt_2) * rate_2)
base_4 = base_3 + ((brkt_4 - brkt_3) * rate_3)
base_5 = base_4 + ((brkt_5 - brkt_4) * rate_4)
base_6 = base_5 + ((brkt_6 - brkt_5) * rate_5)
base_7 = base_6 + ((brkt_7 - brkt_6) * rate_6)
If salary > brkt_7 Then
Fed_withhold = Round(rate_7 * (salary - brkt_7) + base_7, 2)
ElseIf salary > brkt_6 Then
Fed_withhold = Round(rate_6 * (salary - brkt_6) + base_6, 2)
ElseIf salary > brkt_5 Then
Fed_withhold = Round(rate_5 * (salary - brkt_5) + base_5, 2)
ElseIf salary > brkt_4 Then
Fed_withhold = Round(rate_4 * (salary - brkt_4) + base_4, 2)
ElseIf salary > brkt_3 Then
Fed_withhold = Round(rate_3 * (salary - brkt_3) + base_3, 2)
ElseIf salary > brkt_2 Then
Fed_withhold = Round(rate_2 * (salary - brkt_2) + base_2, 2)
ElseIf salary > brkt_1 Then
Fed_withhold = Round(rate_1 * (salary - brkt_1) + base_1, 2)
Else
Fed_withhold = 0
End If
End Function