Okay, here's the example spreadsheet. I chose FAI as an example facility with both COLA and CIP; I think I worked out the math right but if someone at a facility with either could check on Sheet 2: Pay that would be nice.
I made this in Numbers on a Mac, so the layout of the first sheet is a bit morphed in Excel. Also the formulas show the cell identifiers instead of the names, so if you're looking to edit them it might be a bit harder to figure it out.
- If you do get COLA and/or CIP, it's important that you enter basic pay and locality separately (they're based on basic pay, not base pay).
- Besides all the variables in the Variables section, edit the following in the Paychecksection:
- Actual yearly gross is less than predicted yearly gross because the hourly rate is based on 2087 hours per year, which is an average taking into account leap years and such.
- The Social Security Tax phase-out and Additional Medicare Tax phase-in are yearly values; I just used 1/26th of each value for this calculator (meaning it doesn't have memory and assumes the current paycheck is exactly the same as all the other paychecks for the year). It should be close enough, especially if you're well below or well above the lines.
- The state tax withholding is only accurate for my state. For any other state, you'll have to find your tax code and modify the values and possibly the logic. Or for a state with no income tax, like Alaska, just enter an insane amount of state withholding allowances.
Excel file attached, Numbers file download here via dropbox.
IllIlIllIllIllIIl HoneyBadgr (if you're looking at facilities with COLA/CIP, this version is corrected)
Didn't feel like tackling the updated W-4 all year, finally got around to it, updated other stuff too.
Version 2 contains the following improvements:
- Instructions sheet tells you what to enter under Variables (you're on your own to copy over your individual numbers for Deductions)
- You only have to enter your Base (Annual) Salary and Locality, no digging through SF-50s for your Basic Salary
- Overview, Earnings, Deductions sections split as on LES
- Earnings section split into line items as on LES
- Properly calculates overtime pay, taking into account FLSA premium (you actually get your base pay plus 50% of your average hourly pay for the pay period, including diffs, not just 150% of base pay)
- No longer have to update Regular Hours cell if you worked OT, keep it at 80
- Enter hours worked in HH:MM format, not decimal format (matches LES)
- Premiums/differentials now calculated by the hour, not in aggregate, based on rounded rate (matches LES)
- Includes Affordability Differential calculation, applicable to ACK and ASE
- TSP based on either percentage or flat amount per pay period
- Fed income tax withholding based on old or new W-4
- OASDI Cap and Additional Medicare Tax options, no longer based on estimated yearly earnings (calculations for individual pay periods will be more accurate)
Note that OASDI tax is calculated based on the proper percentage, 6.2%, and will not account for payback of deferred 2020 tax. You can manually add that to the Overview - Deductions line if desired.
The 2021 version features ASE as the example facility and Colorado income tax withholding. Calculation of other states' withholding is left as an exercise to the reader. Numbers (Mac) version download here, Excel version view here (download from File tab). Benefit of using Numbers file: calculations use cell names instead of just B3, K32, etc—makes it easier to see what I was doing.
Please let me know if there are errors, especially if you're at a CIP/COLA/aff-diff facility. The calculations are based on everything counting for FICA tax, COLA not counting for income tax, all three based on 80 hours a week no matter what, and all three counting toward FLSA premium. That all makes sense but it could easily be incorrect. When checking for errors in the tax withholding logic, remember it's using 2021 rates, not 2020—that tripped me up once. Should be pretty similar though.