Cite.Co is a repository of information and resources created by industry seniors and experts sharing their real world insights. Join Network
dear sir, i want to know how to create auto generate calculation table in excel file,kindly advice asap.kindly acknowladge
From India, Mumbai
Can anyone please tell me how to calculate PF inspection charges in PF summary report. imanage
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

From India, Bangalore

Attached Files
File Type: xls EPF Pension.xls (21.5 KB, 816 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:

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

From India, Bangalore

Attached Files
File Type: xls EPF Statement.xls (19.5 KB, 797 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

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

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

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.

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...

From India, Bangalore

This discussion thread is closed. If you want to continue this discussion or have a follow up question, please post it on the network.
Add the url of this thread if you want to cite this discussion.






About Us Advertise Contact Us
Privacy Policy Disclaimer Terms Of Service



All rights reserved @ 2020 Cite.Co™