Creating Your Own Functions in Excel

By Dinesh Takyar

Writing custom functions in Excel is easy and important because everybody needs to perform a calculation specific to their situation every now and then. Some companies may offer a special bonus to their employees while some other companies may offer more travel allowance or Christmas benefits. Using the standard available functions in Microsoft Excel may not provide the solution. The solution to the problem lies in creating your own function.

Custom functions, like macros, use the Visual Basic for Applications (VBA) programming language. They differ from macros in two significant ways. First, they use function procedures instead of sub procedures. They start with a Function statement instead of a Sub statement and end with End Function instead of End Sub. Second, they perform calculations instead of taking actions.
A custom function is more flexible because none of the actual raw data is hard-coded into the function; the user just types the data for the calculation instead of the actual calculation. Let's see a function in action.

To create this custom function, follow these steps:

1. Start Microsoft Excel.
2. Click on 'Tools' in the menu bar, select Macro and click finally on 'Visual Basic Editor' or just press 'Alt+F11' to start the Visual Basic Editor
3. On the Insert menu, click Module.
4. Type the following code in the new module:

Function paypackage(salary)
'House Rent Allowance (HRA)
'HRA=60% of basic salary
'Leave Travel Allowance (LTA)
'LTA=8.33% of basic salary
paypackage = salary + salary * 0.6 + salary * 0.0833
End Function

To use the custom function, follow these steps:

1. Type the following values in your worksheet:

Cell Value


A3 7000.00

B3 =paypackage(A3)

The value in cell A3 represents the basic salary of the employee.
This function automatically calculates the paypackage of an employee based on the basic salary and the result 11783.1 appears immediately.

Similar interesting functions can be created to calculate compound interest using:

Function Compound_Interest(PV As Double, R As Double, N As Double) As Double

Compound_Interest = PV*(1+R)^N 'Performs computation

End Function

In the function above PV is the present value, R is the interest rate, and N is the number of investment periods.

Take care!

Dr. Dinesh K Takyar is a corporate training in Microsoft Excel since the last 15 years.