ESI Eligibility Confusion: How to Handle Salary Fluctuations and Deductions?

ESI Breakup Formula Inquiry

I am using this formula for ESI breakup. Please let me know if an employee's earnings are more than 20,000 Rs, then they are not eligible for ESI. However, if they are absent for around 10 days, their earnings may fall below 15,000 Rs, resulting in automatic ESI deduction. Could you please suggest another formula for this scenario?

=IF(K2<=15001, ROUNDUP(K2*1.75%,0),0)

Regards,
Riyaz.

ESI Deduction Formula Clarification

For ESI deduction, the IF condition should be used in the Actual salary, not in the Earned salary. If you use the formula in the Actual salary, you mentioned that mistakes will not happen.

Example for ESI Deduction Formula

- **Actual Salary:**
G4 = Gross Salary (20,000)

- **Earned Salary:**
H4 = Gross Salary (13,000)

Then, the formula would be as follows:
=IF(G4<=15000, H4*1.75%, 0)

If you use this formula, you mentioned that mistakes will never happen.

Thanks & Regards,
A. Maria Jerome

Dear Basha,

A. Maria Jerome is right. If you use this formula to calculate ESI, it will give you the wrong output because the comparison your formula is making is on Gross Earnings. Therefore, it is better to keep the Net Salary part also in the Excel sheet and put the formula to evaluate the net salary. On this basis, it should calculate ESI from the earned salary.

The above-suggested formula will solve your problem, but in some cases, it may calculate Rs. 1 less. Please check the attachment. The formula is a little complicated but will give you the exact contribution amount.

Hope this will help you.

ESIC Calculation Formula

Please find the ESIC calculation formula:
=IF(G5<=15001, ROUNDUP(G5*1.75%, 0), 0)
(where G2 is the Total Gross Income of the Employee)

Thank you.