How to Calculate the GPF Interest in Excel – Easy Steps

What Is GPF Interest?

Government or non-government personnel can use the General Provident Fund (GPF) as an excellent savings tool. This is calculated through an interest rate which is known as GPF interest. Employees contribute with a part of their salary, accumulating a balance in the GPF account, which will be refunded upon retirement.

This is a sample Provident Fund dataset based on various parameters.

Step 1: Inserting Primary Data

• Insert columns to store the variables and insert the primary data: Basic Salary, Interest Rate, Basic Increment, Individual and Company Contributions.

Step2 : Calculating Basic Salaries for 7 Years

To calculate the Basic Salaries for the next 7 years based on a yearly increment:

• Enter the following formula in C5 and press ENTER. This formula will store the Basic Salary in C5.

• To calculate the Basic Salaries for the upcoming years, enter the formula in D6 and press ENTER.

• Use the Fill Handle to AutoFill the rest of the cells.

Step 3: Determining Individual and Company Contributions

• Use the following formula to calculate the Individual Contribution for the first year.

• Drag down the Fill Handle to determine Individual Contributions for the upcoming years.

• Enter the following formula to calculate the Company Contributions.

• To store the total Contributions, enter the following formula.

Step 4: Calculating Closing Balance

• Enter the formula below in H5. It will return the Closing Balance amount of the first year.

• Store this value in C66 using the formula below.

• After that, use the Fill Handle to AutoFill the rest of the cells.

• Enter the following formula to calculate the Closing Balance for the second year.

• Dag the Fill to AutoFill therest of the cells. The Opening Balance column will fill automatically.

Step 5: Calculating the Gross Total

To calculate the total amount of Individual and Company Contributions and the Closing Balances:

• Enter the following formula in E12.

The formula uses the SUM function to return the total Individual Contribution.

• Drag the Fill Handle to the right to calculate the Company Contribution and Total Contributions.

• Store the final Closing Balance amount using the formula below.

Practice Section

Practice here.

Download Practice Workbook

Related Articles

• How to Calculate Interest Between Two Dates in Excel
• Calculation of Interest During Construction in Excel
• How to Split Principal and Interest in EMI in Excel
• Perform Carried Interest Calculation in Excel
• How to Perform Actual 360 Interest Calculation in Excel
• How to Use Cumulative Interest Formula in Excel
• How to Calculate Daily Interest in Excel

<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel