Ready-to-use Employee Provident Fund Calculator Template in Excel, OpenOffice Calc & Google Sheet to track and calculate the EPF amount.
With this template, you can calculate the real-time EPF balance in your EPF Account on monthly basis as well as yearly up to 35 years of service.
It is an important HR document that assists in calculating the retirement benefits of employees. Thus, it is useful to small/medium-sized business owners, HR professionals, and accountants.
Table of Contents
Download Employee Provident Fund Calculator Template (Excel, OpenOffice Calc & Google Sheet)
Additionally, you can also download other HR templates like Payroll Template With Attendance, Salary Slip Excel Template India, Salary Slip Format UAE, Simple Salary Sheet, and Employee Salary Sheet depending on the company requirement.
Feel free to contact us for the customization of this template as per your requirement. We also design new templates based on your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.
How To Use This EPF Calculator?
For ease of use and user convenience, the EPF Calculator available to download in three different formats: Excel, OpenOffice Calc, and Google Sheet. Download the desired format and start using it.
This Employee Provident Fund Calculator is a fully automated Calculator that calculates the following based on Indian Labor Laws:
- Monthly Contribution based on the input of current basic salary (12% employee and 3.67% employer).
- Interest received on monthly contribution.
- Monthly employer contribution to Employee Pension Scheme (EPS).
The user needs to insert only the basic salary for non-government employees and Basic Salary + DA (dearness allowance) in the second column.
Moreover, the EPF estimator helps to estimate the amount of Employee Provident Fund an employee can accumulate during 30-35 years of service. It also includes the provision of a yearly percentage increase in salary.
All you need to do is to fill details in the table on the right side. The template will automatically estimate the EPF and EPS amount balance along with interest at the end of the period.
Before we proceed to the components of the template, let us understand the Employee Provident Fund in brief.
What is EPF?
EPF or Employee Provident Fund is a retirement benefit program made under the Employees’ Provident Funds and Miscellaneous Provisions Act, 1952. This fund is managed by the EPFO (Employee Provident Fund Organization). This organization runs under the government.
Under this scheme, the employee and employer make contributions towards this fund. At the end of the service, the employee gets a lump sum amount which they can use in case of unemployment, disability, or retirement.
The employee receives 8.50% annual compounding interest on the total contributions made by the employee and employer together.
Applicability of EPF to Companies
Under the Labor Law, it is mandatory for every company having more than 20 employees to register with EPFO.
Employee Eligibility
It obligates the employee bearing salary below Rs. 15000 to register with EPFO. Employees with higher salaries can become members by taking prior permission from PF Commissioner. It has to be a mutual decision of the employer and employee for those with higher salaries than the threshold limit.
EPF contribution Rules
Under EPFO, the employee and employer both make an equal contribution. But the employee contribution is further divided into 2 parts; EPF contribution and EPS contribution.
A government or public sector employee makes 12% of Basic and DA whereas a private sector employee makes 12% of his basic salary. Complete 12% of the employee contribution goes to the EPF account.
The employer also makes a contribution of 12% of Basic + DA in the case of the government/public sector. Whereas 12% of only basic in the case of private-sector employees.
3.67% of the employer contribution is deposited to the Employee Provident Fund. Whereas the remaining 8.33% is deposited in Employee Pension Scheme.
The minimum contribution accepted is 12% of Rs. 15000 which is Rs. 1800. Thus, if the employee’s salary is below Rs. 15000, the employer and employee both need to make Rs. 1800 as a contribution. The employer contribution will be distributed as discussed above.
Let us understand the calculations with the help of different examples.
Example – EPF Calculation With Basic Salary Below Or Equal To Threshold Limit
Mr. Surendar is working for Company A with CTC Rs. 18000 and a Basic salary of Rs. 9500 and Dearness Allowance of Rs. 2000.
The sum of basic and DA is Rs. 11500. But the employee, as well as an employer contribution, needs to be calculated on Rs. 15000 as per the rules. Thus, the calculation of EPF will be as follows:
Employee Contribution: Rs. 15000 X 12% = Rs. 1800.
Employer Contribution:
- Rs. 15000 X 3.67% = Rs. 550.50 to EPF Contribution.
- Rs. 15000 X 8.33% = Rs. 1249.50 to EPS Contribution.
Let us take another example in which the Basic and DA is above Rs. 15000 threshold limit.
Example – EPF Calculation With Basic Salary Above The Threshold Limit
Mr. Johnson is working for Company B with CTC Rs. 56000 and a Basic salary of Rs. 30000 and Dearness Allowance of Rs. 5000.
The sum of basic and DA is Rs. 35000. Thus, the calculation of EPF will be as follows:
Employee Contribution: Rs. 35000 X 12% = Rs. 4200.
Employer Contribution: Rs. 4200 out of which Rs. 1249.50 that is 8.33% of Rs. 15000 will be contributed to EPS and the remaining will be added contributed to EPF.
- Employer EPF Contribution = Rs. 4200 – Rs. 1249.50 = Rs. 2950.50.
- EPS Contribution.= Rs. 15000 X 8.33% = Rs. 1249.50.
Let us take another example in which the Basic and DA is above Rs. 15000 threshold limit.
EPF Withdrawal Rules
The employee can partially or completely withdraw the EPF amount. Full withdrawal can be done under 3 circumstances:
- an individual retires at the age of 58.
- If an individual is unemployed for more than 2 months.
- If an individual dies.
Apart from that, different withdrawal purposes have different rules. See the image below:
For other scenarios, kindly click the link below:
EPF Withdrawal Rule Book
EPF Income Tax Applicability
Until 2020, EPF deposits and interest were completely exempt from tax. In Budget 2021, it has been decided that if the deposits in Employee Provident Fund and Voluntary Provident Fund will exceed Rs. 2.5 Lakh during a financial year, then the interest above Rs. 2.5 Lakh will be taxable.
Moreover, if no contribution is done by the employer to the EPF account, then the interest component is exempt up to the deposit of Rs. 5 Lakh in a particular financial year.
The above details are just in brief to help you understand the Employee Provident Fund calculations more simply and easily.
Let us understand the contents of the template in detail.
Components of Employee Provident Fund Calculator Template (Excel, OpenOffice Calc & Google Sheet)
Employee Provident Fund Calculator consists of 2 sheets: EPF Calculator and EPF Estimator.
EPF Calculator Template
The EPF calculator consists of the following heads:
Month
Basic Salary + DA
EPF Employee Contribution
EPF Employer Contribution
Additional EPF Contribution
Total Contribution
Monthly EPF Interest Rate
Interest Amount
EPF Balance
EPS Amount
Insert only the basic salary amount in the second column. All other columns consist of predefined formulas and are auto-populated. Moreover, you can calculate real-time monthly EPF and EPS contributions for 3 years.
Additional EPF Contribution is the balance amount left after deducting the maximum EPS Rs. 1249.50 which is the threshold amount. Please refer to the second example given above.
Total Contribution is the sum of 3 columns: EPF Employee Contribution, EPF Employer Contribution, and Additional EPF Contribution.
EPFO gives 8.5% annual compounding interest on EPF deposits. Hence, divide the 8.5% by 12 to get the monthly interest rate that is 0.71%.
Interest amount is calculated on the Total Contribution amount from the second month of the financial year. Because the first month’s contribution is made after completion of the first month. The interest of that EPF deposit comes in the preceding month.
EPF balance is the total EPF accumulated without interest. EPS is calculated on Rs. 15000 basic as per the rules. Please check the above EPS rules section.
EPF Estimator Template
EPF Estimator is useful to those people who want to estimate their PF amount at the end of their service. There are two sections in this template: EPF Input Data and EPF Output Data.
EPF Input Data
The EPF input data section consists of the following:
Employee Name
Employee CTC
Basic + DA
% Annual Increase in Basic
EPF Interest Rate
EPF Contribution % (Employee & Employer)
EPS Contribution % (Employer)
Employee Name, CTC, and Basic will changes based on the employee. Whereas the other details remain static and only change when government policy changes. These details are currently as per the Fiscal Year 2021-22.
Usually, salaries of employees increase nominally 3% to 5% every year. Hence, you can estimate the % increase in your company and insert the % increase accordingly.
As the employer deposits the EPF amount after the completion of the month, it calculates the interest for the first month in the preceding month.
Thus, during the fiscal year, You don’t receive interest in the first month. Furthermore, at the end of the fiscal year, it calculates the balance without any deductions for the last month.
EPF Output Data
The template auto-populates the EPF data output section once you enter the above-mentioned details. With the help of this template, you can calculate EPF and EPS amount balance for 35 years.
This section consists of the following columns:
Year
Opening Balance
Basic Pay + DA
EPF Employee Contribution
EPF Employer Contribution
Total Contribution
Annual Rate of EPF interest
EPF Closing Balance
EPS Contribution
In this sheet, the template displays all the calculations based on a full year. Moreover, if you already have an opening balance, insert the amount in cell C7.
All other columns and cells in this sheet consist of predefined formulas. Hence, you don’t need to enter any data in this section.
If the employee is from the public sector or government employee, insert his Basic plus DA. Whereas, if the employee is from the private sector, insert only the basic salary.
The employee contributes 12% of his Basic to EPF Contribution whereas the employer contributes 3.67% of Basic Salary.
Basically, the employer also makes a total contribution of 12%. As per rules, the employer can contribute only 8.33% to Employee Pension Scheme as the rules.
The template calculates EPF Employee Contribution and EPF Employer Contribution on 12 months basis.
Employee Contribution: (Basic + DA) X 12% X 12 months
Employer Contribution: (Basic + DA) X 3.67 % X 12 months
The government provides 8.5% interest on these EPF deposits. Hence, it calculates the interest on the total contribution amount.
The real-time calculations are made on the compounding interest formula on the monthly balance. Hence, the yearly closing balance differs. The last two columns display the EPF and EPS balances.
Frequently Asked Questions
No, interest is not calculated on the withdrawn amount. However, interest will be calculated on the remaining amount. To activate UAN, register at the EPF member portal before processing claims or withdrawing funds online. No, you don't need to take new UAN. UAN allotted to a member will remain the same. The new PF account by the new employer is linked to the existing UAN. Transfer option is available. You can do both. It is highly recommended that you transfer the amount. Because, withdrawing the amount before 5 years of service is taxable. Contributions made by employee and employer to EPF are exempt respectively for both. The employer’s contribution is tax-exempt at its source. Whereas, the employee’s contribution is tax deductible under section 80C up to a maximum of Rs. 1.5 lakh per annum for 2019-2020 and 2.5 lakhs per annum 2021-2022. Thus, employee’s contribution is eligible to for tax-exemption only under section 80C.If EPF is partially withdrawn is interest calculated on withdrawn amount?
How to activate UAN for transferring PF online?
Is it necessary to get a new UAN if we change job?
Do we need to withdraw the EPF amount when we change new job or it can be transferred?
Are both the employee’s and employer’s contributions to my EPF account tax-exempt?
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.
Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.