Create USER DEFINED FUNCTION (UDF) in Excel - An example on Tax Calculation



There are a lot of built in functions in excel such as SUM,Average and so on. However some time some calculations are to be done multiple times which could be reduced using user defined functions in excel. 

In this article I am giving an example on calculating income tax as per government rule for fiscal year 2071/72. However you can use the same process for creating other functions of your choice.


fig:- Income Tax Rate 2071/72
Source:-NBSM & Associates

Note:- I am using excel 2013, the process may be similar for others too.


Steps:-
1) Activate developer Tab on excel
Open excel,go to file tab, Click on excel option and then customize ribbon, enable developer tab and click ok

Fig:- Adding developer tab
2) Enable Macros
Go to developer tab>>macro security and enable macro
Fig:- Enabling Macro
3) Add a module
Click on Visual basics then right click Microsoft excel objects and insert module
Fig:- Adding Module
4) Adding the code in the box opened.

For Tax calculation of Married person, add the following code


Function TaxMarried(salary)
    If salary * 12 <= 300000 Then
        TaxMarried = salary * 12 * 0.01
     ElseIf 300000 < salary * 12 <= 400000 Then
         TaxMarried = 300000 * 0.01 + (salary * 12 - 300000) * 0.15
    ElseIf salary * 12 > 400000 Then
        TaxMarried = 300000 * 0.01 + 100000 * 0.15 + (salary * 12 - 400000) * 0.25
End If
End Function



Fig:- Code for Tax of Married person

5) Click on file tab and save the workbook to the location 
C:\Users\your_user\AppData\Roaming\Microsoft\AddIns
with save type excel add-in (.xlam)
Tax.xlam in my case
Fig:- Saving workbook as add-inn
Note:- The location is important nor it wont work always in any workbook

6) Coding for Tax calculation of Unmarried person
Insert Another module right clicking Microsoft Excel objects and add the following code and save.


Function TaxUnMarried(salary)

    If salary * 12 <= 250000 Then
        TaxUnMarried = salary * 12 * 0.01
     ElseIf 250000 < salary * 12 <= 350000 Then
         TaxUnMarried = 250000 * 0.01 + (salary * 12 - 250000) * 0.15
    ElseIf salary * 12 > 350000 Then
        TaxUnMarried = 250000 * 0.01 + 100000 * 0.15 + (salary * 12 - 350000) * 0.25
End If
End Function

Figure:- Adding code for unmarried person

7) Activate the add-inn saved earlier 
Close the all windows resulted from clicking visual basic in developer tab.
Click File tab in excel>> options>>add-ins
Manage excel add-inns>> Go
Figure:- Manage Add-in

Cick on Tax for activating it and click ok
Fig:- Activating add-in Tax
8) The function TaxMarried and TaxUnMarried are now ready and they appear for the tax to be calculated.
Fig:- Using TaxMarried function

Fig:- Tax Caluclation using userdefined function TaxMarried
Note:- Monthly salary is given as input and annual tax is calculated. However it can be changed while coding

Also you can download the add in Tax here and paste it to location C:\Users\your_user\AppData\Roaming\Microsoft\AddIns, then follow steps above starting from step 7.

It is just an example of user defined function for tax calculation. However User Defined Function could be created for any task according to the need.

This article/tutorial is written by Basanta Adhikari, ICTV Malangwa Municipality. For feedback, please comment on the article below.
Share on Google Plus

About Author (Basanta Adhikari)

This portal "ICT for Local Bodies" is written by various individuals - ICT Experts and ICT Volunteers working under MoFALD/LGCDP with an aim of integrating ICT at Local Governance. The author is this post is mentioned at the end of the article itself (with their Google+ profile alongside). Learn more about ICT4LB KMDB here.