Payroll Template Excel - Gross to Net with Pay Stub

About this template

Running payroll by hand comes down to the same arithmetic every pay period: hours times rate plus overtime gives gross pay; pre-tax deductions come off; Social Security, Medicare, federal and state income tax are withheld; what is left is the net pay on the check. This workbook lays that out as a payroll register, one employee per row, so the whole period's payroll and the employer's share of FICA are on one sheet.

It deliberately contains no tax tables. Federal income tax withholding depends on each employee's Form W-4 and the IRS's Publication 15-T for the year; state income tax ranges from none at all to several percent and some cities add their own; even Social Security has an annual wage cap. So every rate is a cell you fill in: the Social Security and Medicare rates sit in the rate block at the top (pre-filled with the employee rates of 6.2% and 1.45% - check them for your year), each employee has a column for the federal amount withheld and their state rate, and nothing is hidden inside a formula. It is a calculator, not tax advice.

The register: company and pay period in rows 3 to 6, the rate block to the right, and the employee table from row 10 - Employee, Pay type, Rate or salary, Regular hours, Overtime hours, Gross pay, 401(k), Health (Sec. 125), Federal income tax, Social Security, Medicare, State tax rate, State tax, Other deductions and Net pay. A totals row and the employer's FICA match follow.

The order of operations follows how US payroll treats the common deductions: a traditional 401(k) deferral reduces wages for income tax but not for Social Security and Medicare, while Section 125 health premiums reduce both. The Pay Stub sheet looks up one employee and prints a stub with earnings, each deduction, net pay and a check block.

How to use it

1. Set the pay period and check the rates - Company name, period start and end and the pay date go in B3 to B6. In the rate block, confirm the Social Security and Medicare rates and the overtime multiplier (1.5 is the FLSA time-and-a-half for non-exempt employees over 40 hours a week).
2. Add each employee - Name and pay type: Hourly employees need the hourly rate and hours, Salary employees the salary for this pay period (an annual salary divided by 26 for biweekly, for example).
3. Enter deductions and withholding - 401(k) and health premium amounts per period, the federal income tax to withhold (worked out from the employee's W-4 with IRS Publication 15-T or your payroll provider's calculator), their state tax rate and any other after-tax deductions.
4. Check the net pay and totals - Net pay is gross minus every deduction and tax. The totals row gives the period's payroll; the employer section adds the employer's matching Social Security and Medicare to show the full cost.
5. Print pay stubs - On the Pay Stub sheet, choose an employee from the drop-down in B6. The stub fills in from the register; print it or save it as PDF, then pick the next name.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(A11="","",IF(B11="Salary",C11,ROUND(C11*D11+C11*E11*$I$5,2))) - Gross pay: salary employees get the period salary; hourly employees get rate times regular hours plus rate times overtime hours times the overtime multiplier in I5.
• =IF(A11="","",ROUND((F11-N(H11))*$I$3,2)) - Employee Social Security: gross pay less Section 125 health premiums, times the rate in I3. Medicare is the same calculation with the rate in I4. A 401(k) deferral is not subtracted here because it is still subject to FICA.
• =IF(A11="","",ROUND((F11-N(G11)-N(H11))*N(L11),2)) - State income tax: gross less 401(k) and health deductions, times the employee's state rate in column L. Some states treat 401(k) deferrals differently - adjust if yours does.
• =IF(A11="","",F11-N(G11)-N(H11)-N(I11)-J11-K11-M11-N(N11)) - Net pay: gross pay minus 401(k), health, federal income tax, Social Security, Medicare, state tax and other deductions.
• =SUM(F11:F20) - The totals row adds each money column for the period; gross pay is shown here.
• =F21+J21+K21 - Total employer cost: gross payroll plus the employer's matching Social Security and Medicare, which equal the employees' shares.
• =IFERROR(MATCH(B6,Payroll!$A$11:$A$20,0),"") - On the Pay Stub sheet: finds which register row holds the chosen employee.
• =IFERROR(INDEX(Payroll!$F$11:$F$20,$E$6),0) - Pay stub lines read the register with INDEX at that row - gross pay here, and each deduction the same way.

Customising it

• Cap Social Security at the annual wage base by adding a year-to-date wages column and limiting the taxable wages to whatever is left under the cap.
• Add a local tax column (city or county income tax) beside the state tax with its own rate, and include it in the net pay formula.
• For a weekly payroll, change the period dates and keep everything else; the register does not assume a pay frequency.
• Keep one sheet per pay period by copying the Payroll sheet and naming it by pay date; a summary sheet can then add up year-to-date figures for W-2s.
• Employer-only taxes (FUTA, state unemployment) are not included - add rows under the employer section with your rates if you want the full cost.