dear sir, i want to know how to create auto generate calculation table in excel file,kindly advice asap.kindly acknowladge
From India, Mumbai
From India, Mumbai
Can anyone please tell me how to calculate PF inspection charges in PF summary report. imanage
From India, Bangalore
1From 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 subject so that you may calculate manually as well.
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 past service, and the latter one is pensionable service. Past service is divided into 4 slabs: service up to 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 who 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: Pensionable Salary x Pensionable Service / 70. Pensionable salary can be categorized into 3: 1) Below Rs. 6500. 2) Rs. 6500 & above, but contribution on the statutory ceiling of Rs. 6500. 3) Above Rs. 6500 & opted to contribute on actual salary. In the case of the 2nd, pensionable salary is Rs. 6500. In the other two cases, pensionable salary will be the average of the last twelve months. Also, if pensionable service is 20 years & above, 2 years' bonus will be given.
For details, please see the website: [EPFO](http://epfindia.com)
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 years 9 months (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 of calculation, there will be a minimum for those who have service before 16.11.95. In the EPS-95, they are categorized 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 give a brief on the 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 years or more service. If it is less than 24 years, this will be reduced proportionately (amount x actual service / 24). However, this amount will be subject to a minimum of Rs. 450.
I shall insert an Excel worksheet to calculate pension. Enter Date of Birth, Date of Join, Date of Separation from Service, Salary on 16.11.95, Salary on Separation from Service (in compliance with the contribution to the pension fund) and break in service before and after 16.11.95, if any, in the green color column. The results will appear in the yellow color column. The red color is for static information.
In case of any error or suggestion, please notify me.
Abbas. P. S., ITI Ltd, PALAKKAD - 678 623.
Ph. +91 9447 467 667
From India, Bangalore
I shall insert an Excel sheet for pension calculation. Prior to that, I shall give some ideas on the subject so that you may calculate manually as well.
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 past service, and the latter one is pensionable service. Past service is divided into 4 slabs: service up to 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 who 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: Pensionable Salary x Pensionable Service / 70. Pensionable salary can be categorized into 3: 1) Below Rs. 6500. 2) Rs. 6500 & above, but contribution on the statutory ceiling of Rs. 6500. 3) Above Rs. 6500 & opted to contribute on actual salary. In the case of the 2nd, pensionable salary is Rs. 6500. In the other two cases, pensionable salary will be the average of the last twelve months. Also, if pensionable service is 20 years & above, 2 years' bonus will be given.
For details, please see the website: [EPFO](http://epfindia.com)
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 years 9 months (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 of calculation, there will be a minimum for those who have service before 16.11.95. In the EPS-95, they are categorized 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 give a brief on the 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 years or more service. If it is less than 24 years, this will be reduced proportionately (amount x actual service / 24). However, this amount will be subject to a minimum of Rs. 450.
I shall insert an Excel worksheet to calculate pension. Enter Date of Birth, Date of Join, Date of Separation from Service, Salary on 16.11.95, Salary on Separation from Service (in compliance with the contribution to the pension fund) and break in service before and after 16.11.95, if any, in the green color column. The results will appear in the yellow color column. The red color is for static information.
In case of any error or suggestion, please notify me.
Abbas. P. S., ITI Ltd, PALAKKAD - 678 623.
Ph. +91 9447 467 667
From India, Bangalore
Hi,
Thank you for reaching out for suggestions on calculating your PF. Below is a breakdown of your contributions and calculations:
Organization Joined:
- 1 June 2005
- Basic salary: Rs. 5500
- PF deduction for 36 months: Rs. 660 (employee side only)
- After Promotion:
- PF Deduction for 09 months: Rs. 1660 (employee side only)
- Total contribution for 45 months
Calculation:
- Both side contributions (Employee & Employer 12% + 12%) of basic salary of 12%
- Without annual interest:
660 + 660 = Rs. 1,320 x 36 months = Rs. 47,520
1,660 + 1,660 = Rs. 3,340 x 09 months = Rs. 29,880
Without annual interest rate of 8.5% only:
Rs. 47,520 + Rs. 29,880 = Rs. 77,400 (without interest)
With an annual interest rate of 8.5%:
Interest calculated annually from the joining date to resignation (1st June 2005 to 2nd April 2009), not including interest after that period. Applied for PF claim in May 2010.
Rs. 53,202 + Rs. 36,022 = Rs. 89,224 (with annual interest)
Please guide me on the exact PF amount I will receive next month. Thank you.
Regarding your friend's case, he worked in the organization for 18 months, resigned two years ago, and withdrew his PF last month. He received the PF claim in two parts: Rs. 17,300 and Rs. 6,900. His PF deduction was Rs. 450 on the employee side monthly, totaling Rs. 900.
Please feel free to share your comments or email me at ajjuz1@rediffmail.com for further discussion.
Thank you.
From India, Mumbai
Thank you for reaching out for suggestions on calculating your PF. Below is a breakdown of your contributions and calculations:
Organization Joined:
- 1 June 2005
- Basic salary: Rs. 5500
- PF deduction for 36 months: Rs. 660 (employee side only)
- After Promotion:
- PF Deduction for 09 months: Rs. 1660 (employee side only)
- Total contribution for 45 months
Calculation:
- Both side contributions (Employee & Employer 12% + 12%) of basic salary of 12%
- Without annual interest:
660 + 660 = Rs. 1,320 x 36 months = Rs. 47,520
1,660 + 1,660 = Rs. 3,340 x 09 months = Rs. 29,880
Without annual interest rate of 8.5% only:
Rs. 47,520 + Rs. 29,880 = Rs. 77,400 (without interest)
With an annual interest rate of 8.5%:
Interest calculated annually from the joining date to resignation (1st June 2005 to 2nd April 2009), not including interest after that period. Applied for PF claim in May 2010.
Rs. 53,202 + Rs. 36,022 = Rs. 89,224 (with annual interest)
Please guide me on the exact PF amount I will receive next month. Thank you.
Regarding your friend's case, he worked in the organization for 18 months, resigned two years ago, and withdrew his PF last month. He received the PF claim in two parts: Rs. 17,300 and Rs. 6,900. His PF deduction was Rs. 450 on the employee side monthly, totaling Rs. 900.
Please feel free to share your comments or email me at ajjuz1@rediffmail.com for further discussion.
Thank you.
From India, Mumbai
CiteHR.AI
(Fact Checked)-The calculations provided for PF seem accurate based on the information given. It aligns with the standard PF contribution rules. (1 Acknowledge point)
Dear Nushad,
You have mentioned that for 36 months your salary is Rs. 5500/- and Rs. 660 is paid to EPF. For the 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 the 1st 36 months (8.33% of 5500) and Rs. 541 each for the next 9 months (8.33% of 6500 ceiling limit) might have been remitted to the pension fund. The balance amount you can withdraw from PF.
The deposit will attract an annual interest of 8.5%, but with monthly compounding. For this, I shall insert an excel sheet. According to this calculation, the employee's contribution after the 45th remittance with interest is Rs. 44092, and the employer's contribution is Rs. 19152.
Regarding pension contribution, you may apply for a Scheme Certificate through Form 10-C, which can be added to later services. Otherwise, you may avail of the withdrawal benefit. 45 months of service will be rounded up to 4 years, and its withdrawal benefit is 3.99 (multiplying factor as per Table D) x 6500 (salary ceiling) = Rs. 25935/-.
Abbas.P.S
From India, Bangalore
You have mentioned that for 36 months your salary is Rs. 5500/- and Rs. 660 is paid to EPF. For the 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 the 1st 36 months (8.33% of 5500) and Rs. 541 each for the next 9 months (8.33% of 6500 ceiling limit) might have been remitted to the pension fund. The balance amount you can withdraw from PF.
The deposit will attract an annual interest of 8.5%, but with monthly compounding. For this, I shall insert an excel sheet. According to this calculation, the employee's contribution after the 45th remittance with interest is Rs. 44092, and the employer's contribution is Rs. 19152.
Regarding pension contribution, you may apply for a Scheme Certificate through Form 10-C, which can be added to later services. Otherwise, you may avail of the withdrawal benefit. 45 months of service will be rounded up to 4 years, and its withdrawal benefit is 3.99 (multiplying factor as per Table D) x 6500 (salary ceiling) = Rs. 25935/-.
Abbas.P.S
From India, Bangalore
CiteHR.AI
(Fact Checked)-The calculation provided seems to be accurate based on the EPF contribution rules and interest rates. Well done! (1 Acknowledge point)
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
From India, Mumbai
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
From India, Mumbai
Dear Nushad,
Please avoid quoting and directly come to the details and accompanying questions. I have edited the above Excel sheet so that now you can enter 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 rates applicable thereafter are:
- 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 limits:
- 16.11.95 to 31.5.2001 - 5000
- 1.6.2001 onwards - 6500
Abbas.P.S
From India, Bangalore
Please avoid quoting and directly come to the details and accompanying questions. I have edited the above Excel sheet so that now you can enter 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 rates applicable thereafter are:
- 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 limits:
- 16.11.95 to 31.5.2001 - 5000
- 1.6.2001 onwards - 6500
Abbas.P.S
From India, Bangalore
Dear mahanty68,
We know that the ceiling amount for the deduction of PF is ₹6,500, i.e., it is the basic salary of an employee. However, if a person is receiving a basic salary higher than the ceiling amount, they are given the choice to opt for the opportunity. It is not mandatory to be included in the list of PF deductions. For instance, if their basic salary is ₹8,400, they can choose to avail of the PF benefits or opt out.
If they wish to enjoy the benefits provided by PF, they must write an application to the HR Manager of the company they are employed with. Subsequently, the HR Manager will have to submit an application to the PF official of the concerned state, requesting them to consider their basic salary as ₹6,500 for PF contribution purposes, indicating their willingness to pay PF contributions based on ₹6,500 only.
Thanks,
Hrishikesh
+91-8811023446
HR Executive
Guwahati, Assam
From India, Delhi
We know that the ceiling amount for the deduction of PF is ₹6,500, i.e., it is the basic salary of an employee. However, if a person is receiving a basic salary higher than the ceiling amount, they are given the choice to opt for the opportunity. It is not mandatory to be included in the list of PF deductions. For instance, if their basic salary is ₹8,400, they can choose to avail of the PF benefits or opt out.
If they wish to enjoy the benefits provided by PF, they must write an application to the HR Manager of the company they are employed with. Subsequently, the HR Manager will have to submit an application to the PF official of the concerned state, requesting them to consider their basic salary as ₹6,500 for PF contribution purposes, indicating their willingness to pay PF contributions based on ₹6,500 only.
Thanks,
Hrishikesh
+91-8811023446
HR Executive
Guwahati, Assam
From India, Delhi
1. Design LTA rules for a manufacturing company where the basic range is from 2000 pm to 100000 pm. How can you provide maximum benefits to an employee while considering income tax provisions?
2. Draw a table of prevailing state-wise minimum basic wages. Additionally, create another table for the salary to be paid to ITI graduates with up to 3 years of experience. Should Provident fund deductions be based on minimum wages or basic salary?
Please let me know if you need further assistance or clarification.
From India, Ranchi
2. Draw a table of prevailing state-wise minimum basic wages. Additionally, create another table for the salary to be paid to ITI graduates with up to 3 years of experience. Should Provident fund deductions be based on minimum wages or basic salary?
Please let me know if you need further assistance or clarification.
From India, Ranchi
I need to put a formula in an Excel sheet to calculate PF amount.
So, how do we put a formula if the wage ceiling is less than 15000 (standard deduction) or greater than 15000. Can somebody help me with an Excel formula, please...
From India, Bangalore
So, how do we put a formula if the wage ceiling is less than 15000 (standard deduction) or greater than 15000. Can somebody help me with an Excel formula, please...
From India, Bangalore
CiteHR.AI
(Fact Checked)-The Employees' Provident Funds and Miscellaneous Provisions Act, 1952, mandates EPF contributions up to a monthly salary of ₹15,000. For wages above that, the contribution is calculated only on the ceiling amount. Use the formula: `=MIN(ceiling, salary) * rate` for accurate calculations. (1 Acknowledge point)Looking for something specific? - Join & Be Part Of Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
CiteHR.AI
(Fact Checked)-The information provided in the user reply is accurate and detailed, covering the calculation of EPS-95 pension benefits. The user also offers to provide an Excel sheet for pension calculations based on the outlined criteria. (1 Acknowledge point)