Most small Nepali businesses pay for HR or payroll software they only half-use — because "everyone else does." For the first 20–30 employees, a properly-designed Excel workbook covers everything the Labour Act 2074 and the Income Tax Act 2058 require: employee master, attendance, leave, holiday calendar, roster/duty planner, and monthly payroll with SSF, EPF, CIT, TDS and the 10% female rebate. This guide gives you the full workbook design — six sheets, the formulas that make them work, and the workflow that keeps them honest month after month.
Key takeaways
- A six-sheet Excel workbook — Master, Attendance, Leave, Holidays, Roster, Payroll — replaces HR software for most SMEs.
- Use one date column as the primary key. Every leave day, roster slot and pay-period link back through the same date.
- Nepal's Labour Act 2074 sets the leave minima: ~18 days annual, 12 sick, 13 public holidays, 98 maternity.
- Payroll must handle the FY 2083/84 unified slab (1% / 10% / 20% / 27% / 29%), SSF vs non-SSF employees, and the 10% female rebate.
- Protect the workbook with sheet-level passwords and a change-log; keep the master file in a Shared Drive (see our Drive/OneDrive guide).
When Excel is enough (and when it isn't)
Excel handles staff management well up to a point. It struggles beyond it. Roughly:
- Excel wins for 1–30 headcount, no biometric device, monthly manual attendance sign-off, one payroll cycle.
- Excel starts to strain at 30–50 headcount, when self-service and mobile access matter.
- Excel loses above 50 headcount, or when biometric/RFID attendance, shift-swap workflows, or SSO-based employee portals become the norm.
Even in an HR-software world, the Excel discipline of "one master, one date key, one formula per calculation" is exactly what makes payroll auditable. Learn it here first.
The six-sheet workbook design
Sheet-to-sheet data flow
Six sheets, one direction of data flow. Payroll is a computed output — never data-entered directly.
1. Employee Master
The Master sheet is the primary key of the whole workbook. One row per employee, never edited during the month — only additions and status changes.
| Column | Purpose |
|---|---|
| Emp ID | Unique code (e.g., WL-001). Never reused. |
| Full Name | Legal name matching PAN records. |
| PAN | 9-digit PAN. Required for TDS certificate. |
| Gender | M / F / Other — drives female-rebate logic. |
| Marital Status | Single / Married — drives filing-status option. |
| Date of Joining | Drives leave accrual and gratuity. |
| Designation, Department | Text. |
| Basic Salary | Monthly. |
| Allowances (fixed) | House, transport, etc. — as monthly amounts. |
| SSF Registered? | Yes/No. Drives SST waiver and contribution rates. |
| EPF Membership No. | If EPF instead of SSF. |
| CIT Contribution | Voluntary monthly amount. |
| Life Insurance Premium (yearly) | Up to NPR 40,000 deductible. |
| Health Insurance Premium (yearly) | Up to NPR 20,000 deductible. |
| Filing Status | Individual / Couple — from FY 2083/84 this may be redundant if the unified slab is applied to everyone. |
| Bank / Account No. | For payroll transfer. |
| Status | Active / On Leave / Resigned / Terminated. |
| Date of Exit | Populated on resignation. |
Every other sheet uses Emp ID to look up back into Master via XLOOKUP. Never duplicate data.
2. Attendance
Design: rows are employees, columns are dates. Codes: P (Present), A (Absent unpaid), L (Leave), H (Holiday), W (Weekly off), WFH (Work from home). Data entered daily or captured from a punch export.
- Colour-conditional formatting: green for
P/WFH, yellow forL, red forA, grey forH/W. - Weekly summary column:
=COUNTIF(range,"P")+COUNTIF(range,"WFH"). - Present days for payroll:
=COUNTIFS(range,"P")+COUNTIFS(range,"WFH")+COUNTIFS(range,"L") // paid days - Unpaid absences:
=COUNTIF(range,"A"). This is what triggers a salary deduction. - Working days in the month:
=NETWORKDAYS.INTL(FirstOfMonth, EndOfMonth, "0000011", HolidayRange)— the "0000011" pattern makes Saturday the weekly off (adjust if Friday-Saturday weekend is your policy).
3. Leave Register
One row per leave application. Columns:
- Emp ID, Employee (auto), Type, From, To, Days, Reason, Approver, Status.
- Type = Annual / Sick / Casual / Home / Maternity / Paternity / Bereavement / Unpaid.
- Days:
=NETWORKDAYS.INTL(From, To, "0000011", Holidays). Do not count holidays as leave.
Balance calculation (per employee, per type):
=Opening + Accrual − IF([Applied]="Approved",[Days],0)
- Annual leave accrual: ~1.5 days/month (approximates the Labour Act's 1 day per 20 days worked; adjust to your policy).
- Sick leave: 12 days a year, non-carry-forward or carry-forward capped — your policy choice.
- Home leave / Casual: per your policy.
- Maternity: 98 days paid, per Labour Act 2074.
4. Public Holidays
A simple two-column sheet: Date and Holiday Name. Update once a year against the Government of Nepal's official calendar.
Nepal's Labour Act 2074 recognises 13 public holidays plus each employee's weekly off. Common holidays include:
- Dashain (typically 5 working days off around Vijaya Dashami)
- Tihar (2–3 working days)
- Chhath (in Tarai regions)
- Loshar (community-specific — Tamu, Sonam, Gyalpo)
- Eid (Fitr and Bakrid; community-specific)
- National Day / Democracy Day / Republic Day / Constitution Day / Labour Day
- New Year (Baisakh 1) and gender-specific holidays such as Rishi Panchami (women).
The Nepal Government publishes the year's exact holiday calendar every Chaitra/Baisakh. Load it into this sheet once a year — every other sheet will pick it up automatically.
5. Roster / Duty Planner
Roster is the forward-looking cousin of Attendance. Design: rows are dates, columns are shifts (or employees). Fill cells with the Emp ID assigned to that slot.
- For a 2-shift office (Day / Evening): two columns of assignments per date, plus an On-call column.
- Validation:
Data → Data Validation → Listfrom the Master Emp ID range, so every cell must resolve to a real employee. - Cross-check:
=COUNTIF(RosterRow, EmpID)flags if a single employee is assigned twice on the same day. - Overtime slot flag: any assignment beyond 8 hours per day should be tagged
OTfor later payroll pickup. - Coverage check per date:
=COUNTA(RosterRow)vs required headcount — highlights under-cover.
Publish the roster at least a week in advance. If your team includes shift-swap requests, capture them in a separate Swaps sheet with approver sign-off — do not overwrite the original roster.
6. Payroll — the monthly calculation
Payroll is a computed sheet, not a data-entry sheet. Every value comes from an upstream lookup or formula. One row per employee per month; add a Period column so a single sheet holds every month.
Core columns
| Column | Formula / source |
|---|---|
| Emp ID, Name | XLOOKUP from Master |
| Basic (monthly) | From Master |
| Allowances | From Master (+ any variable allowance) |
| Gross | Basic + Allowances |
| Present Days | From Attendance (P + WFH + Paid Leave) |
| Payable Days ratio | Present Days / Working Days in Month |
| Earned Gross | Gross × Payable Days ratio |
| OT hours | From Roster / OT log |
| OT amount | OT hours × (Basic / 208) × 1.5 |
| Total Earnings | Earned Gross + OT |
| SSF/EPF Deduction (Employee) | 11% × Basic (SSF) or 10% × Basic (EPF) |
| CIT Deduction | From Master (monthly) |
| Life Insurance (annualised) | Master annual ÷ 12 |
| Taxable Income (annualised) | (Total Earnings × 12) − annual deductions |
| Annual Tax | Slab formula (see below) |
| Female Rebate | 10% × Annual Tax if Female + Individual + only-employment |
| Monthly TDS | (Annual Tax − Rebate) ÷ 12 |
| Other deductions | Salary advance, loans, etc. |
| Net Payable | Total Earnings − SSF/EPF − CIT − TDS − Others |
Slab formula — FY 2083/84 unified schedule
The FY 2083/84 slab (see our salary slab post): 1% first 10 lakh, 10% next 5 lakh, 20% next 10 lakh, 27% next 15 lakh, 29% above 40 lakh. Using LET for readability:
=LET(t, TaxableIncome,
b1, MIN(t, 1000000),
b2, MIN(MAX(t-1000000,0), 500000),
b3, MIN(MAX(t-1500000,0), 1000000),
b4, MIN(MAX(t-2500000,0), 1500000),
b5, MAX(t-4000000,0),
b1*IF(IsSSF,0,0.01) + b2*0.1 + b3*0.2 + b4*0.27 + b5*0.29
)
SSF contributors have the 1% Social Security Tax on the first band waived; non-SSF pay 1% on the first NPR 10 lakh.
Worked example — Sita's monthly payslip
Sita is a Marketing Executive at a Kathmandu SME. Female. Individual filing. Enrolled in SSF. FY 2083/84.
- Basic: NPR 45,000/month
- Allowances: NPR 15,000/month
- Gross: NPR 60,000/month → annualised NPR 7,20,000
- SSF (11%): NPR 4,950/month → annual NPR 59,400
- Life insurance premium: NPR 25,000 per year
- Attendance: 22 P + 4 W + 1 H = 27 days; 0 unpaid absence in a 26-working-day month → full month paid.
Annualised taxable income: 7,20,000 − 59,400 − 25,000 = NPR 6,35,600.
Annual tax (SSF contributor, so 0% on first 10 lakh):
- 0% × 6,35,600 = NPR 0
Female rebate: 0 × 10% = 0.
Monthly TDS: NPR 0. Net take-home = 60,000 − 4,950 = NPR 55,050.
Under the old FY 2082/83 slab Sita would have paid ~NPR 8,500 a year — the FY 2083/84 unified 1% band up to 10 lakh eliminates it completely for SSF contributors below that ceiling.
Where the salary rupee goes — Sita's payslip
Sita's take-home under FY 2083/84 (SSF contributor). The unified 1% band up to NPR 10 lakh removes salary tax at this income level entirely.
Payroll is not "typing numbers into a sheet." Payroll is a calculation — every rupee traceable to a rule, every deduction to a statutory basis. Design it that way from day one.
Monthly workflow — 8 steps
- Day 1–25: Attendance sheet updated daily by a supervisor. Colour formatting flags problems in real time.
- Day 25: Freeze attendance. Any late corrections require a change-log entry.
- Day 26: Reconcile leaves — every
Lcode in Attendance must have a corresponding row in Leave Register (and vice versa). - Day 26: Pull OT from Roster / OT log. Verify against the 4-hour/day, 24-hour/week cap.
- Day 26: Add any variable earnings (project bonus, incentive) or deductions (advance recovery).
- Day 27: Compute payroll. Sanity-check: total gross vs previous month, headcount vs Master, TDS vs previous month.
- Day 27: Payslips generated (per-employee PDF export from a payslip template sheet). Approvals from HR head + Finance head.
- Day 28–30: Bank transfer file uploaded. SSF/EPF/CIT/TDS deposit challans prepared. TDS deposit due by 25th of the following Nepali month (Section 90, Income Tax Act).
Controls, security and audit trail
- Workbook stored in a Shared drive. Version history is your undo. See our Drive / OneDrive guide.
- Sheet protection: Payroll formulas locked; only variable-input cells unlocked. Password per sheet.
- Change log sheet: Date, Who, What changed, Why. Every mid-month attendance correction lands here.
- Two-person approval for payroll release. HR + Finance heads' initials on the paid-out sheet.
- Backup snapshots: Save-as at month-end into an "Archive" folder. Never edit historical months.
- PII handling: PAN and bank account are personal data — restrict edit access to HR + Finance; never share the workbook externally without redacting these columns.
Frequently asked questions
Do I need SSF, EPF or both?
The Social Security Fund (SSF) is the government's unified scheme covering retirement, medical, accident, dependent and maternity benefits, funded by 11% employee + 20% employer contribution on basic salary. Businesses that enrolled in SSF stopped contributing separately to EPF for those employees. Non-SSF businesses continue with EPF (10% + 10%) and typically add CIT as a voluntary retirement top-up.
Can I run bi-weekly or semi-monthly payroll from this workbook?
Yes. Split the Payroll sheet into two half-month cycles with the same schema; the annualised tax computation still runs on the full year, so TDS smoothing works.
How do I handle salary revisions mid-year?
Add an "Effective From" column on the Master sheet and use it as a date filter in the payroll lookup. Never overwrite the previous salary — history matters for statutory calculations and disputes.
What about final settlement on resignation?
Compute pro-rata leave encashment on unused annual leave; pay pending salary up to the last working day; issue TDS certificate (Form 25-Ka) within 15 days of fiscal-year-end. Provident/SSF withdrawal is handled by the fund separately.
Can this workbook produce a payslip PDF?
Yes — a dedicated Payslip template sheet with XLOOKUP-driven fields (Emp ID as the input), then File → Print → Save as PDF, or a small VBA/macro loop that saves one PDF per employee. For less than 30 staff this takes 3 minutes a month.
Getting your workbook built
Building this workbook for a 15-person business takes roughly a working day for someone who knows Excel well. If yours is a mid-sized practice with unusual pay structures — production incentive, sales commission, home-leave pay — the design has to be adjusted, but the six-sheet skeleton stays the same.