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.