Employer payroll taxes in Excel

An employer's payroll cost doesn't stop at gross pay. On top of it come the employer's own payroll taxes, some charged on every dollar of pay and some only up to a yearly limit per employee. This page sets them up in a spreadsheet, stops a tax at its wage base, and checks a payroll for errors before anyone is paid.

What employer payroll taxes are

When a business runs payroll, two different kinds of amounts are involved. Amounts taken out of the employee's pay are covered on the payroll calculations in Excel page, which works from gross pay down to net pay. This page is about the other kind: taxes the employer pays out of its own pocket, on top of what it pays the employee.

Employer payroll taxes share a simple shape. Each is a rate times some measure of wages. They differ in which wages count:

• Taxes with no wage base apply to every dollar of gross pay. If someone earns $4,000 this period, the tax is $4,000 times the rate.
• Taxes with a wage base apply only to the first part of each employee's pay for the year. The wage base is that limit. Once an employee's wages for the year pass it, the rest of their pay for the year is not taxed for that item.

To know how much of this period's pay is under the wage base, you need what the employee has already earned this year, called year-to-date wages, often shortened to YTD.

Why this matters for MO-220 practice

The MO-220 exam objective "Perform routine accounting activities" includes payroll. Two of the skills listed there are calculating the employer's payroll tax liabilities and using functions to spot irregularities. This lesson practices both. Task instructions describe the purpose rather than naming the function. A task says "never more than what is left of the wage base", not "use MIN".

The exercises run in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example 1: Summit Bike Repair

Summit Bike Repair has eight employees on one pay period. The example rate table sits in I3:K6:

Employees are in rows 5 to 12: the ID in column A, the name in B and gross pay in D. The task is to work out example tax 1 in column E and example tax 2 in column F, rounded to the cent, then total both in row 13. Neither tax has a wage base, so each one is simply gross pay times the rate.

Step 1: one formula per column

In E5, multiply gross pay by the tax 1 rate and round:

=ROUND(D5*$J$4,2)

In F5, the same with the tax 2 rate:

=ROUND(D5*$J$5,2)

The dollar signs keep $J$4 fixed when you fill down to row 12, while D5 moves to D6, D7 and so on. For J. Silva, gross pay of $4,091.52 gives $245.49 for tax 1 (4,091.52 × 6%) and $61.37 for tax 2 (4,091.52 × 1.5%).

Step 2: fill down and total

The totals in row 13 are =SUM(E5:E12) and =SUM(F5:F12). Together the two example taxes add $2,521.96 to Summit's cost for the period, on top of $33,626.33 of gross pay.

Rounding once on the total gross pay would give $504.39 for tax 2; rounding each row first, then adding, gives $504.38. The exercise rounds each row. The rounding to the cent page explains why the order of rounding matters.

Worked example 2: a tax that stops at the wage base

The second exercise, for Copperleaf Studio, uses example tax 3: 0.80% on each employee's first $9,000 of wages in the year. Column C now holds each employee's wages earned earlier this year, and column D this period's gross pay. Column E should show the part of this period's pay that is taxable, and column F the tax.

Think it through in words first. What is left of the wage base is $9,000 minus wages already earned. This period's taxable wages are gross pay, but never more than what is left, and never less than zero. In a formula, that is a MIN inside a MAX:

=MAX(0,MIN(D5,$K$6-C5))

MIN picks the smaller of gross pay and what is left of the base. MAX then replaces a negative result with 0. The tax in F5 is =ROUND(E5*$J$6,2). Three of Copperleaf's employees show the three cases:

A. Rivera is well under the base, so all of this period's pay is taxable. C. Okafor crosses the base this period, so only $3,562.05 of the $4,508.93 is taxable. G. Mensah passed the base earlier in the year; what is left is negative, so MAX turns it into 0.

Filled down for all eight employees, taxable wages total $24,047.31 and example tax 3 totals $192.39.

Worked example 3: spotting payroll irregularities

Before payroll is paid, someone should check it. The third exercise gives Harbor Bakery's payroll with hours in column C, gross pay in D and the hourly rate in E, plus an hours limit of 60 in J1. Each row needs one status, checked in this order:

1. Duplicate ID if the employee ID appears more than once.
2. Otherwise Hours over limit if hours are above J1.
3. Otherwise Pay mismatch if gross pay is not hours times rate, to the cent.
4. Otherwise OK.

A list of tests where the first true one wins is what IFS does. COUNTIF counts how often the ID appears in the whole ID column. In F5:

=IFS(COUNTIF($A$5:$A$12,A5)>1,"Duplicate ID",C5>$J$1,"Hours over limit",ROUND(C5*E5,2)<>D5,"Pay mismatch",TRUE,"OK")

The last pair, TRUE,"OK", catches every row that passed the other tests. Filled down, the results are:

J. Silva and B. Chen share ID E436, so both rows are flagged. D. Novak's 38 hours at $23.88 should come to $907.44, not $963.57, a difference of $56.13. H. Ito worked 65 hours, 5 over the limit. F. Larsen worked exactly 60 hours and is paid exactly 60 × $36.70, so that row is fine. In F13, =COUNTIF(F5:F12,"<>OK") counts the rows that need attention: 4.

Common mistakes

These are the mistakes the exercises check for. The feedback names the one you made.

A rate reference that moves when filled down

If E5 is =ROUND(D5*J4,2) without dollar signs, row 6 points at J5 and row 7 at J6. B. Chen's tax 1 becomes $76.17 instead of $304.68, F. Larsen's $19.69 instead of $147.70, and from H. Ito down the rate cell is blank, so the tax shows 0. Lock it: $J$4.

Using the tax 1 rate for tax 2

Copying column E into column F without changing the rate makes both columns identical. Summit's tax 2 total would show $2,017.58 instead of $504.38. Each tax has its own row in the table.

Ignoring the wage base

Taxing every dollar of gross pay overcharges anyone near or past the base. G. Mensah would owe $26.84 instead of nothing, and C. Okafor $36.07 instead of $28.50. Copperleaf's tax 3 total would be $226.80 instead of $192.39.

Comparing the base with this period's pay only

=MIN(D5,$K$6) asks whether this one paycheck is above $9,000. None of them is, so every dollar looks taxable and you get the same wrong $226.80. The base covers the whole year, so subtract the wages already earned in column C first.

Flagging only the second copy of a duplicate

A running range such as COUNTIF($A$5:A5,A5) only counts the rows above. J. Silva's row comes first, so it looks fine, and only B. Chen's row is flagged. Both rows share the ID, and either could be the wrong one, so count across the whole column, $A$5:$A$12.

Treating hours at the limit as over it

Using >= flags F. Larsen, who worked exactly 60 hours. The rule is "above the limit", so the test is >. With the wrong sign, the count in F13 is 5 instead of 4.

Checking your work

Before you finish a sheet, work one row by hand and change a rate in the table to see the sheet update. To record the taxes, see payroll journal entries, which posts them as an expense and a liability. Absolute cell references explains the dollar signs used throughout.