How to do the reverse salary calculation for CTC or Net Pay

How to do the reverse salary calculation for CTC or Net Pay

Salary Calculation

Whether you belong to Talent Acquisition, Finance, HR, Temp Staffing or an Individual. This excel calculator for “how to do the reverse salary calculation” will help you increase your efficiency to work faster.

Why you need the Salary Calculator?

Man thinking
How to Calculate Salary?

You often came across the situation where you want to calculate candidate’s/Employee Salary either by CTC or by Net Pay and wonder how to do the reverse salary calculation for CTC or Net Pay.

The calculation totally depends upon the company pay structure and compensation philosophy, however, it has all the necessary items including Income Tax calculation also. The Reverse Salary Calculator is useful for any industry, you can use it for your industry/ company as well. It does not have any statutory obligation, you can modify it as per your needs.

Hers is the tool to calculate “CTC(Cost to the company) basis Net Pay” or CTC basis Net Pay”. The link of the excel file (The Reverse Salary Calculator) is given below, you can download your copy for Free.

What is not included in it?

Employer Part

It does not include additional employer pay heads like Leave Encashment, Gratuity, Medical Insurance, Office Furniture Cost etc. As per the market survey, it is not a good practice to include these items into the CTC structure. While including these items in CTC sometimes misleads the discussion with the prospective candidate. Nowadays candidates are also aware that these items are included for some savings, “show the big picture to the candidate and hire them”. However, if you want to include them, then you need to modify the excel accordingly.

Employee Part

Deduction items like Professional Tax and Labor Welfare Fund, these are state specific deduction. However, you can manually fill the professional tax amount in the cell “B23“, Labor welfare fund is not necessary as the contribution is a bare minimum.

Setup a few parameters before you start working on it, “Select Yes” whichever is applicable.

  1. Variable Pay – Cell – “F5“.
  2. PF is deductible – Cell -“F6“.
  3. PF is on 15000/- Basic salary – Cell “F7“.

These mappings are essential for the formulas and calculations.

Minimum Wages

Minimum Wages
Minimum Wages India

The calculator has a separate sheet for Minimum Wages. It has all major States Minimum Wages rates. Those are State-specific rates for the skilled or highly skilled category. However, you can edit the amount as per your need. The formula in the basic cell is defined as 40% of CTC or Minimum Wages whichever is higher (Cell – “B11”)

Income Tax

It includes Income Tax calculation. It has all the essential formulas including “Surcharge Waiver” formula. By the time you download this file and just in case the tax rate changes for the current financial year, then you need to change the formulas manually by editing the cell “H25”, “H26” and “H27”.

HRA

HRA Allowance is considered 50% of Basic salary to get the right tax exemption for rent paid. It is based on the best practice that industries have, however, there are no statutory obligations to it. However, you can change it by editing the cell “B12” as per your needs.

Variable Pay

It includes variable pay, however, you can change the percentage by editing the cell “F5”. If there is no variable pay in the salary structure, then leave it as 0% only.

Selecting the Right State

Select the right “State” under “Select State” by changing cell “F8” in case you are calculating salaries for junior category employees/candidates.

If you want to calculate CTC based on “Net Pay” then use/change the cell “F3”.

If you want to calculate Net Pay based on “CTC” then use/change the cell “F4”.

Please make sure the iterative calculations are on, please follow the below steps to activate it.

How to activate Iterative Calculation?

[Excel>>Go to File>>Options>>Formulas>>check on “Enable Iterative Calculation”>>Click Ok]

Please comment your suggestions below!

Please click on the below button to download the excel file for how to do the reverse calculation for CTC or Net Pay.

Salary Calculator (67 downloads)

COMMENTS