# UDF Functions for Financial Models

I made a set of pages that are sub menus to this page that describe certain UDF functions that can be helpful in different kinds of financial models. In the associated child pages to this page you can see how to make a series of user-defined functions that fix problems with excel. I start with some relatively simple user-defined functions including MAXIF, MINIF, SUMNA, XMIRR function and a PAYBACK function. I then move to more tricky functions for resolving the vintage depreciation problem and developing dynamic goal seeks that do not require you to stop excel.  I have included excel files along with the copied code.

## Fixing Problems with Excel

My uncle Walter is a famous statistician and and geneticist.  He was the last PHD student of R.A. Fischer, the father of statistics. Walter told me that I am an idiot for using excel instead of using something more sophisticated like matlab.  If you could not fix excel with UDF functions, he would be correct.  But through using VBA user-defined functions and fixing excel, you can dramatically change the power of the program (a good example of this is the LOOKUP INTERPOLATE function that is described in this section of the website).  With user defined functions, you can take back control of of excel; solve circular references; optimize equations; create functions for computing volatility etc.

## General Principles of User-Defined Functions

To begin the process of solving circular references by functions, I demonstrate some of the principles of user defined functions below. I have tried to read books on VBA and I have used a lot of discussion boards. I find these very difficult or sometimes to easy to be useful. I don’t care about making range’s dimensioned and using complex syntax. All I want to do is make a loop and compute some numbers that can be output to excel. This is the idea of my introduction. So, here are some general principles about functions including naming the function, using OPTION BASE 1, collecting inputs (you cannot refer to ranges in excel without using them to input) :

## The Name of the Function Must be a Variable and the Result

1. The name of the function must be defined with and = sign. For example if you make a function named BONJOUR with Function Bonjour, then somewhere in the function you must have the statement BONJOUR = something (for example, BONJOUR = “Bonjour Monde”.

.

```Function bonjour
bonjour = "Bonjour Monde"
end function```

.

## You Must Read Variables that You Want to Use into the Function

2. You cannot use RANGES from the excel file. For example, you cannot use RANGE(“A1”) in your function. Instead, you must read the variable into excel. If you want to put the date into your BONJOUR function and the date is in excel, you must read the date into your function. To do this you would enter something like FUNCTION BONJOUR(Todaydate).

.

```Function bonjour(todaydate)
bonjour = "Bonjour Monde" & todaydate
end function```

.