How to Calculate Sick Leave in Excel

Legislation and guidelines governing calculations for accountable persons (specifically, company employees) are constantly changing. Accountants must always stay updated. Let's try to simplify the challenging task for accountants: we'll provide instructions on how to find the average daily earnings for sick leave calculations in Excel, as well as the fundamental rules for calculating temporary incapacity benefits.

 

Specifics of Sick Leave Calculation

Where to source funds for benefit payments:

  • For the first three days (if the employee is sick) – from the insurer's funds;
  • From the fourth day onwards – covered by the SSF (Social Security Fund);
  • If the sick leave is due to taking care of a sick family member, for recuperation in a sanatorium, etc. (excluding the employee's own illnesses or injuries), then the SSF covers the benefit from the first day.

How to calculate sick leave and average earnings for benefit calculation:

  1. The calculation period includes the two calendar years preceding the onset of temporary incapacity. If someone takes sick leave in 2016, the years 2014-2015 are required for benefit calculation.
  2. All earnings subject to insurance contributions are necessary.
  3. To calculate the average salary, divide the total payment amount for the calculation period by 730.

Insurance tenure is essential for temporary incapacity benefit:

  • More than 8 years – 100% of the average salary;
  • 5-8 years – 80%;
  • Less than 5 years – 60%.

These percentages apply when the worker himself suffers an injury or illness or cares for a child in a hospital setting.

Calculation of Average Earnings for Sick Leave Payment

You can calculate directly within the payroll spreadsheet or maintain a separate record for sick leave payments.

Many accountants use Excel for payroll records. For example, in February 2016, two people were on sick leave. Bobrovsky was absent from 8 to 12.02, and Pronkin from 15 to 17.02. In the "Number of Sick Days" column, enter the number of days of temporary incapacity: 5 for Bobrovsky and 3 for Pronkin.

The calculation period for each is 2014-2015. Since the accountant has all the records in Excel, linking to the appropriate cells simplifies the final sum. Earnings are considered without deductions and withholdings. In our example, it's the "Total Earnings" column (column O).

To calculate the average daily earnings, use the formula: SUM(jan2014:dec2015!O13)/730.

The average earnings considering insurance tenure: SUM(jan2014:dec2015!O13)/730*LOOKUP(C13;{0;5;8};{0.6;0.8;1}).

Multiply the result of the second formula by the number of days of temporary incapacity. The final formula for sick leave calculations:

Payroll spreadsheet.

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

Sick leave benefit calculation form.

The calculation period is entered manually. The total amount is calculated automatically.

Formula for average daily earnings: =B28/730.

Considering tenure: =E7*LOOKUP($C$1;{0;5;8};{0.6;0.8;1}).

Temporary incapacity benefit calculation: =E8*E9.

Minimum Benefit Amount

If the employee's average earnings fall below the minimum wage or if they had no salary during the calculation period, the calculations differ.

Details for filling out sick leave if average earnings are below the minimum wage:

If earnings are below the minimum wage.

Average earnings considering insurance tenure: =I9*LOOKUP(I6;{0;5;8};{0.6;0.8;1}).

Benefit: =I10*I11.

download file Download the Excel sick leave calculation example

Enter the work experience and number of days of temporary incapacity in the table (minimum wage changes). All other indicators are calculated automatically.


en ru