IR348 Reconciliation Excel - Free Template

1. Enter the pay period end date, employee name and IRD or NZBN reference on the IR348_Reconciliation sheet. Then fill in the pay figures for gross earnings and each deduction column.
2. Compare the submitted PAYE amount with the payroll calculation for each employee. If the variance is not zero, check the pay run, any manual adjustment, and the filed IR348.
3. Use the Reconciled? field to mark rows that match. This gives you a quick list of anything still needing a fix before you move on.
4. Review the Summary_Dashboard sheet for totals by pay period. That is where you spot whether the issue is one employee, one deduction type, or the whole filing.
5. Keep the Lookups_And_Notes sheet up to date with payroll references, rate notes and any changes to setup. That way the next pay run starts with the right defaults.
6. Once the period is balanced, save the workbook with the filing date and keep it with your payroll records for your seven-year retention trail.

This workbook suits the person who actually closes the pay run: the office manager in a 12-person plumbing firm, the bookkeeper in a small Ltd company, or the sole trader who has added a first employee and now has to file payroll properly. If you pay staff fortnightly, monthly or on an off-cycle top-up, the gap usually shows up when the numbers are already in myIR and you need a tidy check.

The template follows the same rhythm as a real payroll job. On IR348_Reconciliation, image 1 shows columns for Pay Period End, employee name, NZBN / IRD ref, gross earnings, PAYE, employee and employer KiwiSaver, ESCT, ACC, student loan, child support, total deductions, submitted PAYE, variance, reconciled status and notes.

Where this helps most

Say you run payroll for 4 staff and one person had a $250 overtime adjustment. A small change like that can shift PAYE by $37.50 and KiwiSaver by another $7.50, so the filed schedule no longer ties to the payroll report. This sheet lets you see the mismatch immediately instead of hunting through bank payments and payslips.

What the dashboard gives you

Image 2, the Summary_Dashboard, is for the totals view. You can keep an eye on how much PAYE and deductions have been recorded for the period, which is useful when you are checking a $28,000 monthly payroll or confirming a final pay before a public holiday.

The design suits NZ payroll because the key fields are the ones you are actually asked to explain. You are not trying to manage a general ledger here; you are matching the schedule you filed against the payroll you paid.

Inland Revenue expects payroll records to be complete enough to support the return you filed, and you need to keep them for 7 years. That means the worksheet should show the pay period end, employee detail, the amounts deducted, and any variance that explains why the filed IR348 changed.

The main payroll rules behind the numbers are straightforward: PAYE is withheld from wages, employee KiwiSaver can be 3%, 4%, 6%, 8% or 10%, and the employer contribution is generally 3% plus ESCT on that employer contribution. ACC levies may also sit in the pay run, while student loan and child support deductions are separate statutory deductions.

Why the schedule has all those columns

The reason the template separates each deduction is practical. If gross pay is $6,250 and PAYE is $1,562.50, but your filed IR348 shows $1,540.00, you can see the $22.50 gap without opening three different reports. If the issue is KiwiSaver, you can test whether the employee contribution or the employer side was posted wrong.

For a small company with an NZBN, this workbook also gives you a clean support file if Inland Revenue queries a filing. If you are a sole trader with your first employee, it is a simple way to build a proper audit trail from day one rather than trying to reconstruct everything at year end.

There is no need to overcomplicate the structure. The point is to keep the payroll source data, the filed schedule and the explanatory notes together so you can prove what happened on the day the pay run was done.

The usual problem is not the big mistake; it is the small one that slips through. A $12.75 difference in PAYE on one employee might look harmless, but if you have 18 staff and the same coding error repeats for a month, you can be $229.50 out before you even add employer KiwiSaver or ACC.

Common failure points

One common issue is leaving a manual adjustment out of the IR348 but still paying it through the bank. Another is using the wrong deduction code for student loan or child support, which creates a mismatch between the payroll software and the filed return. A third is forgetting that a final pay with annual leave, holiday pay and a bonus can move the numbers enough to upset the whole period.

The cost is usually time first, then money. If you spend 45 minutes tracing a mismatch across the pay run, the bank file and the filed schedule, that is a real labour cost, and if you miss the filing window you may have to fix the return after the fact in myIR.

Why the notes column matters

The Notes column is there for the bits software never explains properly. Write down things like a one-off overtime correction, a reimbursed deduction, or a retrospective rate change so the next person does not have to guess why the figures moved.

In a real workshop, this is where the lightbulb goes on for payroll admins: the spreadsheet is not just a checker, it is the memory of the pay run. If you have 3 pay periods with similar errors, the notes help you see the pattern and stop paying to fix the same issue twice.

Those repeated corrections also point to the year-end paperwork, where an income tax return workbook captures the same adjustments for filing rather than payroll.

The best way to use this workbook is to tie it to the job you already do. If you run payroll on Friday, open the file straight after the bank file is sent, while the numbers are still fresh and before the email trail gets buried.

Simple habits that stick

• Copy the prior pay period and update only the new rows, so you are not rebuilding the sheet from scratch each time.
• Use the Reconciled? field as a hard stop: nothing is marked done until the variance is zero or explained.
• Keep the notes short and specific, such as “manual bonus included” or “student loan added after starter form”.
• Review the dashboard before you file so you can see whether the mismatch is one employee or the whole payroll.

That routine is usually enough for a small team. If you are running 30 or more employees, handling multiple cost centres, or reprocessing backdated leave every fortnight, you are probably past spreadsheet-only territory and should be looking at payroll software like Xero or MYOB.

The handover point is usually when the file starts taking longer than the pay run itself. At that point, the workbook can still be a check, but it should not be the engine.

At that point, the workbook can still be a check, but it should not be the engine; a tax code declaration tracker becomes the place to keep those starter-form changes aligned before the next pay run.