Start Management in Practice How to Calculate Payroll in Excel

# How to Calculate Payroll in Excel

5
23306

What is: The payroll is a monthly, weekly or daily list of workers' compensation of an institution. It is a set of labor procedures done by a company to carry out the payment of employees.

Why organize: Organizing the payroll of your company's employees can be a great challenge, even for those who are already an experienced entrepreneur. Complex tools available on the market can be more of a hindrance than helping.

## How to Calculate the Payroll?

The basic concept of payroll calculation is quite simple, it is the total paid by the company by an employee according to the agreed salary. That is, at first it would be something that should not change much from month to month, right? However, employees hired via CLT have several types of discounts that will influence the final amount and should be counted monthly to ensure the amounts paid are correct. In fact, the simplified formula would be:

Compensation = Base salary + Additions - Discounts

Items that can “increase” your pay:

Items that can “decrease” your remuneration:

We will now see how each of these points influences the calculating your payroll individually.

### Calculation of Overtime

Overtime is calculated according to the hours the employee worked in addition to his or her contracted working hours. That is, if the time is from 9 am to 17 pm and the employee stayed until 22 pm, it means that he worked 5 overtime hours that day. Hours should always be counted towards the total calculation at the end of the month. Some companies even have an hour bank system so that employees can also “spend” overtime on days when they want to work less hours. For that, it is recommended to use a point system and also a spreadsheet for calculating overtime / point control.

O overtime calculation must be done based on the employee's hourly wage. In addition, there is usually an extra per hour, typically ranging from 50%, 100% or 150% of base time. In a practical example:

Base salary = R \$ 2000

Base Time = R \$ 2000 / 220 hours = R \$ 9,10

Extra Time with 50% = R \$ 13,64

In this case, with 5 overtime due, the employee should have an 5 add-in x R \$ 13,64 = R \$ 68,20

This addition is only applicable to employees who have the shift between 22h and 5h the next day. O extra night does not vary monthly but you should pay attention to include it in employees with different shifts in your payroll. Its value is 20% based on the base time.

### Holidays

The holidays have a very special behavior and we have a specific post talking about all its details. Anyway, in a simple employee of the case 30 withdraw their vacation days at once, you just have to add a 1 / 3 of base salary to be paid in the previous sheet to the enjoyment of the holiday. That is, holidays differently from normal salary, are paid in advance and not at the end of the holiday.

Base salary = R \$ 2.100

Additional vacation = R \$ 2.100 / 3,33 = R \$ 700

Payment in the month prior to vacation = R \$ 2.700

### Thirteenth

The thirteenth salary is also calculated using the base salary and must be paid regardless of the months that the employee worked for the company and can be paid in two installments, one to 30 November and the other to 20 December. Many companies, for cash flow reasons, pay half in the middle of the year and the second installment in December. When the employee is less than a year in the company, he should receive the proportional in months. As this calculation is quite easy, we will see a case of an employee who is only 6 months in the company.

Base salary: R\$ 2000

Thirteenth = R \$ 2000 / 2 = R \$ 1000

Remembering that the value can be paid or not in two installments, this is in charge of the company.

### Unhealthy / Hazardous

These are very specific additions to activities that expose the employee to health risks. As well as nocturnal addition, its calculation is very simple. The additional unhealthy is 20% and the dangerousness 30% on the base salary. However, it is important to note that they are not cumulative, that is, the employee who is entitled to both should opt for the one that gives him the most benefits, this is the additional hazard. We also have a specific post with more details and cases on unhealthy / hazardous.

### Weekly Paid Break (DSR)

The DSR payment is one of the strangest payroll calculations since it is basically an extra pay for what has already been paid overtime. That is, it is the additional additional one. However, its calculation is relatively simple:

Total Value of Extra Hours / weekdays of the month x amount of Sundays and holidays

Let's take the case of overtime in a month with 22 business days and 5 Sundays / holidays.

R \$ 68,20 / 22 = R \$ 3,10

R \$ 3,10 x 5 = R \$ 15,50

### Vacation payment (Pecuniário)

This is the case of the vacation sale. This means that you can exchange 1 / 3 for your holidays, meaning 10 days for more money, in case you do not want to enjoy the rest. This is usually solved along with the holidays, so let's see a case in which you decide to just take 20 holiday days:

Base salary: R \$ 2.100

1 / 3 Vacation Wage: R \$ 700

Remuneration of 10 working days = R \$ 2.100 / 30 = 70 x 10 days = R \$ 700

Remuneration = R \$ 2.100 + R \$ 700 + R \$ 700 - R \$ 3.500

This is a common practice in Brazil although it is not advisable in terms of financial management for both the employee and the employer. In any case, if your company has this policy, the advances must be deducted from the next salary to be credited to the current payment, always using the base salary as a measure.

### Faults and delays

The shortages and delays can be deducted from the salary just as it is done with the overtime. You should use hourly wages as a basis for calculations, and if you want to be thorough, you can even convert it to hourly wages, although the effort often does not pay. In case of use of time bank, can also be discounted at the bank and paid overtime, this will depend on the company's policy.

### Transportation vouchers

The transportation voucher is a mandatory benefit, in the case the employee applies, which is paid by both employee and employer. From the employee's salary, typically 6% of base salary is discounted. In case the benefit is of greater value, this cost will be paid by the employee.

The voucher must be used exclusively for travel to the place of work and can not be transferred or sold to other persons.

## INSS

The INSS table is updated annually and should always be updated either in your system or worksheet used to make the payroll. In 2018, the table is the following according to the base salary. Remembering that this amount is deducted from the employee.

 Contribution Salary (R \$) Aliquot Up to \$ 1.693,72 8% From R \$ 1.693,73 to R \$ 2.822,90 9% From R \$ 2.822,91 to R \$ 5.645,80 11%

For salaries above R \$ 5.645,80 onwards, you must pay the contribution ceiling in the amount of R \$ 621,04.

### FGTS

FGTS is one of the simplest and most reliable calculations of payroll. Basically, the employer has to deposit 8% of base salary as future benefit to the employee. Although this does not lead to a greater gain or loss to the employee, this amount is always detailed in the checkbook (holerite) for knowledge.

### IRRF

The calculation of the income tax that must be withheld at source is based on the salary deducted from the INSS contribution and also on the employees' dependency. That is, let's take the case of someone who earns \$ 3.000 and has a child.

Base salary minus the INSS of 9% = R \$ 2.730

In the case of the dependent, R \$ 189,59 must be deducted. Thus, the tax will be calculated over the amount of R \$ 2.540,41

• 1ª range: 7,5% for bases from R \$ 1.903,99 to R \$ 2.826,65;
• 2ª range: 15% for bases from R \$ 2.826,66 to R \$ 3.751,05;
• 3ª range: 22,5% for bases from R \$ 3.751,06 to R \$ 4.664,68;
• 4th track: 27,5% for bases starting at \$ 4.664,69.

Those who receive less than R \$ 1.903,99 are exempt from tax. As our case is in the 1ª range, the calculation would be 7,5% of R \$ 2.540,41 = R \$ 190,53

However, for each track there is also a standard reduction as follows:

• 1th track: R \$ 142,80;
• 2th track: R \$ 354,80;
• 3th track: R \$ 636,13;
• 4th track: R \$ 869,36.

Thus, for the purpose of calculating the payroll, the amount withheld would be R \$ 190,53 - R \$ 142,80 = R \$ 47,73

### Health insurance

In many companies there is the benefit of the health plan, however this benefit is not mandatory. Although in many cases the company will bear the cost of the plan in full, in others this is shared with the employee, so the option of the health plan of your company can also affect the calculation of the payroll.

### Union Contribution

Although the union contribution has ceased to be mandatory, there are still many classes, professionals and companies that see value in continuing to contribute. Therefore, this amount is deducted from the payroll once a year, and the value and discount month can vary from union to union.

## How to Calculate Your Payroll in Excel

One way to organize payroll in an easy and fast way is through a payroll-specific worksheet - the Sheet of Payroll in Excel.

This worksheet will help you to organize and automate all of the calculations explained above, as well as generate each employee's paycheck (counterpart) and generate some indicators about your costs over the months, which most systems or manual calculation will never give you. Therefore, we will see below the step by step of construction and also of use of the payroll in excel.

### 1 Step: Employees Registration

The first step for any team management tool, of course, is to define who this team is, so it is interesting in the spreadsheet to have a general employee record tab. In the photo above, we have shown a few data that should have this list, but it is much larger and should include items such as:

1. Full name
3. Resignation date
4. Department
5. Base salary
7. Phone / Email
8. RG / CPF
10. Salary Account
11. Dependents

### 2: Tables for Calculations

To really automate the worksheet and gain time and also reliability in the calculations, we insert in our worksheet a tab with the tax rates that can be updated every year to keep the current worksheet.

Apart from the basic charges, the next table to be filled is the INSS (National Social Security Institute). This is the rate that will be deducted from the salary of the employee of the company and destined to the INSS. Then, the second table refers to the IRRF, that is, the income tax rate that must be withheld at source. In the third table, the entrepreneur must fulfill the other charges that the company must pay. Basically, these are undiscounted values ​​of the employee's salary. Watch out to see if your tax rates are up to date.

### 3 Step: Monthly Payroll Release

With employees already properly registered, it is time to go to the payroll itself. This part of the worksheet is separated by month and in it you will automatically have the list of previously filled employees as well as the callsign if it receives 13 salary or vacation for that month.

For each employee, you will fill in the extra hours like extra hours, nightly extras, discounts and more! This is the basis of the worksheet and now you have already filled in all the information you need to automatically generate your payroll.

### Step 4: Payroll Cost Reports in Excel

The next interesting content that a spreadsheet for excel worksheet will provide you is a detailed analysis of each type of cost that makes up your payroll and its evolution per month.

This kind of automatic and accurate content will help you quickly see if any of your company's people management practices are generating excessive costs as well as opportunities for improvement. Of course, many of the costs are mandatory and can not be changed, but the use and consequent overtime pay is a classic cost invisible in business that with this worksheet will be clear to be analyzed.

### Step 5: Issue and Print Holerites (Checks)

After you make your payroll it is important that you can pass this data on to your employees so they understand the discounts, salaries, and bonuses that make up your salaries. For this you can print payment slip, known as pay stubs, in two ways, one for you and another for your employee, thus showing that the company fulfills its tax and labor obligations.

If you use a Worksheet to make your payroll, you can use her data to make your Holerite. Our Sheet of Payroll, for example, automatically generates pay stubs for your employees.

In addition to the detailed monthly sheet slip, the spreadsheet also features a holiday receipt slip and also a thirteenth salary, both of which are important vouchers for your personal department control.

## Payroll template in Excel

The examples in this post were taken from our own payroll worksheet in excel. It is a product that allows you to manage up to 50 sheets per month, performing all calculations and discounts automatically, as well as creating reports and charts.

Have you seen how organizing the payroll is important to your company? The Spreadsheet Light has a payroll worksheet automated, facilitating this service! Buy now and make good deals!