Practical applications of spreadsheets Excel they are numerous. Not only in the professional field, but also for our domestic economy. In this post we are going to see one of those practical uses: Calculate net salary in Excel, a simple and precise way to know how much we are going to collect each month in our employee payroll.
Excel is an ideal tool for this type of calculation. In the case at hand, it helps us to know the gross salary and the net salary. All we need is to have the employment contract at hand, which includes the hours worked and other concepts to take into account. Afterwards, it is simply a matter of applying the appropriate formula. We explain everything below:
Difference Between Gross Salary and Net Salary
To answer the question "How much will I earn?" that all salaried workers ask themselves, it is important Know the concepts of gross salary and net salary. Both are reflected in the payroll, but they are not the same.
Image: nominapro.mx
If we want to explain it quickly, we can say that the gross salary is the total amount of money that the company pays the worker, but it is usually a greater amount than what he finally receives, which is the net salary.
Where does that difference come from? The gross salary includes the social contributions paid by the employee, a certain percentage of taxes that depends on each individual situation (in Spain it is called IRPF or Personal Income Tax) and, in some cases, also medical coverage or insurance. private, special incentives, allowances, company vehicle, etc. That is to say: Gross salary is compensation before taxes.
In Spain, the Social security it takes as a base the gross salary to calculate the pension or unemployment that is going to be received. For this reason, a series of concepts are retained as quotes:
- Quote for common contingencies (pensions and other benefits).
- Contribution for AT/EP (work accidents or occupational disease).
- Unemployment contribution.
- Vocational training.
On the other hand, some Deductions that depend on each worker and their personal circumstances. Here the following is taken into account:
- Civil status.
- Age.
- Professional category.
- Place of residence.
- Charges for minor children.
- handicaps.
- Seniors in charge.
- Elderly or dependent children with a disability.
What we will really see deposited in our bank account at the end of the month is the net salary, which is the result of subtracting the withholdings and deductions mentioned in the previous paragraph from the gross salary.
Knowing this difference between gross and net salary is essential so as not to make mistakes when accepting a job offer, requesting a loan or planning our daily economyInter alia.
Calculation of gross salary in Excel
There are two practical functions that we can use to calculate the gross salary in Excel: the SUM function and the PRODUCT function. This is how we should apply one and the other:
The SUMA function will help us in the event that we have several different types of concepts to be collected on our payroll. For example: base salary, bonuses for holidays or night shifts, overtime, etc. There are many different options. In our Excel sheet, it is about placing these concepts in the same column and inserting the formula in the result cell =SUM, to which we will add between parentheses and separated by commas the cells where the amounts to be added are.
The PRODUCT function helps us to calculate the gross salary for hours worked, since it allows us to multiply the values ​​of different cells. To carry out the calculation, we first need to arrange the hourly salary in one cell and, on the other hand, in another cell the number of hours worked in a certain period of time. Then we go to a blank cell and insert the formula =PRODUCT, after which we will add the cells corresponding to salary per hour and hours worked, between parentheses and separated by commas.
Calculation of net salary in Excel
Once we know the gross salary, calculating the net salary in Excel is a relatively simple process. Basically, it consists of taking the initial figure and subtracting taxes withholdings and other deductions. This is how we will get the final result, what we are really going to charge.
Since the deductions are amounts that we are going to subtract, their values ​​must necessarily be negative. However, we will use the function =SUM to indicate the cells or range of cells with negative values that we have to add This is how we are going to obtain the net salary.
Once we are familiar with using these functions in Excel, it will be very easy to create our own custom functions to calculate a salary (net or gross) in Excel. It is also possible to download default templates from some specialized web pages.