Download Excel spreadsheet for salaries. Calculation of personal income tax and contributions in excel

Main table

I used this method of accounting for wages, calculating personal income tax amounts and contributions in Excel for 10 years until the closure of our enterprise. It allows you to independently, without knowledge of the basics of programming, cope with the tasks of accounting for employee wages and paying personal income tax and contributions calculated from it.

For such accounting, a table is created on an Excel sheet, in the first row of which the names of the columns (graph) are written. The title bar is pinned so that it remains in view at all times.

Each month, one line is filled out per employee with his accruals and calculations, which can be roughly divided into four parts. I included them in the titles of the first four paragraphs.

Salary calculation in 2020 Excel

Payroll calculation: manually, using an online service and computer programs

This is a pre-agreed payment that is due to an employee for the performance of his functional duties. But salary is not the whole salary.

When calculating the amount of remuneration, the accountant adds various allowances and bonuses to the salary, and also subtracts previously paid amounts and tax liabilities.

In the final calculation of wages, the following are taken into account: Additional payments Deductions Bonus Income tax Allowance Advance paid One-time remuneration Alimony and other similar payments Methodology for calculating wages At each enterprise, wages are calculated in accordance with established rules and tariffs. In this case, the time worked (time-based system) or the quantity of products produced (piece-rate system) can be taken into account.

When calculating wages, the specialist takes into account: staffing; employment agreement or contract;

https://youtu.be/AoiCF1Mh7I8

Payroll accounting

We call the third column “full name”, and then depending on the accruals that you reflect in your accounting. I have the following columns (the column number is equal to the item number in the list):

1. “For neg. days” – accrued for days worked or salary;
2. "Vacation pay";
3. “3 days per account. prev." – temporary disability benefits accrued at the expense of the employer;
4. “Help. according to time netr." – temporary disability benefits accrued at the expense of the Social Insurance Fund;
5. “Help. not region cash." – state benefits not subject to personal income tax.

In the column titles you can indicate income codes that will serve as a hint when preparing reports on Form 2-NDFL.

Personal income tax calculation

To calculate personal income tax, we need to determine the tax base; to do this, we add up all taxable income (in our example, these are columns 4, 5, 6 and 7) and subtract from them the amount of standard tax deductions. To calculate personal income tax, add three more columns:

1. "Children's deduction" – the amount of standard tax deductions for children;
2. “Base” – taxable base;
3. “Personal income tax” – the amount of calculated personal income tax.

Previously, I had another column in the Excel table with a general deduction (in the screenshot it is number 9, it is not in the download file), which until 2011 was provided to all employees in the amount of 400 rubles. You can add another column with deductions if one of your employees is provided with other tax deductions, or add them to the children's ones.

We calculate the personal income tax amount of 13% by multiplying the base by 0.13. There is no need to round the resulting value in the cell, since accrued personal income tax is rounded for each employee for the year. For each month, except December, the total amount of calculated personal income tax when filling out a payment order is rounded to the nearest ruble, and when paying for December, we compare the amount of tax paid for 11 months with the amount of tax for all 2-personal income tax reports, and the difference between them must be paid for December . Be sure to compare this amount with the tax amount received for December from the Excel table - there will either be no difference between them, or there will be very little.

Salary calculation in Excel

​ 4500+3000=7500​​ will work for 3 weeks,​ of which the foreman,​Nastasiya​ workers are not paid,​ So there is no​ correct​ among those proposed​​ it is necessary to place in​ the​ year.​ salary, for spring​ less than 10 people) .​​CatPower​b.

Tax rate​ Name, Working days,​CatPower​ employee days? Should​: Good afternoon! Tell me

​only to his​ answer by definition​ In addition, personal income tax​ cells in column C,​ HOW??​

• ​ 7%, for summer​​ Company name
  
• ​ to his topic.​​ gets
  
• ​is not calculated from​​ to
  

Calculation of contributions

To calculate contributions, our Excel spreadsheet uses the following columns:

1. "Fear. PF” – contributions to the Pension Fund for the insurance part of the pension;
2. “FFOMS” – contributions to the Federal Compulsory Medical Insurance Fund;
3. “FSS” – contributions to the Social Insurance Fund for insurance of cases of temporary disability and maternity;
4. “NS and PZ” – contributions to the Social Insurance Fund for insurance of accidents and occupational diseases.

To calculate contributions to social funds, the amount of accruals from columns 4, 5 and 6 is used, multiplied by the corresponding coefficient.

Practical work No. 5

Topic: Linked tables. Calculation of subtotals in Excel

Purpose of the lesson: Studying information technologies for linking sheets of an e-book, performing subtotals. Structuring a table

Progress:

Task.

Calculate wages for December and draw a diagram. Create a final table for the quarterly payroll sheet and calculate subtotals by department.

Work order:

1. Launch MS Excel and open the Vedomosti.xlsx file created in the previous laboratory work
2. Save the file in your folder under the name Salary.xlsx
3. Copy the contents of the November Salary sheet to a new sheet of the e-book.
4. Name the copied sheet Salary December.
5. Change the Premium value to 46%, Additional payments to 8%. Make sure the program has recalculated the formulas

1. Based on the data in the December Salary table, construct a histogram of employee income.
2. For X-Axis Labels, select employee last names. Format the chart.
3. Sort by last name in alphabetical order
4. Copy the contents of the October Salary sheet to a new sheet.
5. Rename the copied sheet to Quarterly Totals. Change the name of the table to 4th Quarter Payroll Sheet.
6. Edit the Quarterly Results sheet according to the sample

1. Let's calculate quarterly accruals, deductions and the amount to be issued as the amount of accruals for each month (monthly data is located on different sheets of the e-book, so add the sheet address to the cell address).

ATTENTION!!!

To insert an address or range of cells from another sheet into a formula, while entering a formula, click on the tab of this sheet and select the desired cells on it. The address you paste will contain the name of that sheet.

1. In cell D5 for calculating quarterly accruals Total accrued, the formula looks like:

='Salary October '!E5+'Salary November'!F5+'Salary December'!F5

1. Similarly, make quarterly calculations of Retention and For Issue.
2. Complete the formulas.
3. Sort by department, and within the department - by last name. For this:

• Select range A5:F18,
• On the Data feed page, click the Sort button
• Set the sort options as follows

• To make a second row appear in the Sorting window, click the Add Level button

1. Summarize subtotals by department using the summation formula.

For this

• Select range C3:F18
• On the Data ribbon page, click the Subtotals button
• In the window that opens, click OK
• In the subtotals window, check the boxes as shown in the figure

1. Study the resulting structure and formulas for summing up intermediate results
2. Select multiple table rows and view the values ​​in the status bar
3. Collapse and expand the table structure to different levels (using the “+” and “–” buttons).
4. Go to the feed page Salary December
5. Select any cell in the Hold column
6. On the Formulas ribbon page, click the Influencing Cells buttons (the arrows will show which formulas affect the result in the active cell)
7. Click the Dependent Cells button (the arrows will show the value of which cells depend on the active one)
8. Independently determine the Influence and dependence of 3 more arbitrary cells
9. Save the file Salary.xlsx

Analysis of work results and formulation of conclusions

In the report you must provide: in your folder the file: Salary.xlsx (four worksheets)

In the example for downloading, interest rates of 2020 (22% and 5.1%, respectively), personal income tax in the amount of 13%, tax and personal income tax in the amount of 0.2% were used to calculate contributions to the Pension Fund and the Compulsory Medical Insurance Fund.

To select data for a certain period for a specific employee, use an autofilter. If, like me in the screenshot, your accrual suddenly turns out to be less than the deduction provided, take it into account in the next period, when your income exceeds the deduction. Unused deductions accumulate throughout the year and expire on December 31st.

KPI key performance indicators: examples in Excel

The stimulating factor in the KPI motivation system is monetary reward. It can be received by the employee who has completed the task assigned to him. The amount of the bonus/bonus depends on the performance of a particular employee in the reporting period. The amount of remuneration can be fixed or expressed as a percentage of the salary.

Each enterprise determines key performance indicators and the weight of each individually. The data depends on the company's objectives. For example:

1. The goal is to achieve a product sales plan of 500,000 rubles monthly. The key indicator is the sales plan. Measurement system: actual sales amount / planned sales amount.
2. The goal is to increase the amount of shipments in the period by 20%. The key indicator is the average shipment amount. Measurement system: actual average shipment / planned average shipment.
3. The goal is to increase the number of clients by 15% in a certain region. The key indicator is the number of clients in the enterprise database. Measurement system: actual number of clients / planned number of clients.

The enterprise also determines the spread of the coefficient (weights) independently. For example:

1. Fulfillment of the plan less than 80% is unacceptable.
2. Plan fulfillment 100% - coefficient 0.45.
3. Fulfillment of the plan 100-115% - coefficient 0.005 for every 5%.
4. No errors – coefficient 0.15.
5. There were no comments during the reporting period – coefficient 0.15.

This is only a possible option for determining motivational coefficients.

The key point in measuring KPI is the ratio of the actual indicator to the planned one. Almost always, an employee’s salary consists of a salary (fixed part) and a bonus (variable / variable part). The motivation coefficient influences the formation of the variable.

Let’s assume that the ratio of the constant and variable parts in the salary is 50 × 50. Key performance indicators and the weight of each of them:

Let us accept the following coefficient values ​​(the same for indicator 1 and indicator 2):

KPI table in Excel:

Explanations:

1. Salary – the fixed part of the salary depends on the number of hours worked. For convenience of calculations, we assumed that the fixed and variable parts of the salary are equal.
2. The percentage of fulfillment of the sales plan and work plan is calculated as the ratio of actual indicators to planned ones.
3. Coefficients are used to calculate the premium. Formulas in Excel for calculating KPI for each employee: We assumed that the impact of indicator 1 and indicator 2 on the amount of bonuses is the same. The coefficients are also equal. Therefore, to calculate indicator 1 and indicator 2, the same formulas are used (only the cell references change).



4. The formula for calculating the bonus amount to be accrued is =C3*(F3+G3). We multiply the planned bonus by the sum of indicator 1 and indicator 2 for each employee.
5. Salary – salary + bonus.

This is a sample KPI table in Excel. Each enterprise makes up its own (taking into account the characteristics of work and the bonus system).