Payroll Calculator in Excel with PF, ESI and Professional Tax: Employer Cost, Statutory Dues and Bank Transfer List

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: payroll-calculator-pf-esi-pt-system-template.xlsx: a 9-sheet system with example data and real formulas (Read me, Setup, Employees, Inputs, Payroll, Statutory dues, Bank transfer, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Bagale Engineering Works, October 2026 (made-up employees, real rates checked 17 October 2026).

For October the sheet shows: gross earned ₹1,72,913, net pay to the bank ₹1,56,088, employer cost ₹1,87,807, PF to deposit ₹24,666 (employee ₹12,333 + EPS ₹8,560 + EPF ₹3,773), ESI ₹3,153 for the 5 employees under ₹21,000, and PT ₹1,200 from 6 people. The pay components themselves are explained in salary calculation: basic, HRA, DA, PF; paid days come from the attendance system and slips from the salary slip generator.

2. Build it sheet by sheet

Step 1. Setup: month and rates with sources

1. On Setup, type the month (first day) in B4 and in B5 =DAY(EOMONTH(B4,0)) for the days: 31.
2. From row 10 type each rate in a yellow cell with its source in column C: PF ceiling ₹25,000 in B11, PF 12% in B12, EPS 8.33% in B13, ESI ceiling ₹21,000 in B14, ESI 0.75% in B15 and ESI employer 3.25% in B16.
3. Type the departments in E5:E8 for the drop-down on Employees (drop-down lists).

Step 2. Employees and Inputs

1. On Employees, the master: Emp ID, name, gender, designation, department, basic, HRA, conveyance, special allowance; J5 =SUM(F5:I5) for the monthly gross.
2. On Inputs, A5 =Employees!A5, B5 =XLOOKUP(A5,Employees!$A$5:$A$12,Employees!$B$5:$B$12), then type paid days (from attendance), incentive, advance recovery and TDS.

Step 3. Payroll: earned pay

1. On Payroll, D5 brings the monthly gross with XLOOKUP and E5 the paid days: =XLOOKUP(A5,Inputs!$A$5:$A$12,Inputs!$C$5:$C$12) (XLOOKUP).
2. Earned basic F5: =ROUND(XLOOKUP($A5,Employees!$A$5:$A$12,Employees!$F$5:$F$12)*E5/Setup!$B$5,0) (ROUND). G5 adds the other components the same way.
3. Gross earned I5 =F5+G5+H5. Shraddha Bagale with 29.5 paid days: ₹28,549.

Step 4. Payroll: deductions and employer share

1. PF wages J5 =MIN(F5,Setup!$B$11) and PF K5 =ROUND(J5*Setup!$B$12,0): Ravindra Bagale ₹24,000 × 12% = ₹2,880.
2. ESI L5 =IF(D5<=Setup!$B$14,ROUNDUP(I5*Setup!$B$15,0),0): zero for anyone whose monthly gross is above ₹21,000.
3. PT M5 uses the nested IF with gender and February (nested IF); net pay P5 =I5-SUM(K5:O5).
4. Employer side: EPS Q5 =ROUND(J5*Setup!$B$13,0), EPF R5 =K5-Q5, ESI employer S5 and employer cost T5 =I5+Q5+R5+S5. Ravindra Bagale: ₹42,880.

Step 5. Statutory dues and bank transfer

1. On Statutory dues, read the Payroll total row: C5 =Payroll!K13 (PF employee), C6 =Payroll!Q13 (EPS), C7 =Payroll!R13 (EPF), C8 =Payroll!L13, C9 =Payroll!S13, C10 =Payroll!M13.
2. Count people with COUNTIF: B8 =COUNTIF(Payroll!$D$5:$D$12,"<="&Setup!$B$14) gives 5 covered by ESI (COUNTIF).
3. Total PF C12 =SUM(C5:C7) = ₹24,666 and total ESI C13 =C8+C9 = ₹3,153.
4. On Bank transfer, list ID, name, masked account (=XLOOKUP(A5,Employees!$A$5:$A$12,Employees!$K$5:$K$12)) and net pay =Payroll!P5, with a check cell against the Payroll total.

Step 6. Report and Dashboard

1. On Report, by department: gross =SUMIFS(Payroll!$I$5:$I$12,Employees!$E$5:$E$12,A5) (SUMIFS), employer contributions, total cost and share of the company total =E5/Payroll!$T$13. Production: ₹75,082, 40%.
2. On Dashboard, KPI cards: gross A5 =Payroll!I13, net B5 =Payroll!P13, employer cost C5 =Payroll!T13, PF to deposit D5, ESI to deposit E5, ESI-covered employees F5 (KPI cards).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Applying 12% employer PF entirely to EPF. The employer 12% is split: 8.33% of PF wages goes to EPS (pension) and only the rest to EPF. The dues sheet shows both lines.
2. Keeping the old ₹15,000 ceiling in a formula. The ceiling moved to ₹25,000 from 17 September 2026. Because it sits in Setup B11, every formula updated with one edit.
3. Charging ESI on employees above ₹21,000. Coverage depends on the monthly gross. Ravindra Bagale and Shraddha Bagale earn above the ceiling, so their ESI is 0.
4. Rounding ESI normally. ESI contributions are rounded up to the next rupee, so the formula uses ROUNDUP, not ROUND.
5. Forgetting that TDS is not a rate. TDS depends on regime and declarations. It is typed on Inputs; the sheet only carries it to net pay and the dues list.

5. More real examples

Example 1: Factory paying overtime. Add an OT hours input and an OT pay column (hours × hourly rate × 2 as per your rules). OT is part of gross earned for ESI but not of basic for PF.

Example 2: Firm with 25 employees. Extend every range from row 12 to row 29 (or convert the ranges to Tables). Nothing else changes.

Example 3: Employer paying PF on full basic. Some employers contribute on basic above the ceiling. Set B11 to a very large number for those employees with a per-employee PF on full basic? column and an IF.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate employer PF contribution in Excel?

PF wages = MIN(earned basic, ceiling). EPS = ROUND(PF wages × 8.33%, 0); employer EPF = employee PF − EPS. Together they make the employer 12%.

What is the PF wage ceiling now?

₹25,000 a month from 17 September 2026 (S.O. 5109(E), Ministry of Labour and Employment, and PIB release of 16 September 2026). It was ₹15,000 from September 2014.

Who is covered by ESI?

Employees whose wages are up to ₹21,000 a month (₹25,000 for persons with disability). Employee 0.75%, employer 3.25% (esic.gov.in).

What is employer cost or monthly CTC?

Gross earned + employer EPS + employer EPF + employer ESI. In this sheet it is column T: ₹1,87,807 for October.

Does this file calculate gratuity, bonus or EDLI?

No. It covers the monthly PF, ESI, PT and TDS lines. Add columns for EDLI admin charges, bonus or gratuity provisions if your accountant needs them.

7. Quick quiz

Ten questions on this page. Sign in to save your score.