Spreadsheet Finds Gross Pay
& Taxes
From Net Pay
By Analog Services, Inc.
If you are a one-person company or small company, here is a spreadsheet that might be useful. You input net pay and it calculates gross pay, federal income tax, state income tax, social security, and medicare. You can download it by clicking here.
Taxes, etc. are based on gross pay. After the taxes are removed, then whatever is left is your net pay. But, suppose you do things unconventionally and write yourself a check for the net pay? Then you need to reverse the calculations and determine the gross pay that would have produced your net pay. This isn't so bad if you keep the net pay the same every month. But what if it changes from month to month? This is exactly the kind of situation that the spreadsheet covers.
All you do is enter the net pay for each month and it determines you gross pay and taxes for that month. It also keeps year-to-date totals of everything. Each time you enter a new net pay it finds:
1. Your gross pay.
2. Federal Income Tax.
3. State Income Tax.
4. Your Social Security payment.
5. Company's matching Social Security payment.
6. Your Medicare payment.
7. Company's matching Medicare payment.
8. Amount of Federal check to be written.
9. Amount of State check to be written.
The spreadsheet is done in Microsoft Excel [1] and is illustrated below.
To use the spreadsheet you enter Net Pay in cells B6 through B17. At the start of the year you would normally set all of these values to zero. Cells A22, D22, and E22 must also be set at the start of the year. A22 is the Social Security cap for the year. You do not pay Social Security for any Gross Pay above this amount. Cell D22 is the Federal tax rate expressed as a fraction. Cell E22 is the State tax rate. Cells F22 and G22 have similar fractions for the Social Security and Medicare rates. These have remained at 6.2% and 1.45% for a number of years and probably do not need to be changed. Obviously, you can also make changes in rows 1 through 3. These are just text. All the rest of the cells, including those in hidden columns K through P must not be altered.
For the example shown, a number of things are worth noting. In January Sven Larson earned Net Pay of $8000. When this was entered into cell B6 the program calculated the values in B6 through J6. We see, as expected, that B6, D6, E6, F6, and H6 add up to the Gross Pay in C6. We also see that values in D6, E6, F6, and H6 are the correct percentages of the Gross Pay in C6. The company's matching Social Security and Medicare amount is given in G6. It is just the sum of E6 and F6. The amount of the check to the IRS (ouch!) is given in I6 and is just the sum of D6, E6, F6, and G6. The amount of the check to the State is given in J6 and is just a repeat of H6. During October Sven reached the Social Security cap and his Social Security amount went down from the previous month and was zero in November and December.
Note that if you pay yourself more often than once a month, the sum of the payments during the month is the Net Pay. For more than one employee, make new a new "sheet" for each one.
Disclaimer
Analog Services, Inc. does not warrant that either the above information or the spreadsheet described are correct or useful. Talk to your accountant if you have any questions regarding income tax withholding, etc.
References
1. Microsoft Corporation. Redmond, WA.
For more information on how Analog Services, Inc. can help solve your circuit/system problems, call or e-mail us today.
E-Mail: stevea@analogservices.com