Calculation for sick leave download in Excel

Legislative acts and instructions governing settlements with accountable persons (in particular, employees of the enterprise) are constantly changing. The accountant has to keep his finger on the pulse all the time. We will try to make the hard work of accounting a little easier: we will give instructions for finding the average daily earnings for calculating sick leave in Excel, as well as the basic rules for calculating temporary disability benefits.

Features of calculating sick leave

Where to get funds for the payment of benefits:

• the first three days (if the employee himself is sick) - from the funds of the insured;
• from the fourth day - paid by the FSS;
• if a sick leave is issued to care for a child, a sick family member, for aftercare in a sanatorium, etc. (this list does not include illnesses and injuries of the employee himself), then from the first day the benefit is paid by the FSS.

1. In the billing period, 2 calendar years preceding the date of the onset of temporary disability are taken. If a person goes on sick leave in 2016, then 2014-2015 is needed to calculate benefits.
2. All accruals for which insurance premiums are calculated are needed.
3. To calculate the average salary, you need to divide the total amount of payments in the billing period by 730.

To pay temporary disability benefits, the insurance period is important:

• over 8 years - 100% of the average salary;
• 5-8 years - 80%;
• less than 5 years - 60%.

These percentages are relevant in cases where the employee himself was injured or ill or is caring for a child in a hospital.

Calculation of average earnings for sick leave

You can calculate directly in the payroll sheet or keep separate records of sick leave payments.

Many accountants keep payroll in Excel. For example, in February 2016, 2 people were on sick leave. Bobrovsky - from 8 to 12.02. Pronkin - from 15 to 17.02. In the column "Number of sick days" we put the number of days of temporary disability. For Bobrovsky - 5. For Pronkin - 3.

The settlement period for each is 2014-2015. Since the accountant has saved all the accrual statements in Excel, when finding the total amount, you can make links to the corresponding cells. Charges are taken without taking into account deductions and contents. In our example, the column “Total accrued” (column O).

Average earnings taking into account the length of service - SUM(Jan2014:Dec2015!O13)/730*VIEW(C13;(0;5;8);(0.6;0.8;1)).

The value obtained as a result of the action of the second formula is multiplied by the number of days of temporary work capacity. The final formula for calculating sick leave accruals:

For separate accounting and calculation of sick leave payments, you can use the following form:

Accruals in the billing period are made manually. The total amount is calculated automatically.

The formula for calculating the average daily earnings: =B28/730.

Taking into account the length of service: =E7*VIEW($C$1;(0;5;8);(0.6;0.8;1)).

Calculation of temporary disability benefits: =E8*E9.

Minimum Benefit

If the average salary of an employee is below the minimum wage, or in the billing period the person had no salary at all, then the calculations will be carried out a little differently.

Data for filling out a sick leave if the average salary is less than the minimum wage:

Average earnings, taking into account the insurance period: \u003d I9 * VIEW (I6; (0; 5; 8); (0.6; 0.8; 1)).

The work experience and the number of days of temporary disability (minimum wage, when it changes) are entered into the table. All other indicators are calculated automatically.