Formula for calculation of ESI and EPF

This query is : Resolved

Report Abuse Follow Query Ask a Query

Daya

Daya (Querist)
Follow

22 May 2025 can any one provide formula for calculating EPF and ESI in excel for employer and employee both.

Ram Avtar Singh

Ram Avtar Singh (Expert)
Follow

23 May 2025 Here's a clear breakdown of how to calculate EPF (Employees' Provident Fund) and ESI (Employees' State Insurance) in Excel for both Employer and Employee contributions as per standard Indian payroll practices (as of the latest available norms):

✅ EPF Calculation (Applicable when basic + DA ≤ ₹15,000/month)
Contribution Rate Applies on
Employee 12% Basic + DA
Employer 12% Basic + DA
Employer split 3.67% (EPF) + 8.33% (EPS) Basic + DA

Note: If Basic > ₹15,000, contribution can still be on ₹15,000 unless voluntarily agreed otherwise.

✅ ESI Calculation (Applicable when Gross ≤ ₹21,000/month)
Contribution Rate Applies on
Employee 0.75% Gross Salary
Employer 3.25% Gross Salary

📊 Example Excel Sheet Setup
Assume these columns:

A B
Employee Name [Name]
Basic + DA 15000
Gross Salary 20000

Now the formulas:

🔢 EPF Formulas
Employee EPF:

sql
Copy
Edit
=MIN(B2,15000)*12%
Employer EPF:

sql
Copy
Edit
=MIN(B2,15000)*3.67%
Employer EPS (Pension):

sql
Copy
Edit
=MIN(B2,15000)*8.33%
🔢 ESI Formulas
Employee ESI:

Copy
Edit
=IF(B3<=21000,B3*0.0075,0)
Employer ESI:

Copy
Edit
=IF(B3<=21000,B3*0.0325,0)
✅ Notes:
Replace B2 with the actual cell for Basic + DA

Replace B3 with the actual cell for Gross Salary