UDF Functions for Financial Models

I made a set of pages 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 an 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.

Fixing Problems with Excel

My uncle Walter is a famous statistician and and genetcisit.  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 mathlab.  If you could not fix excel with UDF functions, he would be correct.  But through using VBA and fixing excel, you can dramatically change the power of the program.  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. 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 outuput to excel. This is the idea of my introduction. So, here are some general principles about functions:

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

.

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
bonjour = "Bonjour Monde"
end function

.

 

3. You should understand the difference between SCALAR and ARRAY variables. If you read a series of inputs into the function, they are array variables such as the series of capital expenditures, or EBITDA over time. If you read a single variable like the debt to capital ratio, this is a scalar variable that must not have an index like debt_to_capital(i).