# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-payroll-gross-to-net
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

> **On the numbers here** — Social Security at 6.2% and Medicare at 1.45% of the employee share are long-standing. The wage base cap, the additional Medicare threshold, and income tax withholding all change and vary by jurisdiction. Every one of them belongs in a named input cell, and the figures used below are illustrative. Verify against current published tables before running a real payroll.

Tool: [Try the take-home pay calculator](https://dothecalculation.com/calculators/take-home-paycheck-calculator) — Check a single cheque against the sheet, including the deduction ordering.

## Which deduction reduces which base

**The table that decides the whole layout**
| 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

**A $6,500 monthly gross**
| 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.

**Social Security with the cap**

```
=MIN(PayrollBase, MAX(0, WageBaseCap − YTDPayrollWages)) * 0.062
```
- YTDPayrollWages is the cumulative payroll tax base before this period.
- MAX(0, ...) stops a negative remaining allowance producing a refund.
- Once the cap is reached the formula returns zero, and take-home pay jumps for the rest of the year.

**Additional Medicare above the threshold**

```
=MAX(0, MIN(PayrollBase, YTDPayrollWages + PayrollBase − MedicareThreshold)) * 0.009
```
- Applies only to the portion of wages above the threshold, so the period in which it is crossed is split.
- It has no employer match, unlike the base 1.45%.
- The threshold is not indexed, so it catches more people each year.

> **Build the sheet as a year, not as a cheque** — One row per pay period with a running year-to-date column beside each base. Both the wage cap and the additional Medicare tier are cumulative, and any single-period sheet gets them wrong in the period where a threshold is crossed. Twenty-six rows cost nothing and remove a whole class of error.

## 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.

**True cost of the same employee, one month**
| 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.

Tool: [Try the labour burden rate calculator](https://dothecalculation.com/calculators/labor-burden-rate-calculator) — Convert 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-payroll-gross-to-net). Quote freely with attribution and a link to this page._