ptiwari2ki@gmail.com Started The Discussion:
kind send me the excel file containing formulas for calculation of EPF etc
Posted 27th December 2007 From India, New Delhi
DEAR TIWARI IAM SENDING U THE FORMAT OF EPF CALCULATION IF U DONT UNDERSTAND THAT BE IN TOUCH WITH MADHAVI47@GMAIL.COM
Posted 28th December 2007 From India, Hyderabad
Attached Files
 Shared By Cite.Community Member "madhavi47" For Public Download epf_607.xls (13.5 KB, 24243 views) epf_105.xls (13.5 KB, 7379 views)

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
Posted 30th December 2007 From India, New Delhi
Attached Files
 Shared By Cite.Community Member "ptiwari2ki@gmail.com" For Public Download pf_calculation_686.xls (35.0 KB, 12504 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)
Posted 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

Posted 30th December 2007 From India, Hyderabad
hi, please find enclosed the excel sheet for the reference Regards,
Posted 30th December 2007 From India, Pune
Attached Files
 Shared By Cite.Community Member "narayanrao.mp" For Public Download epf_1__123.xls (13.5 KB, 5638 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
Posted 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

Posted 11th July 2008 From India, Hyderabad
please give a revised excel calculation sheet for pf with salary more than Rs.6500/- per month.
Posted 23rd December 2008 From India, Calcutta
Madhavi Could you please elaborate what is 780 in case person getting salary beyond Rs6500.00 thanks Tapan:icon1:
Posted 19th June 2009 From India, Hyderabad
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
Posted 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
Posted 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
Posted 26th September 2009 From India, Mumbai
Can anyone please tell me how to calculate PF inspection charges in PF summary report. imanage
Posted 17th December 2009 From India, Bangalore
Dear Sir , I want to Know how how will calculate the EPF if you send me worksheet it will will very help full to me
Posted 18th June 2010 From India, Bangalore
Please send me the formula in exel sheet to compute my pesion under EPF pension scheme 1995.Thanks & regards.
Posted 13th August 2010 From India, Delhi
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
Posted 4th September 2010 From India, Bangalore
Attached Files
 Shared By Cite.Community Member "abbasiti" For Public Download EPF Pension.xls (21.5 KB, 527 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 .

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

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
Posted 8th September 2010 From India, Bangalore
Attached Files
 Shared By Cite.Community Member "abbasiti" For Public Download EPF Statement.xls (19.5 KB, 521 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.

? 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
Posted 14th September 2010 From India, Mumbai

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
Posted 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
Posted 8th August 2013 From India, Delhi
Anonymous
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.
Posted 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...
Posted 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?
Posted 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?

i found the answer for this question :

here it is =IF(SUM(D3, E3) >= 15000, 1800, SUM(D3, E3)*12%)
Posted 10th March 2015 From India, Bangalore
Hi all...I have recently joined as HR Executive.Can anyone please suggest me how to do p and esic calculation.
Posted 18th August 2015 From India, undefined

Found This Useful? +Vote Up This Via Google.

Why Vote? User validation is extremely important for good content to prosper.
Disclaimer: This network and the advice provided in good faith by our members only facilitates as a direction towards the actions necessary. The advice should be validated by proper consultation with a certified professional. The network or the members providing advice cannot be held liable for any consequences, under any circumstances.