How does execl set the scoring angle when calculating individual social security contributions?

How many decimal places should be reserved for (1) radix?

Viewpoint 1: It is mentioned in the document that "the base of individual contributions in the current year is determined according to the average wage income of employees in the previous year. The upper and lower limits of the individual contribution base are determined according to the published average monthly salary of employees in this city in the previous year of 300% and 60% respectively. The value is calculated according to the average monthly salary of employees in the city, and according to the principle of rounding, it is advanced to the corner and then to the yuan. " Accordingly, some HR think that the radix should be numerator, that is, no decimal places are reserved.

Viewpoint 2: It is mentioned in the document that "if an individual takes part in work for the first time and changes his work unit, the monthly payment base shall be determined according to the monthly salary income of the new unit in the first month." Therefore, since it is wage income, there must be some angles and points. Accordingly, some HR think that the base should be based on the actual situation of the total wages paid by the unit, and two decimal places or one decimal place should be reserved.

Analysis: the understanding of the viewpoint 1 is one-sided, because the document "advanced to the corner and then to the yuan according to the rounding principle" is aimed at the upper and lower limits of the payment base, not at the payment base between the upper and lower limits, that is, it is not stipulated that the base between the upper and lower limits should also be rounded to the yuan, which is misunderstood by many HR in practice. Point two makes sense. At present, the author understands that the social security center generally requires the payment base to be rounded to the corner, that is, one decimal place is reserved. If you keep it to one decimal place, you cannot enter it into the social security system.

After the base is determined according to the provisions of the document, the social security expenses of employees can be calculated by multiplying the individual contribution base by the social security proportion of the corresponding individual part, and then summing up.

(two) the calculation rules of social security expenses of this unit.

Social security costs are divided into unit parts and individual parts. If the real-time table is copied from the social security center, or calculated according to my above method, the accuracy of personal social security expenses can be guaranteed, but how to calculate the social security expenses of the unit? Is the base number of each employee multiplied by the proportion of the unit part and then summed up? The answer is wrong, because this calculation method is different from the rules of social security calculation unit.

The document stipulates that "the base of the monthly payment of social insurance premiums by the payer shall be determined according to the sum of the monthly payment bases of individual contributions of the unit". Therefore, after determining the individual payment base, all individual payment bases should be added up, and then multiplied by the social security ratio of the corresponding units-take the urban insurance ratio as an example: pension 22%, basic medical care 10%, local supplementary medical care 2%, unemployment 1.7%, work-related injury 0.5%, and maternity 0.8%, and the multiplication results are shown in the near angle respectively.

For example, if the sum of the payment bases of employees in a large enterprise is 1882520.6 yuan, then the calculation formula of unit part cost is-endowment insurance =1882520.6× 0.22 = 4141544.53200 ... (See near corner). In the same way, we can calculate the specific expenses of basic medical care, local supplementary medical care, unemployment, work injury and maternity, and then see the point near the corner, that is, enter the corner when we see any point, whether it is 1 or 9 o'clock (pay attention to the difference of rounding), and finally add up the calculation results of the five major risks to calculate the amount of the unit listed in the social security payment notice. There are two points to note here: 1 and 12% medical insurance should be divided into basic medical care 10% and local supplementary medical care 2% for calculation respectively; 2. Pay attention to the near angle rather than rounding.

(three) the proportion of payment and special circumstances

As the proportion of payment is scattered in the specific documents of major types of insurance, and there is also a well-organized proportion table on the Internet, this article will not repeat it, reminding HR novices that there may be two special situations in the urban social insurance paid by the unit according to different situations, namely, non-urban household registration in other provinces and cities (three insurances) and the transition from urban insurance to urban insurance. Their social security payment base and proportion need to be studied through relevant documents or refer to relevant articles in previous issues of this magazine.

20 12 Shanghai social insurance premium payment standard reference website:

Second, the actual operation

Through the above study, we have understood the basic method of calculating social security expenses. Next, we will use excel to realize the calculation function of approach angle. Everyone knows that excel is very powerful. Some HR colleagues found some functions with the function of approaching the angle through Baidu search, such as the ROUNDUP function and the CEILING function. However, after use, it is found that there is still a difference of ten cents when calculating the personal part of social security expenses of some employees. What is the reason? Let's take a look at an example first: an employee's social security contribution base is 59 17.6 yuan, enter cell B2 of excel, and enter the formula =ROUNDUP(B2*0.08, 1) in cell C2 to calculate the individual part of endowment insurance, and the result is 473.5 yuan, while the correct cost shown in the real-time social security table should be 473.4 yuan, which is much more. After calculating with a calculator, it is found that 59 17.6× 0.08 = 473.4080 ... Although the quantile of the calculation result shows 0, the third digit after the decimal point shows 8, so the ROUNDUP function shifts the third digit to the second digit first, so the second digit becomes 1, and then to the first digit, that is, the angular position, so it will This is obviously not the result of the angle we need, so we need to further improve this formula to keep two decimal places before calculating the angle.

The improved formula is = roundup (rounddown (B2 * 0.08), 1).

Formula analysis: the middle part of rounddown (B2 * 0.08,2) is interpreted as "rounding down". When it is applied here, it means to keep the 2 decimal places of the calculation result and discard the rest. The outer layer is nested with the ROUNDUP function, which is interpreted as "rounding up", and the following 1 is interpreted as retaining one decimal place, so that the functions of watching and approaching the angle can be realized. This formula can be applied to other social insurance.

Flexible use of the improved formula can accurately calculate the individual and unit costs of social security and reasonably calculate the cost of human resources, which is of great help to practical work.

Of course, the use of excel is not unique, and another function can also achieve this function. Again, the formula is = int (B2 * 0.08 *10+0.9)/10 (this formula is provided by HRM Xu). After the author's actual test, this formula can achieve the same function for your reference.

Further reading: How to buy insurance, which is good, and teach you how to avoid these "pits" of insurance.