Formula for calculation of ESI and EPF

Save Share

This query is : Resolved

Quick Summary

Provides EPF & ESI calculation formulas for Excel. EPF: 12% of Basic+DA (capped at Rs.15,000), employer split into EPF & EPS. ESI: 0.75% (employee) and 3.25% (employer) on gross up to Rs.21,000, with sample Excel formulas.

Daya

Daya (Querist)

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)

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

You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now