How to calculate overtime pay with free download form 2025

The overtime pay table is an essential tool to help businesses manage and accurately calculate overtime pay for employees. Correct application of overtime regulations not only ensures compliance with labor laws but also demonstrates fairness and transparency in human resources policies. With many different coefficients applied to weekdays, weekends and holidays, calculating overtime pay requires accuracy and efficiency. 1C Vietnam will guide you on how to create and use the most effective overtime pay table for businesses.

1. Learn about overtime pay

1.1 What is overtime pay?

Overtime pay (also known as overtime pay) is the amount of money that employers must pay employees when they work beyond the normal working hours as prescribed. This is a mandatory regime under the Labor Code to ensure the rights of employees when they have to work overtime.

Overtime pay plays an important role in:

• Ensure workers are fairly compensated for overtime work
• Create motivation when businesses need to increase productivity in a short time
• Help businesses be flexible in meeting production and business needs
• Ensure fairness and transparency in salary policy

1.2. Legal regulations on overtime pay in Vietnam

According to the 2019 Labor Code (effective from January 1, 2021), overtime pay is specifically regulated as follows:

Overtime pay according to working time:

• Weekdays: At least 150% of regular hourly wage
• Weekly rest day: At least 200% of regular hourly rate
• Holidays, Tet: At least 300% of regular hourly wage
• Working at night: Must be paid at least 30% more than the salary calculated according to the salary unit price or actual salary paid for the work being done during the day.

Conditions for receiving overtime pay:

• Overtime must be agreed upon by the employee.
• Overtime hours do not exceed 40 hours in 1 month
• Total standard working hours and overtime hours shall not exceed 12 hours in 1 day.
• Overtime hours do not exceed 200 hours in 1 year (in special cases, not more than 300 hours/year)

Legal documents related to overtime pay include:

• Labor Code 2019 (Law No. 45/2019/QH14)
• Decree No. 145/2020/ND-CP detailing and guiding the implementation of a number of articles of the Labor Code on working conditions and labor relations
• Circular No. 10/2020/TT-BLDTBXH guiding a number of articles of the Labor Code on the content of labor contracts

2. Detailed calculation of overtime pay

Calculating overtime pay requires accuracy and compliance with legal regulations. Below is a detailed guide on how to calculate overtime pay in different cases.

2.1. Formula for calculating overtime pay by hour/weekly day

To calculate overtime pay for weekdays, it is necessary to determine the basic hourly wage first, then apply the overtime coefficient according to regulations.

The basic formula for calculating overtime pay on weekdays is as follows:

• Overtime pay on weekdays = Basic hourly wage × Number of overtime hours × 150%
• In there:
• Basic hourly wage = Monthly wage / (Number of working days in the month × Number of working hours per day)
• The number of working days in a month is usually 22-26 days (depending on the month)
• The normal working hours per day are 8 hours.

Example:

• Basic salary: 6,000,000 VND/month
• Number of working days per month: 22 days
• Working hours per day: 8 hours
• Overtime hours per month (weekdays): 15 hours

Step 1: Calculate basic hourly wage

Basic hourly wage = 6,000,000 / (22 × 8) = 34,091 VND/hour

Step 2: Calculate overtime pay on weekdays

Overtime pay on weekdays = 34,091 × 15 × 150% = 766,023 VND

2.2. Formula for calculating weekend and holiday wages

For overtime pay on weekends and holidays, the salary will be higher than on weekdays due to different multiplication coefficients.

Formula for calculating overtime pay on weekends:

Overtime pay on holidays = Basic hourly wage × Number of overtime hours × 200%

Formula for calculating overtime pay on holidays:

Holiday overtime pay = Basic hourly wage × Number of overtime hours × 300%

Example:

• Basic hourly wage: 34,091 VND/hour (from previous example)
• Overtime hours on weekends: 8 hours
• Overtime hours on holidays: 4 hours

Calculating overtime pay on weekends: Overtime pay on holidays = 34,091 × 8 × 200% = 545,456 VND

Calculating overtime pay on holidays: Overtime pay on holidays = 34,091 × 4 × 300% = 409,092 VND

2.3. How to calculate overtime pay at night

Overtime pay at night is calculated in a special way because it combines both night work allowance and overtime coefficient. According to regulations, night work time is calculated from 10 pm to 6 am the next morning.

Formula for calculating nighttime overtime pay: Nighttime overtime pay = Basic hourly wage × Number of overtime hours × Overtime coefficient × (100% + 30%)

Or it can be calculated in another way: Overtime pay at night = Regular overtime pay + Night work allowance

In there:

• Night work allowance = Basic hourly wage × Number of night work hours × 30%
• Regular overtime factor depends on day type (150%, 200% or 300%)

Example:

• Basic hourly wage: 34,091 VND/hour
• Overtime hours at night on weekdays: 3 hours

Method 1: Overtime salary at night = 34,091 × 3 × 150% × (100% + 30%) = 199,166 VND

Method 2:

• Regular overtime pay = 34,091 × 3 × 150% = 153,410 VND
• Night shift allowance = 34,091 × 3 × 30% = 30,682 VND
• Overtime pay at night = 153,410 + 30,682 = 184,092 VND

Note: There are slight differences between the two calculations due to rounding. In practice, businesses should agree on one calculation method and apply it consistently.

Additional regulations on night work:

• Night workers must have at least 12 hours of rest before moving to another shift.
• Workers working at night must be arranged for regular health check-ups according to regulations.
• Some special subjects (pregnant women, minors) have separate regulations on working at night.

3. Instructions for creating an overtime pay spreadsheet in Excel

Microsoft Excel is a popular and effective tool for creating overtime pay spreadsheets. With built-in formulas and features, Excel helps automate the calculation process, minimize errors and save time.

3.1. Basic steps to set up an Excel spreadsheet

Here is a step-by-step guide to creating an overtime pay spreadsheet in Excel:

Step 1: Set up spreadsheet structure

Create a new Excel file and name the sheet (for example: "Overtime salary calculation sheet March 2025")

Design a header for a table with basic columns:

• Employee code
• Full name
• Department
• Basic salary
• Overtime hours on weekdays
• Overtime hours on holidays
• Overtime hours on holidays
• Nighttime overtime hours
• Total amount of each type
• Total overtime pay

Step 2: Enter basic data

• Enter employee information: code, name, department
• Enter the basic salary of each employee
• Enter overtime hours by type (weekdays, holidays, nighttime)

Step 3: Set up calculation formulas

Create a cell to calculate basic hourly wage: =Basic wage/(22*8)

Create a formula to calculate overtime pay on weekdays: =Basic hourly wage*Number of overtime hours on weekdays*1.5

Create formula to calculate overtime pay on holidays: =Basic hourly wage*Number of overtime hours on holidays*2

Create formula to calculate overtime pay on holidays: =Basic hourly wage*Number of overtime hours on holidays*3

Create formula to calculate nighttime overtime pay: =Basic hourly wage*Number of nighttime overtime hours*1.5*1.3

Step 4: Calculate total overtime pay

Use the SUM function to calculate the total overtime pay for each employee: =SUM(Overtime pay on weekdays, Overtime pay on holidays, Overtime pay on holidays, Overtime pay at night)

Step 5: Format the spreadsheet

• Format cells containing currency in VND
• Add conditional formatting to highlight special values
• Add borders and background colors to increase visibility

Step 6: Create additional features

• Add SUMIF function to calculate total overtime cost by department
• Create charts to visualize overtime costs
• Add filtering and sorting features to easily find information

4. Free overtime pay spreadsheet template

Overtime pay calculation forms according to Circular 133/2016/TT-BTC and Circular 200/2014/TT-BTC of the Ministry of Finance are designed in accordance with current accounting regulations, helping businesses easily account for labor costs.

DOWNLOAD

Overtime pay calculator is an indispensable tool for any business that employs overtime workers. Correctly applying overtime pay formulas not only helps businesses comply with legal regulations but also ensures the rights of employees, motivates them to work and builds a fair and transparent working environment.