Payroll calculation in excel. How to do payroll in excel? It is easier to start working in the program than in Excel

Payroll calculation in excel. How to do payroll in excel? It is easier to start working in the program than in Excel
Payroll calculation in excel. How to do payroll in excel? It is easier to start working in the program than in Excel
10.02.2022

In the software market, there are often offers “we make programs to order”, or the like, for the Buchsoft Salary program no special programmer required. BuhSoft program is a ready-made solution that either already satisfies your requirements or requires little effort so that the features of your requirements would be taken into account in the program in the shortest possible time.

Write to us on your wishes, and we will tell you where it is already provided for in the program!

It is easier to start working in the program than in Excel!

Here are the actions that allow the program to calculate salaries, calculate all taxes (personal income tax and insurance premiums) and receive all the necessary documents:

  • Download and install the program
  • In the Organization Information, enter the name and rate for injury insurance premiums (all other data can be entered later)
  • In personnel data, enter your full name, date of birth and standard deductions, if any (other personnel data can be entered later)
  • In payroll, click the Accrue button (for all employees or for each).

As a result, all taxes from the payroll fund will be automatically calculated correctly and all the necessary documents for payroll will be filled out.

Here are some documents that the payroll program automatically fills out:

Automatic payroll calculation based on personnel data and time sheet

Of course, over time comes the need to demand even more automation from the program.

The convenience of the program lies in the fact that when permanent accruals are entered in personnel data, the calculation of salaries, deductions, and payroll taxes is performed in one action for the entire list of employees (the "List" button). At the same time, if the accrual relates to salary payment and the user maintains a time sheet, then the program automatically takes into account only the days worked. If the user's time sheet is not kept, then the salary is charged in full. Also, according to personnel data, the accrual of sick leave and vacation pay is automatically tracked. Thus, if there is initial information on the calculation of wages, the entire procedure for calculating wages and the formation of the necessary documents is carried out automatically in a few minutes, even at large enterprises.

If necessary, the program has the ability to make individual accruals through the personal account of the employee.

A wide range of customized accruals for payroll

The program automatically calculates almost all known accruals. For each accrual, the program has already made settings on how taxes should be calculated from it.

Here are some types of accruals provided for in the program:

  • salary payment, piecework payment;
  • permanent and one-time bonuses, allowances and additional payments;
  • royalties;
  • remuneration under a civil law contract;
  • temporary disability allowance and other benefits;
  • payment of annual (additional) holidays;
  • compensation payments;
  • accruals according to regional coefficients;
  • dividends;

For all accruals, adjustments were made to calculate the corresponding taxes and contributions.

If necessary, the user can add any own accrual and set up tax calculation.

One of the advantages of the BukhSoft: Salary program is that it provides for the automatic generation of certificates of income of individuals (employees) in the form of 2-NDFL. At the end of the year, when the salary for all months is calculated, the program will generate a file and copy it to a USB flash drive in full compliance with the requirements of the tax authorities.

We wish you pleasant work!

The program for payroll is designed to automate accounting in the organization. It is very simple and intuitive, has a user-friendly interface, so it is easy to work with. Our solution will help you save a lot of time and energy that you spend on accounting, reporting, payroll, paperwork and printing, and other related tasks. This will naturally lead to a reduction in time and money costs, and an increase in the profits of your business.

The program has three independent sections:

  • Payroll calculation, formation of a payroll, payroll, payslips, compiling a wage order journal.
  • Compilation of a turnover sheet for all accounts and sub-accounts of the organization based on order journals, formation of data in a summary table for posting to the general ledger, formation of a balance sheet for the period.
  • Importing bank statements from the client's bank into the program, compiling a journal of order No. 2 and statements to it.

This is an open source program.

All directories can be corrected and new ones can be uploaded.

Forms of directories, databases, other forms and documents, as well as calculation algorithms, can be adjusted and new ones can be created in the "Constructor" menu.

The program is useful for students as a visual aid for studying accounting and creating their own programs.

Main functions and features of the program

Reference books:

  • a list of employees indicating gender, division, position, salary, monthly bonus, class bonus, parental leave up to 1.5 years, other accruals, employee tax deductions, loan payments, writ of execution, trade union dues, payments for compensation for damages, other deductions, tax benefits;


  • list of positions of the organization;
  • list of departments of the organization;


  • reference book "Types of income" (Appendix No. 4 to the Tax card for accounting of income and income tax);
  • reference book "Types of deductions" (Appendix No. 5 to the Tax card for accounting of income and income tax) indicating the amount of the deduction;


  • established tax rates;

  • constants: billing month, number of working days in a month, working-age population subsistence level, eligible income limit, standard tax deductions, minimum wage for skilled workers, minimum wage for unskilled workers, estimated minimum wage for calculation of income tax, the estimated level of the minimum wage for calculating the unified social tax and the mandatory insurance contribution to the Unified State Social Insurance Fund for the purpose of pension provision, the estimated level of the minimum wage for indexation of alimony, the amount of standard tax deductions, the amount of material assistance not subject to income tax, non-taxable amount of material assistance with the unified social tax;

  • code assignment table (Appendix No. 3 to the Instruction of the Ministry of Finance of the PMR "On the procedure for calculating and paying the unified social tax and mandatory insurance premium");

  • types of accruals indicating the income tax code and the unified social tax code (for reporting on income tax and unified social tax);
  • types of deductions;


Payroll:

  • payroll for employees indicating the types of accruals, tax deductions, types of deductions, accruals of the unified social tax;




  • calculation for the month it generates the final data on accruals and deductions for the month;



  • consolidated payroll calculation (on the right side of the section, payroll is calculated for employees of the organization);
  • documents in OpenOffice Calc: payroll, pay slips;
  • Microsoft Office Excel documents: payroll;





  • ZhO 5: the date of the operation, the type of accrual, the type of deduction, the debit of the account, the credit of the account and the amount are reflected;
  • ZHO 5 per month: reflects the total turnover on salary accounts for the month;
  • journal order 5 (on the right side of the section posting is performed on accounts);
  • Microsoft Office Excel documents: order journal No. 2, order journal 5;




  • chart of accounts for financial and economic activities of organizations (Order of the Ministry of Finance of the PMR dated June 29, 2009 No. 169);
  • turnovers reflect the date, the amount of the transaction, the debit of the account and the credit of the account;
  • monthly turnover reflects the sum of turnovers on all accounts of the organization for the month;
  • Microsoft Office Excel Document: General Ledger;



  • general ledger (on the right side of the section posting is carried out according to accounts);


  • turnover balance sheet (in the columns of the balance at the beginning and the balance at the end, the value is (+) debit, the value (-) credit);


  • bank statements are imported from the bank-client file;
  • directory of banks operating on the territory of the PMR;


  • database of suppliers and buyers of the organization;
  • ZhO 2 for the month reflects the total turnover on the current account for the month;


  • journal order 2 is formed in the right part of the section (added from the bank statement);


  • chessboard bank, bank statements are formed by dates and debit of accounts;

Download Templates (.csv files):

  • bank statements;
  • constants;
  • charts of accounts;
  • employee handbook.

Brief instructions for working with the program

The first group of the program contains directories with which users work.

"Employees": the first tab contains basic information about the employee, the second tab contains deductions and taxes. The directory is better to download from the template. Open the template using OpenOffice Calc, fill in the details by copying from the forms available in the organization, save by selecting the "Use current format" button. In the program menu, click the "Import" button, select this template, click the "Open" button, in the "Data Import" window, click the "Import" button. Fill in the missing data manually. At the same time, the reference books will be filled: "Subdivisions" and "Positions".

Reference books: “Types of income”, “Types of deductions”, “Tax rates”, “UST codes” are filled in, they are corrected when regulatory legal acts are changed.

In the directory "Constants" the lines are filled in: "The subsistence minimum of TN", "Minimum wage of the KR", "Minimum wage of the NKR", "RU of the minimum wage for calculating income tax", "RU of the minimum wage for calculating the unified social tax, SALT", " RU MW for indexation of alimony”, the rest of the lines are calculated according to the formulas.

Reference books "Codes of accruals" and "Deductions" can be brought into line with those in force in the organization.

In the second group of the program, payroll is calculated.

"Payroll for employees" is generated automatically, is given for information, it can be hidden in the Constructor. However, in case of incorrect work in the program, it is possible to accrue salaries to an employee several times. In this section, input errors are clearly visible.

"Calculation for the month" in the form opens the next month.

"Summary payroll" in the form of payroll. The cursor is set to the month of calculation. On the right side of the form, the “Create” button, select an employee, select the month of accrual, fill in the lines for the number of days worked, depositors (debts of the organization), debts for the employee. Sick, vacation, one-time accruals and deductions are calculated separately and entered into the calculation. The rest of the data is filled in automatically. The Documents menu opens Payroll, Payroll, and Payslips.

"ZhO 5" is generated automatically, is given for information.

"JO 5 per month" in the form opens next month.

In the form "Journal order 5" posting is done in the accounts in the same way as payroll. In the "Documents" menu, "Journal Order 5" opens in a pivot table with filters. The table data is entered in the General Ledger form.

In the third group of the program, a turnover sheet is compiled for all accounts of the organization on the basis of order journals and other documents.

A balance sheet for the period and data for transfer to the general ledger of the organization are formed.

"Turnovers" are formed automatically, are given for information.

“Turnover per month” in the form opens next month.

"General ledger" in the form posting to accounts is carried out similarly to payroll. The "Documents" menu opens the "General Ledger" in a pivot table with filters. The table data is entered into the General Ledger of the organization.

"Turnover balance sheet" is generated automatically for a given period. At the bottom of the form, in the filter, set the dates “Start” and “End”. Press the "Apply" button.

In the fourth group of the program, bank statements are imported from the client's bank. A magazine order No. 2 and a statement to it are compiled.

"Bank statements" are loaded from the template. Upload bank statements from the client's bank in Microsoft Office Excel format. Open the template using OpenOffice Calc, fill in the details by copying from bank statements, fill in the "Debit" "Credit" columns in exact accordance with the "Chart of Accounts" code, save by selecting the "Use current format" button. In the program menu, click the "Import" button, select this template, click the "Open" button, in the "Data Import" window, click the "Import" button.

The Directory of Banks is corrected when bank details are changed and new ones are added.

"Counterparties" is filled in automatically, new counterparties are added when importing bank statements.

"JO 2 per month" in the form opens next month.

"Order Journal 2" on the right side of the form, data from the "Bank Statement" is added. In the "Documents" menu, "Journal Order 2" opens in a pivot table with filters. The table data is entered in the General Ledger form.

"Chess bank" is generated automatically for quick data review.

The fifth group of the program contains templates for loading data.

Templates are opened by clicking on the icon on the right side of the form.

It is possible to modify this program according to individual requirements.
You can check the scope of work and cost through

Calculation of the amounts of personal income tax and contributions to social funds using the Excel program for the monthly payment of taxes on the salary of employees and use for the preparation and submission of reports. Download the example file.

I used this method of accounting for wages and calculating the amounts of personal income tax and contributions in Excel from the beginning of 2005 to the 1st quarter of 2016 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 the wages of employees and the payment of personal income tax and contributions calculated from it.

The table used in practice for calculating personal income tax and contributions to social funds with changed full names. workers

For such accounting, a table is created on an Excel sheet, in the first line of which the names of the columns (graph) are recorded. The title bar is pinned to always stay in view.

Every month, one line is filled in for an employee with his accruals and calculations, which can be conditionally divided into four parts. I put them in the titles of the first four paragraphs.

Period

We call the first column "Year", and the second "Month". Such a division of the period into two columns is necessary for more convenient use of the autofilter. We leave the format of cells in all non-monetary columns as “General”, in cells with monetary amounts we set the format to “Numeric” with two decimal places.

Payroll accounting

  1. "For neg. days” - accrued for days worked or salary;
  2. "Vacation";
  3. "3 days for prev.» - allowance for temporary disability, accrued at the expense of the employer;
  4. “Help. by time netr." - temporary disability benefits accrued at the expense of the FSS;
  5. “Help. not region cash." - government benefits not subject to personal income tax.

In the column names, you can specify income codes that will serve as a hint when preparing reports in the 2-NDFL form.

Calculation of personal income tax

To calculate personal income tax, we need to determine the tax base, for this we add up all taxable income (in our example, these are columns 4, 5, 6 and 7) and subtract the amount of standard tax deductions from them. 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. "NDFL" - the amount of calculated personal income tax.

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

We calculate the amount of personal income tax in the amount of 13% by multiplying the base by 0.13. It is not necessary to round the resulting value in the cell, since the accrued personal income tax is rounded for each employee for the year. For each month, except December, when filling out the payment order, the total amount of calculated personal income tax is rounded up to rubles, and when paid 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 should be paid for December . Be sure to compare this amount with the tax amount received for December from the Excel spreadsheet - there will either be no difference between them, or there will be very little.

Calculation of contributions

Our Excel spreadsheet uses the following columns to calculate contributions:

  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 FSS for insurance of cases of temporary disability and maternity;
  4. "NS and PZ" - contributions to the FSS 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 appropriate coefficient.

Download example

In the download example, the interest rates of 2017 (22% and 5.1%, respectively), personal income tax in the amount of 13%, and NS and PZ in the amount of 0.2% are used to calculate contributions to the PFR and the MHIF.

To select data for a specific period for a specific employee, use an autofilter. If you, like mine in the screenshot, suddenly accrue less than the deduction provided, take it into account in the next period, when income exceeds the deduction. During the year, unused deductions accumulate and expire on December 31st.

Payroll in MS Excel- The electronic form presented here is designed to automate the process of calculating wages based on salaries. Now you do not need to perform routine monotonous work, calculating wages and taxes on it using a calculator.

Using the electronic form "Payroll in MS Excel" you just need to enter the list of employees of your organization, salaries, positions and personnel numbers once. Payroll calculation will be done automatically.

The electronic form "Payroll in MS Excel" is designed in such a way that income tax, unified social tax (pension insurance, social insurance, medical insurance) are calculated automatically.

Automatic calculation of taxes on wages (PF, social insurance, medical insurance, p / n), automatic calculation of the funded and insurance parts of labor pension individually for each employee and total (for reporting) for any reporting period.

Absolutely safe for your computer, no viruses or macros.

reviews: 5 | ratings: 25

Payroll in MS Excel for Windows

Opinions about Payroll in MS Excel

Rarely a high-quality program for calculating salaries in excel. Thanks a lot!

Good program for payroll, we have been using it for a long time. After using the trial version, bought the full one and it became even better)

A very convenient program for calculating payroll in excel was downloaded for free without any difficulties. Thank you!

Written for free, but in fact only up to 3 people.

for Pisareva Zh.Yu.

Enter 15 names of workers with data on hours worked. With the help of two reference tables, the payroll sheet with totals should be automatically filled. Bring a pie chart of the distribution of wages by workshops, automatically corrected when the data in the source table changes. Determine the category with the maximum total wage.

Step number 1. Directory of the distribution of workers by workshops and categories.

Let's start the Microsoft Excel program. To do this, press the start button located on the taskbar, thereby getting into the Main menu of the Windows operating system. In the main menu we find the item and in the submenu that opens we find the program Microsoft Excell.

Click and run the program.

On the worksheet, we mark out a table called "Handbook of the distribution of workers by workshops and categories." The table is placed starting from cell "A19 by cell "D179 This table contains four columns: “Personnel number”, “Full name9, “Rank9, “Workshop9 and seventeen lines: the first - combined four cells into one with the name of the table, the second - the names of the columns, the next fifteen to fill with data. The work area of ​​the table has the range "A3:D179" .

The created table is filled with data.

We create a table "Tariff Directory". The table is located on the worksheet from cell "A199 on the cell "B269. The table has two columns and eight rows. Similar to the table created earlier, in the first row it has the name, in the second the name of the columns, and the workspace of the table with the range "A21:B269 data on the ratio of the category to the tariff rate.

We fill the created table with initial data.

Step number 3. Record of hours worked.

By analogy with the table "Handbook of the distribution of workers by departments and categories" we create the table "Statement of hours worked." The table is located on the worksheet in the range of cells "F1: H179" . There are three columns in the table: “Personnel number”, “FULL NAME9 and Time worked. (hour)". The table is used to determine the amount of hours worked for each worker personally.

We fill the created table with initial data. Since the first two columns are identical to the table "Directory of the distribution of workers by workshops and categories", we use the previously entered data for efficiency. To do this, let's go to the first table, select the range of cells "A3: B179", the data of which corresponds to the list of personnel numbers and names of employees, and copy the area to the clipboard by clicking the appropriate button on the toolbar.

We go to the newly created table and stand on the cell "F39quot ;. Copy the contents of the clipboard to the table starting from the current cell. To do this, click the appropriate button on the Microsoft Excell toolbar.

Now let's fill in the third column of the table in accordance with the initial data.

Step number 4. Statement of payroll.

This table also has two columns identical to the previous table. By analogy, we create a table "Statement of payroll."

We fill the created table with initial data as in the previous version using the clipboard. Let's go to the "Timesheet" table;, select the range of cells "F3: G179" whose data corresponds to the list of personnel numbers and names of employees, and copy the area to the clipboard by clicking the appropriate button on the toolbar.

We go to the newly created table and stand on the cell "F219 and copy the data from the clipboard to the table starting from the current cell.

Now let's fill in the third column of the table. The data of the third column should be calculated from the initial data of the previous tables and interactively change when any value changes. To do this, the column must be filled with calculation formulas for each employee. The accrued salary is calculated based on the category of the worker, the amount of time worked by him. RFP = TARIFF * HOURS. For the calculation, we use the Microsoft Excel function “VPR9.

In the cell "H219 enter the formula «= VLOOKUP( VLOOKUP(F21;A3:D17;3) ;A21:B26;2) * VLOOKUP(F21;F3:H17;3) ". In the first multiplier, the VLOOKUP function (VLOOKUP(VLOOKUP(F21;A3:D17;3);A21:B26;2)) determines the employee's rate from the Rate Reference table (range "A21:B269"). To do this, we have to use the nesting of the VLOOKUP function (VLOOKUP (F21; A3: D17; 3). Here the function returns to us the rate of this worker from the table “Directory of the distribution of workers by workshops and categories” (range “A3: D179) and substitutes this value as required for the first VLOOKUP function.

In the second multiplier (VLOOKUP(F21;$F$3:$H$17;3)) the VLOOKUP function determines the time worked by the employee from the Payroll Sheet table (range "F3:H179").

In order to apply autocomplete to filling the resulting column, let's enter a formula with absolute references: "=VLOOKUP(VLOOKUP(F21,$A$3:$D$17,3),$A$21:$B$26,2)*VLOOKUP(F21,$F$3:$H$17,3)9.

Received a filled column of resulting data.

Good day, Friends.

How to fill out payroll?

What are the payroll taxes?

What program is used to calculate?

You have come to the right place. Continuing the topic Accounting of settlements with personnel, today we will consider a practical solution to this problem.

Let's solve it using Excel. bookkeeping, namely Payroll accounting l, it will be a matter of five minutes for you.

For the next billing period, copy, fill in the next month.

Watch the video lesson Payroll accounting in Excel. Try it - you will like it.

Read here how to deal with problems for a novice chief accountant.

Get the book "Annual Report 2014"

Two friends: Abram and Ivan.

Ivan comes to a friend, he knows - that house is not there. Sarah meets.

Listen, Sarah, let me hug you, give you a quarter.

Hug, - after much deliberation, Sarah agrees.

Sarah, let me kiss you, fifty dollars ladies.

Kiss, - Sarah will estimate the winnings.

Sarah, let me fuck you, my steward.

There is no market, money in advance.

Abram returns home.

Sarah, did Ivan come?

Yes, darling.

Did you bring my salary?

Yes, darling???

Now also available to you - personnel accounting. Good luck to you.

With best wishes, Alexander. to home

Subscribe and receive articles by email. Be the first to know.

The Microsoft Excel spreadsheet format can be a useful tool when using an employee payroll application. To help business owners with payroll, Microsoft has developed a template known as Payroll Calculator for Excel that you can download and use to your advantage if you have Microsoft Excel installed on your computer. The template contains formulas and functions already built into the workbook, in which you will need to enter your employees' payroll data. The template automatically calculates pay stubs and net payroll for employees based on the criteria you enter.

Steps

  1. Download the payroll template for Excel.
  2. Unzip the payroll calculation template.
    • Go to the folder on your computer where you saved the template and open the file.
    • Follow the prompts to unpack the template. The file will automatically open in Excel.
    • Depending on the capabilities and operating system version of your computer, you will be prompted to click Unpack or use a utility like Winzip to unpack the template.
  3. Save a copy of the template to use as your worksheet.
    • Hover over "File" in the Excel toolbar, then select "Save As" to save a copy of the template as a payroll workbook.
    • Navigate to the folder on your computer where you want to place this file for future use and enter a name for the book.
    • Click on "Save" to complete the process of creating the payroll book.
  4. Prepare payroll for your business.

    You can open your workbook in Excel.

    • Complete the "Employee Information" sheet. This sheet should open by default. You will be prompted to enter the names of employees, their pay rate, and tax information such as deductions and deductions.
    • Fill out the "Payroll Calculator" sheet ("Payroll Calculator"). To go to this sheet, click on the "Payroll Calculator" tab at the bottom of the Excel window. You will be prompted to enter information from the time sheet; such as how much your employees have worked, the amount of overtime, vacation hours, and sick leave.
  5. Get access to payroll or pay stubs of your employees.
    • Click on the "Individual Pay Stubs" tab, which is located at the bottom right next to the "Payroll Calculator" tab. This sheet contains formulas and functions that extract the data you enter and display it as pay slips for each employee.
  • If you need help using the specified template, click on "Using This Template" in the help section located on the taskbar on the right. This way you will be able to find help information for a specific query.

Article Information

This page has been viewed 16,120 times.

Was this article helpful?

Programs for calculating the earnings of employees, maintaining history, tracking the activity of personnel.

↓ New in the category "Personnel, salary":

Free
PSAPU-Year 04.11 is a convenient application that will help to conduct a qualimetric analysis and statistical processing of student attendance for compulsory classes. The PSAPU-Year application will automatically create lists of absent students for any school day, as well as students who missed more than a third of classes for not entirely valid reasons.

Free
PSORUD-Uniform 02.11 is an application for checking the results of educational activities. The PSORUD-Uniform application will be more convenient, as it saves time when entering the initial data about the school.

Free
Timesheet 2.4.2.19 is a convenient functional application for keeping records of work time for employees, as well as printouts of timesheets and duty schedules.

Free
Employees of the enterprise 2.6.8 is an application to facilitate record keeping by employees of the personnel department. The program has the ability to separate the possibilities by the obtained access rights and group the rights for different categories of users.

Free
Warehouse and trade 2.155 is an application for the organization of wholesale and retail trade and warehouse accounting. The application has a unified and highly customizable interface. The application also contains a large database with the ability to customize its subject matter for each user.

Free
Seniority calculator 6.1.1 is a program for calculating seniority by examining entries in the work book. The program "Experience Calculator" will be a good assistant for accountants and employees of human resources departments. The program also has the ability to calculate the length of service, taking into account the coefficients, calculating the exact number of years, months and days that have passed between the dates of the device and work, as well as counting only days - calendar and working.

Free
The calculation of experience 1.3 is a program for the number of years, months and days. The program "Calculation of experience" makes it possible to enter several periods of work, in addition, the calculation will be carried out by the number of years, months and days for each period, and the total and continuous experience will also be displayed.

Free
Payroll in MS Excel 4.2 is a convenient electronic form of payroll in MS Excel, with minimal manual input. The Payroll program will be able to automatically calculate all the necessary payroll taxes, including Social Security, UST, health insurance, income tax, and the like.

Free
Human Resources 6.0 is a human resources program. The Human Resources Department program makes it possible to create a personal card for an employee, his staff list and print out all personnel orders. The program also allows you to get a large number of various reports and calculate the total or continuous experience for employees.