Salary Structure Calculator: How Can We Make It More Effective for HR and Finance?

Hi all,

I have formulated a Salary Structure Calculator for the use of our members in HR and Finance. I appreciate your comments/suggestions to make it more effective and useful.

Best regards,
Dada

Dear Dada,

In your Excel sheet, with example figures, the PF contribution amount of the employer seems incorrect. The basic pay is Rs 20,667, and the PF contribution, which should be 12% (Rs 2480), is showing as Rs 780. Please advise.

Thanks,
Raghuvir

Raghuvir,

As per the rule, the employer needs to pay only up to 12% of Rs 6,500 if the basic pay is above Rs 6,500. Additionally, the employee has two options:
1. Pay 12% of the basic pay or just 12% of Rs 6,500 if their basic pay falls above Rs 6,500.
2. Make voluntary contributions too. However, the employer only needs to adhere up to the ceiling specified as per the PF Act, i.e., Rs 6,500.

Let me know if this is not correct. Thank you.

Thanks,
Bindu
[Login to view]

Good work! Although you can improve it further by adding more allowances, the name of the employee, other details along with the company name to make it more presentable and professional.

To Bindu:

PF deduction issue: The rule is that whatever the Basic + DA of the existing employee (for PF contribution purposes), the employer has to pay 12% along with the employee's share of 12% with other admin expenses.

The second point is that the employer will contribute 8.33% to the pension fund up to Rs. 541/-, and the rest of the money will be diverted to the employees' provident fund.

Hence, whether the employee earned basic + DA up to Rs. 6,500 or Rs. 10,000/-, the employer has to pay 12% plus admin charges (0.5% and 1.1%).

I hope I made it all clear.

Manish Gupta

Thanks, Manish,

Since the very purpose of this tool is to arrive at CTC breakup, the total contribution of 12% by the employer is considered irrespective of the fact that the said proportion will be apportioned among EPF, Pension Fund + Administrative Charges (additional).

Best,
Dada

Good Morning Dada,

It's a good calculator. However, I need to make the following comments:

1) LTA is part of CTC.
2) HRA can be 40% or 50% of Basic due to the tax-free facility.
3) Car Lease option is not common to all employees, except for senior-level recruitment.

The rest seems to be okay.

Thank you,
CVH

Hi Raghu,

As far as PF is concerned, you have the option to contribute on actual basic pay or on the maximum ceiling of Rs 6500. It depends on the company which one to choose because there are costs involved as the employer has to bear admin, inspection, and PF charges.

Sanjeev

Hi all,

Everyone says that it is very nice steps. May I know how it is good and useful to all? If you calculate the amount on 14500 pm - Employee Contribution 1.75% = Rs. 254 and Employers @ 4.75% = Rs. 689, the total amount will be Rs. 943. Every month it means 943 * 12 = Rs. 11316 per annum.

If you see from the point of view of facility, ESIC does not provide good service for the same. For example, if you go for any treatment, you will wait for 1 to 2 hours, and then they will forward you to another doctor or department. After that, you will go to the dispensary to take your medicine, which will take 1 to 2 hours in the queue.

One question to all of you: How many employees take benefits from ESIC?

In my view, only 25%. I was covered by ESIC for around 2 years but did not go for any treatment during that period.

If you invest such an amount in Mediclaim, you can get more and better facilities compared to ESIC. You will only invest around Rs. 3000 per annum.

I am not a client of any Mediclaim company but an employee of a private limited company as an HR Executive.

In my view, it is not good for us. What are your views? Please reply to us.

Mr. Jagnesh,

Thank you for sharing the salary sheet. It is really helpful for us. However, I am unable to work on it because the sheet is protected and requires a password to be unprotected. Could you please advise the password?

Thanks,
Ravi

Dear all,

Thank you for your compliments.

First of all, let me clarify one thing: I am not a professional Advanced Excel programmer. I am an amateur creator of Advanced Excel. Basically, I am a B.E. (Electrical Engineering) graduate working with R.P.G. Groups. I created all the Excel sheets in my leisure time to streamline our routine job work.

All my programs are user-friendly. You can put your company name/logo on any sheet, and there is no indication of its creator on any sheet (except the Main sheet), and all sheets are free. Upon closer inspection, you will find that I have not provided any password for formulas. You can view all logic in the taskbar. Additionally, all data entry cells have no password.

I have protected the sheets with a password for two reasons:

1. To optimize the performance of Excel, there are logic formulas that should remain hidden; otherwise, unauthorized entries in columns will disrupt the logic of the programs and may lead to incorrect results.

2. There are links from cells to other cells or sheets. To safeguard these links, we should not add or delete rows or columns in any sheet. If someone adds a new row or column, there is a risk of damaging the logic formulas (as the user might not know where I have placed logical formulas), which is why I have protected the sheets to prevent unauthorized additions or deletions.

3. For instance, if I have declared the file name "XYZ" for cells A1:A200, and someone adds a new row or copies and pastes the previous row, the logic may not function as expected because I have only declared cells 1 to 200 in my logic. Therefore, the program may not work correctly or may give incorrect values.

4. You cannot copy, paste, add, or delete new rows/columns due to the presence of formulas.

If a sheet is not password-protected and someone accidentally enters data in a logic cell or deletes a formula, it can be challenging to identify the error. Hence, I have implemented password protection to prevent unwanted entries in formula cells.

Unlike most professional creators who use passwords and offer demo versions to users, my programs are all full versions. The sole reason for the password protection is to avoid the issues mentioned above, not to charge or monopolize.

I have received some emails regarding the need for round figures in the programs. Some users require data in rounded figures, so I have made the necessary corrections and re-uploaded them on my website. If anyone needs such adjustments, please download them from the site.

I hope this provides a clearer explanation regarding the password. Additionally, for those looking to enhance their Excel skills, I have uploaded 200 books/materials for Excel programming, macros, and VBA on the aforementioned site, all available for free.

Thank you.

Hi,

Thank you; that is really useful. Could you also please provide details on the taxable and non-taxable parts of the salary structure? I need to create a salary breakup for a senior position level and would like to present it in a way that maximizes tax benefits.

Please advise.

Ruby

Mr. Jigu, you have protected your sheet. If I have to use this, then how will I unprotect it? I don't know the password. Please reply.

Please find the revised program. In this, you will change or delete categories of the Salary Structure as per your requirements. You can also hide or format data as per your requirements. This program is more user-friendly compared to the previous one.

@premierindia

Request to read all instructions/directions given in the program - "May I Help You Sheet". This will guide you on how to enter data in the program.

Hello Sanjeev,

Can you please advise if the employer's contribution towards PF is 12% of basic + DA or 12% of basic only? In other words, if the basic pay is Rs 4000 and DA is Rs 3000, does the employer need to contribute 12% of Rs 4000 or 12% of the Rs 6500 ceiling considering basic + DA?

Thanks,
Raghuvir

Hello CVH,

Can you please confirm again that HRA is tax-free and does not fall under income tax? Is there a ceiling in Rs or % of basic for HRA?

Thanks,
Raghuvir

Request to ignore my previous program posted today and find attached the new one.

New sheet with necessary amendments. Search salary slip either by employee name or employee code.

I have a question for seniors. I work in a BPO in a UK payroll process. Will that knowledge help me in any way in the Indian payroll system?

Hello Sir,

The salary structure calculator is excellent. This tool is extremely useful for newcomers like me.

Swapnaja.

Hi Parmar!

Thank you for the nice posting, but the attachment is protected. Could you please advise on how we can make use of this Salary Mater if it is read-only?

Regards,
Pushpendra

For editing of the data, there is no need for any password as the editing area is not protected. Please read the instructions given with the program.

Path:
Main Program - "May I help you sheet" - Read all instructions.

Make necessary changes in the white background cells.

Dear Dschakri,

Thank you for pointing out such types of bugs. Please find the rectified program herewith. Don't hesitate to point out such types of bugs in the program if you find any in the future.

Thanks again...

Dear Jignesh N. Parmer,

Can you please clear my doubt about the basic minimum wages? Some factories are fixing the basic wages lower than the government-fixed minimum wage, but the total salary of the worker is more than what the government fixed. In garment industries in C zone, the helper's salary is Rs. 1825/- plus DA 741, totaling Rs. 2566. However, the factories fixed the basic as 1200 + DA 741 + HRA 700, totaling Rs. 2641. Why are they doing this practice to reduce their PF contribution? Is this correct, or should they take the government minimum as Basic, i.e., 1825 + DA for PF contribution?

Please clarify.

Thanks,

Best Regards,

Subiah G.

Dear Ashok, Upinder 79, and Mrs. Rani Chakravarty,

Please let me know why you need a password! You can easily enter, modify, and delete your data. Each and every cell for entering data for the user end is unprotected. You can easily remove the default details I provided and just enter your own details.

I have also provided directions on how to use this program, so please read the instructions carefully before coming to any conclusions. If you encounter any difficulties, please let me know.

Dear sir,

Please let me know in which sheet you find such type of difficulty. Please note that in the summary sheet, I intentionally gave a white background color.

1. In the summary sheet, we don't need an entry because it is a summary sheet.
2. If I gave a background color and if you need to print out that summary sheet (normally possible), the printout will look very odd due to the dark background, and it will also highly consume your printer cartridge.

So, if you find any difficulties entering data without the summary sheet/salary sheet/annual sheet, please let me know the sheet number.

Dear all,

Please find the following steps to run the program. Please follow the steps in the given sequence.

First, open "Company Detail Sheet"

1. Open "Company Detail Sheet"
2. Change the Company name and Address.
3. Change Salary descriptions: By default, I have provided some categories for Fixed earning/Reimbursement/Other deductions. Change the names of these categories as per your requirements.
4. As you enter details mentioned in step 3, the names of the categories will appear in the auto-generated salary section just below the Salary description. Enter the percentage as per your company requirements to generate the salary of all employees. (By default, I set Basic = 25% of gross. You can change the percentage as needed.)
5. Enter the % of ESI from Employee and Employer, and for P.F., choose your options.
6. For the Professional tax slab, enter your criteria.
7. Enter the Names of Departments/Designations of Employees in your Organizations.
8. Enter the Tax Slab for men and women.

Second, open "Master Data Sheet"

1. Enter Employee name-gender-employee code.
2. Select his/her designation-department.
3. Enter Pan no-Bank A/C No-Bank name-P.F no-ESI No-DOB-DOJ-Location.
4. Enter Employees Gross salary/Month.
5. If you choose Auto mode, the Salary slip will be generated automatically as per the data provided in the "Company detail sheet" (normally for Regular Employees, use Auto mode). If you prefer manual mode, enter his/her Basic-HRA and all other salary details manually.
6. Enter the Amount of OT/Hr if Employee does OT.
7. Enter leave details, Previous Year Leave-This Year Leaves.
8. Enter Rent paid by Employee for exemption under section 10 & 17.
9. Enter Amount for section 80c and 80D for U/S VIA deduction.

Third, open "Attendance sheet according to a particular Month"

1. Enter Attendance/Leave/Holiday of Employee.
2. Enter Overtime of The Employee.

Fourth, open "Payroll sheet according to a particular Month"

1. All salary details and attendance will automatically carry forward. You only need to enter reimbursement details and other deduction details.
2. Salary will be generated based on your data.
3. Enter the Salary Amount that you have Paid.
4. Select the Type of Payment.

Most of the remaining sheets are monitoring or summary sheets.

In which sheet have you found difficulty? Have you followed the above instructions? Please let me know the sheet number.

Hi Dada,

I tried to enter as you advised, but there are certain fields which we don't use for all employees. For example, in our company, the salary structure does not include providing the basic salary as a clear percentage of the gross. Because of that, when I enter the data in the master sheet, the basic column is password-protected. Please tell me the password to update the changes.

Thanks,
Shemy
shemylaila@gmail.com

Dear Shemy,

In the "Master Data Sheet," put such type of employee on "Manual mode" and enter your data in (just besides the "Manual" column) Manual Fixed salary and Manual Fixed deduction cells (here salary details don't concern with basic or other things).

Please note that if you put such type of employee on Auto mode, then the program will not allow you to enter data in Manual Fixed salary and Manual Fixed deduction cells.

Is it necessary to deduct PF for allowances like HRA, conveyance, overtime, etc.? Please confirm.
Awaiting eagerly for a reply.

Dear Jiguparmar,

I have a few doubts about your Salary Structure Calculator. Assume we have 200 employees in our company as of January's opening balance, and 10 employees resigned or absconded in the same month. Therefore, the total count is 190 for the closing balance. In your method, the salary for February, March, and so on, will be calculated for 200 employees instead of 190. The names of resigned employees will also appear for all months because the employees' names and other related details for salary and attendance are derived from the "DATA" sheet formula. Please try to solve this issue.

Regards,
Senthil
HR

Dear Mansi,

Have you read the instructions given with the program? First, carefully read the instructions provided both within the program ("May I help You" Sheet) and in the text file. Read the conversation on pages 9, 10, and 11 of the same thread. After that, try entering your data and let me know if you encounter any difficulties.

Dear Jiguparmar,

Could you please change the formula in all salary sheets AH and AI columns to 15000 instead of 10000 for ESI limits? This adjustment is necessary as the ESI limits have been raised from 10000 to 15000.

Regards, Senthil Kumar.A.S

Dear,

You did very well; I appreciate you. But I want to know, what formula should we use if we are calculating PF on basic which has no limit of Rs 6500? Also, how can we change the percentage if it does not depend on gross? For example, Basic is 25% of Gross, but HRA is 15% of Basic.

Regards,
Dilip

Hi,

Here are some problems regarding the ESIC amount. If we input a gross amount of 15000, the ESIC amount is not being calculated. Kindly provide a solution on how to calculate half-day salary in this format.

For PF, if PF is not deducted by any employee, then what if employer contributions of ESIC and PF are made by the employer, then what to do, and the same should appear in Form 3 and 6 of PF. Please explain.

Thanks,
Anand

Thank you for the post, Mr. Jiguparmar. However, the file is not opening. Could you please provide a new link for the PF & ESI calculation/Salary Master?

Regards,
Abhishek

This is a very useful website for buddies like me. I downloaded a salary calculator software a few days ago, but the problem with this software is that our company doesn't have a constant basic percentage or HRA percentage, so I can't use this one. The coding has been made so that we can't edit it. Kindly help me in my case.

Regards, Suresh Babu

Hi Priya Tyagi, I just want to confirm that in your sheet, the PF contribution from the employer is 12%, but it should be 13.6%. Could you please confirm this for me? I apologize if I am incorrect.

Thank you.

Regards,
Priya Tyagi

Hi, I want a salary breakup calculator in which I only have to input the annual CTC. Please refer to the format below, and could someone please create it accordingly. Regards, Suman Sr. HR

Hi, it is possible that some managements include PF contributions from both the employee and employer, totaling 12% as mentioned in the CTC. This is based on the basic slab deductions for PF.

Since the PF ceiling limit has been changed, can anyone help me out and provide an example of salary calculation with all components?

---

I have corrected the spelling and grammar errors in your text and adjusted the paragraph formatting for clarity. If you have any more questions or need further assistance, feel free to ask.

Thank you for your message.

I need to clarify one fact: If the employee's basic salary is 15000 /- and the PF deduction is 12% of 15000, is the deduction amount 1800 or 12% of 6500 which equals 780?

Please let me know.

Hi all,

Can anyone help me with calculating the salary breakdown in Excel to prepare an offer for a candidate? I am looking for a feature where inputting the gross CTC will automatically generate the breakup. Please assist.

Anumitha

Hi,

Is this sheet prepared according to the minimum wages of every state in India? For example, in Punjab, Haryana, and Chandigarh, do we need to ensure that the basic salary aligns with the minimum wage for all categories of workers - Skilled, Highly Skilled, Semi-Skilled, and Un-Skilled? Please confirm.

Additionally, how can we determine if Professional Tax (PT) is being calculated in states where it is applicable?

Thank you.

Hi team,

Could someone guide me on income tax calculation based on income and share with me the formulated Excel sheet? I also need assistance with current year payroll calculation. Thank you.

Dear Sir,

Your salary calculator is excellent for use. Today, I started using it. However, I have a doubt about it. In the attendance for this month (March 2019), the date is not correct. It starts with the 1st date as Monday. How can I change it? I even changed it in the first sheet to the year 2019.

Please guide me.

Thank you.