Need Formula/procedure For EPF / PF Calculation Excel File

kind send me the excel file containing formulas for calculation of EPF etc
27th December 2007 From India, New Delhi

Dear Madhvai,
thanks for the reply. What i exactly need is some formula by which we can caculate how much amount will be accumulated at the year's end at a fixed epf rate (say 8.5%) on the combined monthly EPF shares of Employee and Employer (i.e 12% + 3.67% of {basic+DA})
i have sent the scenario in excel file (sheet2) at your email id.
thanks again
30th December 2007 From India, New Delhi

Attached Files

Membership is required for download. Create An Account First

pf_calculation_686.xls (35.0 KB, 16312 views)

Dear Sir,
Thanks for the formula sheet, but i suggest that the formula sheet provided by you have to be changed such as if an employees basic salary is more than 6500 then the maximum deduction to the pension should be Rs. 541 but as per you formula it is diffeerent .
Sir just change the formulla and get your statement revised.
Regards
Chandrakant V.
9820592842
(PF, ESI, COntract Labour and other labour law Consultant)
30th December 2007 From India, New Delhi

hi dear chandrakanth i forgot to mention about the wage limit i
if the wages is less than 6500 then the formula will be calculated as (basic+da)*12% and if it is greater than 6500 it will be fixed as 780 apart from that employer provident fund wii be 780*3.67/12 and family pension fund is 780*8.33/12
madhavi
30th December 2007 From India, Hyderabad

hi, please find enclosed the excel sheet for the reference Regards,
30th December 2007 From India, Pune

Attached Files

Membership is required for download. Create An Account First

epf_1__123.xls (13.5 KB, 7287 views)

Thanks for the calculations. Can u please help me to know the excel formulas to generate the 3A/6A reports through the said sheet regards kumar 9885576600
10th July 2008 From India, Hyderabad

Thanks a lot
I am in the process of calcualating the PF amoutn for our employees I know the %s but I don't have the format. I was planning toprepare the formula format for PF but You made my work easy.
It helped me a lot.
Thanks
usha

11th July 2008 From India, Hyderabad

please give a revised excel calculation sheet for pf with salary more than Rs.6500/- per month.
23rd December 2008 From India, Calcutta

hi Im new to this site really very useful site. Im looking for Design Engineers who have experience in Manufacturing firm especially ciombatore based motors/pump industry . could anybody can help me how to start my hunt
20th June 2009 From India, Bangalore

[ dear sir i want to know the latest functions of excel 2007.please send me any file in which i understand all about the excel
8th August 2009 From India, Ambala

dear sir, i want to know how to create auto generate calculation table in excel file,kindly advice asap.kindly acknowladge
26th September 2009 From India, Mumbai

Can anyone please tell me how to calculate PF inspection charges in PF summary report. imanage
17th December 2009 From India, Bangalore

Dear S.B. goswamy,

I shall insert an excel sheet for pension calculation. Prior to that I shall give some ideas on the suject, so that you may calculate manually also.

There is no upper limit for Eps-95 pension. For pension calculation, the service will be taken into 2 parts. Service before 16.11.95 and service w.e.f 16.11.95. The first one is called as past service and latter one as pensionable service. Past service is divided into 4 slabs. Service upto 11 years, 12 to 15 years, 16 to 19 years and 20 & above. If the salary on 16.11.95 is below Rs. 2500, the monthly compensation will be Rs. 80, 95, 120 & 150 respectively. For Rs. 2500 & above this will be Rs. 85, 105, 135 & 170. This amount is for those who attain 58 years on 16.11.95. In the case of those attain 58 years after 16.11.95, the above compensation will be multiplied by a factor stipulated in table B, according to the difference between 16.11.95 and the date of completion of 58 years.

For pensionable service there is a formula to calculate pension. It is Pensionable Salary x Pensionable Service / 70. Pensionable salary can be categorised in to 3. 1) Below Rs. 6500. 2) Rs. 6500 & above, but contribution on statutory celing of Rs. 6500. 3) Above Rs. 6500 & opted to contribute on actual salary. In case of 2nd, pensionable salary is Rs. 6500. In other two cases, pensionable salary will be the average of last twelve months. Also if pensionable service is 20 years & above 2 year's bonus will be given.

For details please see the web site :

EPFO <link updated to site home>

One example I shall quote.

Date of Birth - 2.1.1961

Date of join - 23.2.1987

Salary on 16.11.95 - Rs. 2500 & above

Salary on completion of 58 years on 1.1.2019 - Rs. 6500 (Statutory Ceiling)

Past Service - 8 yr 9 m (approx) rounded to 9 years

Compensation - Rs. 85

Factor as per Table B (for less than 24 years, i.e the difference between 16.11.95 & 1.1.2019) - 6.102

(This can be calculated as 1.08 to the power of 24 - 0.5, correct to 3 decimals)

Past Service Benefit - 85 x 6.102 = Rs. 519 - (A)

Pensionable Service - 23 years

Bonus (Service is 20 & above) - 2

Pensionable Salary - Rs. 6500

Pensionable Benefit - 6500 x 25 / 70 = 2321 - (B)

Total Pension - (A) + (B) = Rs. 2840

Besides the above method calculation there will be a minimum for those who have service before 16.11.95. In the EPS-95, they are categorised into three.

1. Date of commencement of Pension before 16.11.2000

2. Date of commencement of Pension between 16.11.2000 & 16.11.2005

3. Date of commencement of Pension after 16.11.2005

As the first two categories are already over, I shall a give a brief on third.

Pensionable benefit (minimum) of Rs. 635 and Past service benefit as mentioned above, subject to a minimum of Rs. 800. This amount is for 24 yrs or more service. If it is less than 24 yrs, this will be reduced in proportionate (amount x actual service / 24). However this amount will be subject to a minimun of Rs. 450.

I shall insert Excel work sheet to calculate pension. Enter Date of Birth, Date of Join, Date of Seperation from Service, Salary on 16.11.95, Salary on Seperation from Service ( in compliance with the contribution to pension fund) and break in service before and after 16.11.95, if any in green colour column. The results will appear in yellow colour column. The red colour is for static information.

In case of any error or suggestion, please notice to me.

Abbas.P.S, ITI Ltd, PALAKKAD - 678 623.

Ph. +91 9447 467 667
4th September 2010 From India, Bangalore

Attached Files

Membership is required for download. Create An Account First

EPF Pension.xls (21.5 KB, 775 views)

Need your Suggestion… For calculating my PF...

Please correct professionally If am wrong….

Organization Joined:

 1 June 2005 Basic salary: Rs.5500

 PF deduction 36 months Rs.660 (employee side only)

 After Promotion :

 PF Deduction 09 months Rs.1660 (employee side only)

 Total contribution 45 months only

Calculation:

 Both side contributions (Employee & Employer 12% +12%) of basic salary of 12%

 With out annually interest.

660 + 660 = Rs.1,320 x for 36 months = Rs.47,520

1,660 +1,660 = Rs. 3,340 x for 09 months = Rs.29, 880

Without annually Interest rate of 8.5% only.

Rs. 47,520 + Rs.29, 880= Rs.77, 400 (without Interest)

With annually interest rate of 8.5%.

Interest calculated annually form joining date to resigned (1st june 2005 to 2nd april 2009 ) not included interest after that period. Apply for PF claim may 2010.



Rs.53, 202 + Rs. 36,022 = Rs. 89,224 (with annually interest)

Please guide me what will I get exact PF amount on next month. thanks

Note: Last month referance case,one of my friend worked in organisation 18 months only. he withdraw his pf last month he resigned two year ago and he got pf calim in two part.two sms alart received on cell Rs. 17300 and Rs.6900. his pf dedected 450 emplyee side total monthly Rs.900 only .

please your comment:

or mail on:
5th September 2010 From India, Mumbai

Dear Nushad,

You have mentioned that for 36 months your salary is Rs. 5500/- & Rs. 660 is paid to EPF. For next 9 months your contribution is Rs. 1660/- monthly. From your contribution I assume that your last drawn salary is Rs. 13830/-. ( If any difference, please clear it).

Out of the above contribution Rs. 458 each for 1st 36 months (8.33% of 5500) and Rs. 541 each for next 9 months (8.33% of 6500 ceiling limit) might have remitted to pension fund. Balance amount you can withdraw from PF.

The deposit will attract an annual interest of 8.5%, but monthly compounding. For this I shall insert an excel sheet. As per this calculation employee cotribution after the 45th remittance with interest is Rs. 44092 and employer contribution is Rs. 19152.

Regarding pension contribution, you may apply for Scheme Certificate through Form 10-C and can be added to later services. Other wise you may avail withdrawal benefit. 45 months of servise will be rounded upto 4 yrs and its withdrawal benefit is 3.99 (multiplying factor as per Table D) x 6500 (salary ceiling) = Rs. 25935/-.

Abbas.P.S
8th September 2010 From India, Bangalore

Attached Files

Membership is required for download. Create An Account First

EPF Statement.xls (19.5 KB, 755 views)

Need your Suggestion… For calculating my PF...

Please correct professionally If am wrong….

Organization Joined:

 1 June 2005 Basic salary: Rs.5200

 PF deduction 37 months Rs.660 (employee side only)

 After Promotion :

 PF Deduction 09 months Rs.1660 (employee side only)

 Total contribution 46 months only

Calculation:

 Both side contributions (Employee & Employer 12% +12% (3.67 and 8.33%) of basic salary of 12%

 With out annually interest.

624 + 624 = Rs.1,248 x for 37 months = Rs.46,176

1,660 +1,660 = Rs. 3,340 x for 09 months = Rs.29, 880

Without annually Interest rate of 8.5%.

Rs. 46,176 + Rs.29, 880= Rs.77, 400 (without Interest)

With annually interest rate of 8.5%.

Interest calculated annually form joining date to resigned (1st june 2005 to 2nd april 2009 ) not included interest after that period. Apply for PF claim may 2010.

Rs.53, 202 + Rs. 36,022 = Rs. 89,224 (with annually interest)

Please guide me what will I get exact PF amount on next month. thanks

Note: Last month referance case,one of my friend worked in organisation 18 months only. he withdraw his pf last month he resigned two year ago and he got pf calim in two part in same day.two sms alart received on cell Rs. 17300 and Rs.6900. his pf dedected 450 emplyee side.

please your comment:?

? how much amount credited in bank account.

? if intrest not calculated on 8.33% when will get these amount.

? i am not intitle for pension.

? cedited pf in two part in my bank account or one. credited total 24% with intrest or only 12%+3.67%)

Regrds

Nushad
14th September 2010 From India, Mumbai

Dear Nushad,
Please avoid quotings and directly come to the details and accompanying questions.
I have edited the above excel sheet so that now you can entry the interest rate and ceiling limit. As the contribution to EPS is computed as 8.33%, this excel sheet is applicable only w.e.f 16.11.95.
The interest rate applicable there after is
16.11.95 to 31.3.2000 - 12%
1.4.2000 to 31.3.2001 - 11%
1.4.2001 to 31.3.2005 - 9.5%
1.4.2005 onwards - 8.5%
Ceiling limit :
16.11.95 to 31.5 2001 - 5000
1.6.2001 onwards - 6500
Abbas.P.S
15th September 2010 From India, Bangalore

Dear mahanty68,
we know that the ceiling amount for the deduction of pf is 6500 i.e. it is the basic salary of an employee. But, if a person who is getting his basic salary more than the ceiling amount than he/she is given a choice to take the opportunity. It is not mandatory to be included under the list of pf deducted. Say his basic salary is 8400 than he/she can enjoys the pf benefits or may not enjoy. But, if he/she wants to enjoy the benefits provided by pf than he/she must write an application to the HR Manager of the company he/she is working and after that the HR Manager will have to write an application to the pf official of the concerned state requesting them for assuming their basic as 6500, and they are willing to pay the pf amount on 6500 only.
Thanks,.
Hrishikesh
+91-8811023446
HR Executive
Guwahati, Assam
8th August 2013 From India, Delhi

1. Design LTA rule for a manufacturing company where basic range from 2000pm to 100000pm how you can give maximum benefit to an employee, keeping Income tax provisions in mind.
2. Draw a table of prevailing state wise minimum basic wages, parallel to this draw another table of salary to be paid to ITI graduate experience up to 3yrs. Should we deduct Provident fund on minimum wages or basic salary.
8th August 2014 From India, Ranchi

I need to put a formula in excel sheet to calculate PF amount.
So how do we put a formula if the wage ceiling less than 15000 (standard deduction) or greater than 15000.
Can somebody help me with excel formula please...
10th March 2015 From India, Bangalore

If the amount is equal to 15000 then we give the mentioned formula = sum( Basic+ DA)*12%. ( in salary statement or payroll statement- excel )
what is the formula if Basic+ DA less or greater than 15000?
can anybody answer me please?
10th March 2015 From India, Bangalore

Hi,
If the amount is equal to 15000 then we give the mentioned formula = sum( Basic+ DA)*12%. ( in salary statement or payroll statement- excel )
what is the formula if Basic+ DA less or greater than 15000?
can anybody answer me please?
i found the answer for this question :
here it is =IF(SUM(D3, E3) >= 15000, 1800, SUM(D3, E3)*12%)
10th March 2015 From India, Bangalore

Dear Sir, One of my friend is working as Security Guard. He is paid on daily basis. The security agent deduct PF from his salary. Please tell me how he will calculate PF against his salary
5th August 2016 From India, Kolkata

Hi, i worke in Pvt.company last 5.5 year my PF was cut in my salary Rs.865 so how much Rupees i got.
5th September 2016 From India, Thane

sir,
I have to explain this excel assignment to the students. plz help me. i have send the scanned assignment so that u can know excaltly what i need is formula for DA, TA,HRA, Spl. Allow, EPF, HRD, Tax.
30th June 2017 From India, Patna

Attached Images

Membership is required for download. Create An Account First

excel assignment 001.jpg (548.1 KB, 116 views)

salary slip headwise detail rules
30th October 2017 From India, Uran

if basic wage is more then 6500 we should pay 5** something,is there any possible to pay less then that ceiling amount
5th December 2017 From India, Chennai