Principles of Financial Modelling

CHAPTER 33User-defined Functions

INTRODUCTION

This chapter focuses on the application of user-defined functions (“udfs”). The first section summarises the potential benefits of using udfs. The second section recaps some main points about their syntax and implementation. The third section provides examples, especially of those which may have their application in general financial modelling contexts: these include “wrapper” functions (that include several Excel or VBA functionalities together within an overall function), and those that capture calculation processes that do not exist as Excel functions, including some statistical measures.

BENEFITS OF CREATING USER-DEFINED FUNCTIONS

Functions are distinguished from subroutines in that they can provide a result only into an Excel cell (or into a range, for array functions), and that they create a live-link between the function's parameters and the results in Excel.

User-defined functions can be very powerful in many circumstances, including:

• To reduce the size of models in which there are otherwise large tables of intermediate calculations (where only the result is of explicit interest, rather than the individual steps).
• To perform calculations that are cumbersome to create in Excel.
• To increase flexibility in model structure. A udf can allow for a more rapid adaptation of the model as new data is added. For example, many sequences of Excel calculations are linked to specific input ranges, and as new data is added, the size ...