Counting basic net salary

If you want to calculate fast and simply how much would somebody’s net salary be, then you might want to use the macro below.

Gross salary is deducted with following payments

Income tax: this is from employee’s tax card

Employee’s part for work pension: 7,15 % of gross salary in case the employee is 17-52 or 63-67 of age.

Employee’s part for unemployment insurance: 1,25 % of gross salary in case the employee is 17-64 of age.

This can be done easily by writing the formulas manually. However, if you do this repeatedly, sooner or later you will value a macro.

Fill the cells with yellow filling: gross salary and income tax percentage.

This model does not take into consideration taxable benefits like mobile phone or taxable bonuses on top of basic salary.

Sub z_payroll_basic_macro()

Application.ScreenUpdating = False

ActiveCell.FormulaR1C1 = "Gross"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Tax"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Pension"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Unempl"
ActiveCell.Offset(-2, 2).Range("A1:A3").Select

Selection.NumberFormat = "0.00%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "7.15%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1.25%"
ActiveCell.Offset(-2, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-1]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-2]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-3]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select


ActiveCell.FormulaR1C1 = "=R[-4]C-SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "netto"
ActiveCell.Offset(-4, 0).Range("A1").Select

    ActiveCell.Offset(0, 1).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(1, 1).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Application.ScreenUpdating = True
End Sub

I did not write this macro manually, but recorded two macros and modified them.

Screen updates have been turned off, that the screen does not flash, when the macro is executed. You can remove the code lines, if you want. In case you want the screen updates to be disabled in some other macros, you can copy the lines here and add them to another macro.

This blog has been published earlier but now updated according to the year 2020 percentages.

Related