Payroll Ledger Template

About this template

Three sheets, one ledger

The workbook opens on ‘Dashboard’, with ‘Input data’ and ‘Usage guide’ behind it. The ledger is the ‘Input data’ sheet; the dashboard holds only formulas that read it. Sixteen columns run No. · Emp. No. · Name · Department, then Base pay · Overtime pay · Meal (tax-free) · Total payable, then the deductions — National Pension · Health insurance · Long-term care · Employment ins. · Income tax · Local income tax · Total deductions · Net payable. Eight sample employees sit in rows 2-9; the sheet takes 20 input rows.

Only four things are typed by hand

1. Emp. No., Name and Department (columns B, C, D). The department name is what Summary by department groups on, so spell it the same way every time.
2. Base pay (column E) and Overtime pay (column F). On an annual salary, divide the contracted year by 12; on an hourly rate, contracted hours × rate.
3. Meal (column G) is tax-free up to KRW 200,000 a month and stays out of the premium and income tax base. Pay more and the excess is taxable — move it into base pay or the arithmetic stops matching.
4. Income tax (column M). Look it up in the National Tax Service simplified withholding tax table (KR) by taxable amount and number of qualifying dependents.

How the formulas run

• Total payable =E2+F2+G2. Row 2 is 3,200,000 + 240,000 + 200,000 = 3,640,000.
• National Pension =ROUND((E2+F2)*0.045,-1) · Health insurance =ROUND((E2+F2)*0.03545,-1) · Employment ins. =ROUND((E2+F2)*0.009,-1), all on the taxable base with the meal allowance removed, to the nearest KRW 10.
• Long-term care is charged on the health insurance premium, not on pay: =ROUND(J2*0.1295,-1). The 12.95% multiplies column J, and that is the step most often got wrong.
• Local income tax =ROUND(M2*0.1,-1). Total deductions =SUM(I2:N2), Net payable =H2-O2. Row 2 lands on 416,840 deducted, 3,223,160 net.
• The metric cards are =COUNT(‘Input data’!$P$2:$P$21) and its siblings, reading column P only: 8 rows · total 27,477,160 · average 3,434,645 · max 4,467,600.

The rates change every year

4.5% · 3.545% · 12.95% · 0.9% are the employee’s share for the sample year; replace them inside the formulas before your first live run. The National Pension also caps and floors the standard monthly income, so for anyone above the ceiling type the capped premium straight over the formula in column I.

What the dashboard shows, and what it does not

Summary by department uses COUNTIF, SUMIF and AVERAGEIF for headcount, net payable total, average and share. The sample has two people per team, so every share reads 25% — Corporate Planning Team 7,690,760 · Development Team 7,410,770 · Sales Team 1 6,420,970 · Production Team 5,954,660. ‘Monthly payroll cost (KRW m)’ below it is different: the four value rows are typed in and only Total 130 and Average 32.5 are formulas. It is not wired to ‘Input data’, so refresh it at each month-end close.

What goes wrong

• Typing Total deductions or Net payable by hand — the formula is gone and the dashboard stops matching what was transferred.
• Entering values on an empty row. Below row 9 there are numbers but no formulas, so copy the last filled row and paste it in.
• Counting the meal allowance twice, in Total payable and again in base pay. The premiums come out too high.
• Storing the file wherever is convenient. It holds personal data; keep it in the folder your organization has designated for it. Korean labor law also requires a separate pay statement for each employee, and one row copied across carries the reasoning with it.