Gross to Net Pay in Excel: Four Deductions, Four Different Tax Bases
A retirement deferral escapes income tax and not payroll tax. A health premium escapes both. Getting the order of subtraction right is the whole exercise, and it is why one gross figure produces four different taxable bases.
One Gross Figure, Four Taxable Bases
The hard part of a payroll sheet is not the percentages. It is that each tax has its own base, and the deductions do not all reduce every base. A retirement deferral is exempt from income tax withholding and fully subject to Social Security and Medicare. A health premium under a cafeteria plan is exempt from all three.
A sheet that subtracts everything and then applies every rate to the result will understate payroll tax on every cheque. This article builds the bases explicitly.
Try the take-home pay calculatorCheck a single cheque against the sheet, including the deduction ordering.Which deduction reduces which base
Swipe sideways to compare columns.
| Deduction | Reduces income tax base? | Reduces Social Security and Medicare base? |
|---|---|---|
| Traditional retirement deferral | Yes | No |
| Roth retirement contribution | No | No |
| Health, dental, vision under a cafeteria plan | Yes | Yes |
| Health savings account via payroll | Yes | Yes |
| Flexible spending account | Yes | Yes |
| Group life over the exempt amount | Adds to base | Adds to base |
| Garnishment or union dues | No | No |
Read the second and third columns as two separate subtraction chains. That is exactly how the sheet should be built: two base columns, not one.
The calculation, in order
Swipe sideways to compare columns.
| Line | Formula | Amount |
|---|---|---|
| Gross pay | input | $6,500.00 |
| Retirement deferral at 6% | =Gross*DeferralPct | −$390.00 |
| Health premium, cafeteria plan | input | −$285.00 |
| Income tax base | =Gross−Deferral−Health | $5,825.00 |
| Payroll tax base | =Gross−Health | $6,215.00 |
| Social Security | capped, see below | −$385.33 |
| Medicare at 1.45% | =PayrollBase*0.0145 | −$90.12 |
| Federal withholding, illustrative 12% | =IncomeBase*FedRate | −$699.00 |
| State withholding at 4.5% | =IncomeBase*StateRate | −$262.13 |
| Post-tax life insurance | input | −$45.00 |
| Net pay | =Gross−sum of deductions | $4,343.42 |
Net is 66.8% of gross. Note that the payroll tax base is $390 higher than the income tax base, purely because the retirement deferral does not escape Social Security and Medicare. Applying both taxes to the lower base would understate them by $29.84 a month, or $358 a year, per employee.
The wage base cap, which needs a year-to-date column
Social Security stops at an annual wage cap. That makes it the one deduction that cannot be computed from the current period alone; the sheet has to know what has already been taxed this year.
The employer side
Employer cost is not gross pay. Add the matched payroll taxes, unemployment insurance, the retirement match, and the employer share of insurance.
Swipe sideways to compare columns.
| Component | Amount |
|---|---|
| Gross pay | $6,500.00 |
| Employer Social Security and Medicare | $475.45 |
| Unemployment insurance, illustrative | $42.00 |
| Retirement match at 4% | $260.00 |
| Employer health share | $620.00 |
| Total employer cost | $7,897.45 |
| Loaded cost as a multiple of gross | 1.215 |
A loaded multiple of about 1.2 is typical for a salaried employee with modest benefits, and it climbs above 1.35 with richer insurance. That number belongs in any pricing or hiring model, and it is the figure the payroll sheet exists to produce.
Try the labour burden rate calculatorConvert a salary into a fully loaded hourly cost including taxes, benefits, and paid time off.The five errors that show up in payroll sheets
- One taxable base for everything. Retirement deferrals reduce the income tax base and not the payroll tax base, and treating them the same understates payroll tax all year.
- No year-to-date column, so the wage base cap never triggers and Social Security is overcharged for high earners late in the year.
- Annual rates applied to a monthly figure without dividing, or a monthly figure compared against an annual threshold.
- A flat percentage for income tax withholding. Real withholding uses tables with allowances and a filing status, and a flat rate is only ever an approximation. Label it as one.
- Rounding at every line rather than once. Round each deduction to cents, then compute net as gross minus the sum, so the payslip adds up to the cent.
What the sheet cannot be
- A substitute for payroll software or a payroll provider. Filing deadlines, deposit schedules, year-end forms, and multi-state rules are where the real compliance burden sits, and none of it is in a spreadsheet.
- Correct across jurisdictions. Local income taxes, state disability programmes, and paid family leave levies all have their own bases and rates.
- Correct for non-standard workers. Contractors, tipped employees, and commissioned staff each have their own rules, and applying the salaried model to them is a substantive error.
- A withholding table. Real income tax withholding depends on a filing status, dependents, and elected extra withholding, and it is not a percentage.
- Current. Caps, thresholds, and rates change annually. Put a version and effective date on the rate block.
- A source of truth for benefits eligibility, accruals, or leave balances, all of which interact with pay and none of which this models.
Do retirement contributions reduce Social Security and Medicare taxes?
Traditional pre-tax deferrals do not. They reduce the income tax base only. This surprises people and it is the most common error in a homemade payroll sheet, because the deduction appears above the tax lines and looks as though it should reduce everything below it.
Why did my take-home pay increase late in the year?
You most likely crossed the Social Security wage base cap. Once cumulative wages exceed it, that 6.2% stops for the rest of the calendar year and restarts in January. Only a sheet with a year-to-date column reproduces this.
In what order should deductions be subtracted?
Cafeteria plan deductions first, since they reduce every base. Then retirement deferrals, which reduce only the income tax base. Then compute each tax on its own base. Post-tax deductions come out last and reduce no base at all.
How do I model an employee who crosses the wage cap mid-period?
The MIN and MAX formula above handles it automatically. The taxable amount for that period is whatever allowance remains, which is less than the full period wage. This is precisely why the calculation needs the year-to-date figure rather than the period figure.
What loaded cost multiple should I use for pricing?
Compute it rather than assume it. On this example it is 1.215. It moves with benefit generosity, the retirement match, and unemployment insurance experience rating. Anything between 1.15 and 1.40 is common for a salaried role, and using a guess in a pricing model is a large error to carry.
Can I use this sheet to run actual payroll?
Use it to check, model, and budget. Running actual payroll means withholding tables, deposit schedules, filings, and year-end reporting, all of which have deadlines and penalties attached. The arithmetic here is the small part of that job.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team