At the end of the year, it is time to settle the personal income tax. This is not a problem & quot; light & quot; Anyway, especially when your company size is not small. But do not worry, you can program & quot; catch & quot; Excel handles help & quot; burden & quot; This.
Preparation work
- PC running Windows 2000 or XP, Microsoft Excel XP or 2003
- Appendices 1 and 2, issued together with the Finance Ministry's Circular No. 81/2004 / TT-BTC of August 13, 2004.
- Plan the following functions:
PITLC: Personal income tax calculator for Vietnamese citizens and other individuals residing in Vietnam.
PITFR: The calculation of personal income tax for foreigners residing in Vietnam and Vietnamese citizens working and working in foreign countries.
NET2GROSSLC: Net to gross earnings ratio for Vietnamese citizens and other individuals residing in Vietnam.
NET2GROSSFR: Gross conversion of net income to gross income for foreigners residing in Vietnam and Vietnamese citizens working and working abroad.
Note: Function names may change to suit your requirements.
CREATE
Open a new workbook
Press Alt + F11 to launch microsoft Visual Basic
Click on your spreadsheet in the VBA project window. For example: VBA project (book1)
Select Insert.Module
Double click on the new module and enter the following code.
Function pitlc (gross_local)
Personal Income Tax for Local Vietnamese Citizen
If (gross_local & gt; 0) And (gross_local & lt; = 5000000) Then
pitlc = 0
ElseIf (gross_local & gt; 5000000) And (gross_local & lt; = 15000000) Then
pitlc = (gross_local - 5000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: Some Text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016-200. jpg title_vn.txt tmp02.html tmp03.html tmp2.html tmpdesc2.txt tmpdesc3.txt tmpdesc4.txt tmpdesc5.txt tmpdesc6.txt tmpdesc7.txt tmpdesc.txt tmp.html tmpresult.txt tmptrans.txt transresult.txt wm.AzSoft. com www.mailenable.com www.microsoft.com www.securityfocus.com 0.1
ElseIf (gross_local & gt; 15000000) And (gross_local & lt; = 25000000) Then
pitstab = 1000000 + ((gross_local - 15000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.2)
ElseIf (gross_local & gt; 25000000) And (gross_local & lt; = 40000000) Then
pitstab = 3000000 + ((gross_local - 25000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.3)
ElseIf (gross_local & gt; 40000000) Then
pitstab = 7500000 + ((gross_local - 40000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.4)
End If
End Function
Function pitfr (gross_foreign)
Personal Income Tax for Resident Foreigners in
If (gross_foreign & gt; 0) And (gross_foreign & lt; = 8000000) Then
pitfr = 0
ElseIf (gross_foreign & gt; 8000000) And (gross_foreign & lt; = 20000000) Then
pitfr = (gross_foreign - 8000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: Some Text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016-200. jpg title_vn.txt tmp02.html tmp03.html tmp2.html tmpdesc2.txt tmpdesc3.txt tmpdesc4.txt tmpdesc5.txt tmpdesc6.txt tmpdesc7.txt tmpdesc.txt tmp.html tmpresult.txt tmptrans.txt transresult.txt wm.AzSoft. com www.mailenable.com www.microsoft.com www.securityfocus.com 0.1
ElseIf (gross_foreign & gt; 20000000) And (gross_foreign & lt; = 50000000) Then
pitf = 1200000 + ((gross_foreign - 20000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.2)
ElseIf (gross_foreign & gt; 50000000) And (gross_foreign & lt; = 80000000) Then
pitf = 7200000 + ((gross_foreign - 50000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.3)
ElseIf (gross_foreign & gt; 80000000) Then
pitf = 16200000 + ((gross_foreign - 80000000) 1 AzSoft_watermark_big.png AzSoft_watermark_small.png cong-listening? p = 1 des.txt en_metadesc.txt en_name.txt getpageinfo.sh getpagelink.sh imglink.txt imglist.txt img.AzSoft.com label: some text link_original.txt link.txt log.txt meta_desc.txt name.txt news t-142619 testimg2-0.jpg testimg2-1.jpg testimg2.jpg testimg3.jpg testimg.jpg thumb tim-hieu-excel-2016 -200.jpg tmp03.html tmp02.html tmpdesign.txt tmpdesktop.txt .AzSoft.com www.mailenable.com www.microsoft.com www.securityfocus.com 0.4)
End If
End Function
Function net2grosslc (net_local)
Local Net Salary will be gross-up to Gross Salary
If (net_local & gt; 0) And (net_local & lt; = 5000000) Then
net2grosslc = net_local
ElseIf (net_local & gt; 5000000) And (net_local & lt; = 14000000) Then
net2grosslc = Round ((net_local - 500000) / 0.9, 0)
ElseIf (net_local & gt; 14000000) And (net_local & lt; = 22000000) Then
net2grosslc = Round ((net_local - 2000000) / 0.8, 0)
ElseIf (net_local & gt; 22000000) And (net_local & lt; = 32500000) Then
net2grosslc = Round ((net_local - 4500000) / 0.7, 0)
ElseIf (net_local & gt; 32500000) Then
net2grosslc = Round ((net_local - 8500000) / 0.6, 0)
End If
End Function
Function net2grossfr (net_foreign)
'Foreigner Net Salary' to be gross-up to Gross Salary
If (net_foreign & gt; 0) And (net_foreign & lt; = 8000000) Then
net2grossfr = net_foreign
ElseIf (net_foreign & gt; 8000000) And (net_foreign & lt; = 18800000) Then
net2grossfr = Round ((net_foreign - 800000) / 0.9, 0)
ElseIf (net_foreign & gt; 18800000) And (net_foreign & lt; = 42800000) Then
net2grossfr = Round ((net_foreign - 2800000) / 0.8, 0)
ElseIf (net_foreign & gt; 42800000) And (net_foreign & lt; = 63800000) Then
net2grossfr = Round ((net_foreign - 7800000) / 0.7, 0)
ElseIf (net_foreign & gt; 63800000) Then
net2grossfr = Round ((net_foreign - 15800000) / 0.6, 0)
End If
End Function
CREATE ADD-IN
In the steps above you have created the functions used to calculate income tax. However, these functions are only used for the worksheet itself. In order for all spreadsheets to use the function created above or you want to send these functions to your friends, you need to create an add-in. Here's how to create an add-on:
Right-click on the module created above
Fill in the General tab
a. Project Name: Enter the name of the function (for example, PIT).
b. Project Description: Fill in the project description (example: PIT form)
On the Protection tab, click Lock project for viewing then enter your password and confirm your password if you do not want others to see and edit your code.
Close the VBA window
In File_Save As, click the Save as type box and select Microsoft Office Excel Add-in (* .xla).
Name the file and select the path to save the file (eg: c: PITformular .xla)
Completed
To add add-in to any machine with Excel just go to Tools -> Add-Ins and then click Browse ... and find where the file contained .xx and then OK.
Dat_Vuong
dat_vuong1967@yahoo.co.uk
Shimizu Corporation
