The payroll register

A payroll register is the one sheet that shows a whole pay run: every employee on a row, every step from gross pay to net pay in a column, and a total at the bottom of each column. It is where the payroll is checked before anyone is paid, and where the numbers for the journal entry come from. This page builds a register for eight employees and shows how to prove it adds up.

What a payroll register shows

Read across a row and you follow one employee's pay from gross to net. Read down a column and you see one amount for the whole payroll. The register in the exercises has these columns:

• Hours and hourly rate: the inputs for gross pay.
• Pre-tax %: the share of gross pay the employee puts into a pre-tax deduction, such as a retirement contribution.
• Gross pay: what the employee earned, including any overtime.
• Pre-tax deduction: gross pay times the pre-tax %.
• Taxable wages: gross pay minus the pre-tax deduction. This is a subtotal, not a deduction.
• Withholding: income tax withheld, at an example rate on taxable wages.
• Social insurance: the employee's contribution, at an example rate on gross pay.
• Net pay: gross pay minus the pre-tax deduction, withholding and social insurance.

The example rate table sits beside the register, so every formula can refer to it: overtime after 40 hours a week at 1.5 times the hourly rate, withholding at an example rate of 12% on taxable wages, and social insurance at an example rate of 5% on gross pay.

Worked example: Bluebird Veterinary

Here is Bluebird Veterinary's register for one week, finished:

Following one row: D. Novak

D. Novak worked 50 hours at $22.80 an hour, so 10 hours are overtime.

1. Gross pay = 40 × $22.80 + 10 × $22.80 × 1.5 = $912.00 + $342.00 = $1,254.00.
2. Pre-tax deduction = $1,254.00 × 4% = $50.16.
3. Taxable wages = $1,254.00 − $50.16 = $1,203.84.
4. Withholding = $1,203.84 × 12% = $144.46, rounded to the cent.
5. Social insurance = $1,254.00 × 5% = $62.70. In this example it is charged on gross pay, so the pre-tax deduction doesn't lower it.
6. Net pay = $1,254.00 − $50.16 − $144.46 − $62.70 = $996.68.

Each step is one formula, written once in row 5 and filled down. The rates come from the example rate table through absolute references, so every row uses the same ones. For more on the gross pay step, see overtime pay calculation. For why the pre-tax deduction lowers withholding, see pre-tax vs after-tax deductions.

Building the register in a spreadsheet

A register is easy to build well and easy to build badly. A few habits keep it reliable from one pay run to the next:

• One row per employee, one column per step. Never combine two steps in a single cell you can't see. If taxable wages are worth knowing, give them a column.
• Inputs on the left, results on the right. Hours, rates and percentages come first, then each calculated column in the order the money flows. Anyone reading across a row can follow the paycheck.
• Rates in a table, never typed into formulas. When an example rate changes, you change one cell and every row updates. A rate typed inside a formula is easy to miss.
• Round each deduction to the cent. The amounts withheld are cents, not fractions of cents, and the totals must add up to what is actually paid over.
• The same formula down the whole column. If one row needs a different formula, something about that employee is different, and it should be visible in an input column, not hidden in an edited cell.

The second exercise asks you to build the whole register from hours, so these habits pay off straight away. Write each formula once in row 5, check it against a hand calculation for that employee, then fill it down to row 12.

Totals and cross-footing

The totals row is a SUM over every employee row. In the example, that is rows 5 to 12, all eight employees. The totals are the numbers the payroll journal entry uses: $10,546.00 of gross wages, and $381.45 + $1,219.74 + $527.30 = $2,128.49 withheld, leaving $8,417.51 of net pay to be paid out.

Accountants check a register in two directions:

• Footing means adding a column down. Each total in row 13 is a footing.
• Cross-footing means checking that a row adds across. Gross pay minus the pre-tax deduction, withholding and social insurance must equal net pay.

Cross-foot the totals row first, because it tests everything at once: $10,546.00 − $381.45 − $1,219.74 − $527.30 = $8,417.51. That matches the net pay total. If it hadn't, at least one row would be wrong, and cross-footing each row would find it.

Leave taxable wages out of the cross-foot. It is a subtotal (gross minus the pre-tax deduction), not a separate amount taken from pay. Including it would subtract the same money twice.

Finding a keyed error

The third exercise gives you a finished register for Juniper Landscaping in which one row was keyed wrong. The totals row doesn't cross-foot: $8,578.50 − $344.80 − $988.04 − $514.70 = $6,730.96, but the net pay column totals $6,699.61. The register is $31.35 out.

To find the row, add a check column. For each row, work out gross minus the three deductions minus net pay, round to the cent, and show "OK" if it is zero and "Check" if it isn't:

=IF(ROUND(E5-F5-H5-I5-J5,2)=0,"OK","Check")

Seven rows show OK. D. Novak's row shows Check: $720.00 − $36.00 − $82.08 − $43.20 = $558.72, but the net pay keyed is $527.37. The difference is $31.35, exactly the amount the totals were out. One wrong row explains the whole gap, so the register can be corrected and paid.

The ROUND matters. Spreadsheets store numbers in binary, so a row that is right to the cent can leave a tiny leftover such as 0.0000000001 after the subtraction. Rounding the difference to two decimals before comparing stops those leftovers from showing up as false errors.

From the register to the books

The register's totals become the payroll journal entry: debit Wages Expense for total gross pay, credit the payables for each kind of amount withheld, and credit Cash for total net pay. The employer's own taxes are worked out from the same gross pay column; see employer taxes at example rates. The entries themselves are in payroll journal entries.

Common mistakes

The exercises check for each of these and tell you which one you made.

Charging social insurance on taxable wages

For D. Novak, $1,203.84 × 5% = $60.19 instead of $62.70. In this example, social insurance is charged on gross pay. The pre-tax deduction only lowers income tax withholding.

Leaving the pre-tax deduction in net pay

Gross minus withholding minus social insurance gives D. Novak $1,046.84. The pre-tax deduction still comes out of the employee's pay, just before tax, so net pay is $996.68.

Ignoring overtime

50 hours × $22.80 = $1,140.00 pays D. Novak's 10 overtime hours at the regular rate. Under the example rule, hours above 40 earn 1.5 times the rate, for $1,254.00.

Totals that miss the last employee

A SUM over rows 5 to 11 drops F. Larsen. Gross pay would total $9,906.00 instead of $10,546.00. Each total must cover all eight rows, 5 to 12. A quick test: the totals row should cross-foot, and the number of rows in your range should match the number of employees.

A row check that leaves out the pre-tax deduction

Checking gross minus withholding minus social insurance against net pay makes every employee with a pre-tax deduction look wrong. Net pay is gross minus the pre-tax deduction, withholding and social insurance, so all three belong in the check.