Applying UDF – Fundamental Cases

Maybe you want your modelling career to involve applying rules and filling in template models with best practices. Maybe you think that success is when an auditor writes that you have no formulas that violate what they consider are best practices. If these are your objectives are just meeting basic requirements, then this page is not for you. On the other hand, if you want to break through the problems with excel that limit effective analysis of difficult financial analysis, then you can become an innovative analyst through writing some of your own code. This page walks through selected relatively simple exercises of applying a method that requires some programming — user defined functions — to resolve problems with excel that include circular references. I begin with examples from project finance where there are multiple painful circular references that severely limit many of the very big advantages of excel including data tables, goal seek and rapid evaluation of alternative financing structures. The user defined function method allows you to avoid problems that occur with both the iteration method (no goal seek; unstable models; slow models) and the copy and paste method (lose transparency; stop model execution; difficulty with data tables).  I start with very simple examples that only have a few inputs and do not have do loops.  Then I move to more complex cases with loops and reading in more variables. Each of the cases involves first understanding how the financial model works and then working through the excel model with equations.  All of the cases involving making a model parallel to your excel model. I have included the code on this page and also included the sample files.






Template UDF Parrallel Model that You Can Put Directly in Your Model By Pressing Button and then Following Instructions


Excel File with Project Finance Model Exercises Including DSRA, Mini Perm and UDF Exercises




Project Finance Example 1 — Simple Fees without Accumulation

The first example I make is very simple. In this example you can learn about the basics of a UDF. You need to have the name of the function defined as an output. You also cannot get any data from the spreadsheet unless you read data into the UDF. This is illustrated by the two hello functions below that include a function called hello() and a function named div_1000 that simply divides a number by 1000. The FORMULATEXT function in excel shows the functions. All of the functions are included in the file that you can download below.

The hello function is shown below. This is the simplest case that demonstrates how you much have a definition of the function name in the code. I also include a little SUB so you can find the function by pressing ALT-F8. The second function named Div_1000 demonstrates how you need to read data that is used in the function in UDF.


Sub simple_functions()

End Sub

Function hello()

     hello = "beunos dias"

End Function

Function Div_1000(constant)

      Div_1000 = constant / 1000

End Function


Case 1: Simple Funding without Loop – Cash Sweep and LBO Case

For the first case I have set up a very simple sources and uses of funds model with fees on debt. The fees are computed on debt and are included in the uses of funds. In the example you can contrast different methods for solving circular references.

The traditional copy and paste would have something like the following.

Sub SweepMacro()


While Range("Int_Difference").Value <> 0
     Range("Fixed_Int").Value = Range("Comp_Int").Value

End Sub

The alternative method with a user defined function is illustrated below

Function SweepInterest(InterestRate, OpeningBalance, EBITDA, SweepPercent)

sweep_difference = 999 ' Need a starting point that is not zero
Count = 1 ' protect against continuing look

While sweep_difference <> 0 ' This is like copy and paste
   Count = Count + 1
   If Count > 20 Then Exit Function ' protection

     last_sweep = SweepInterest 

' this will start with zero and used for the difference

      cash_flow_s = EBITDA - SweepInterest 

' this is what causes the circular reference

Sweeprepayment = WorksheetFunction.Min(cash_flow_s * SweepPercent, OpeningBalance) 
 ' Use excel function

ClosingBalance = OpeningBalance - Sweeprepayment 

' keep going backwards and see what you need

AverageDebt = WorksheetFunction.Average(OpeningBalance, ClosingBalance) 

' Use excel functions

SweepInterest = AverageDebt * InterestRate 

' start with the end, and see what you need

sweep_difference = last_sweep - SweepInterest

End Function

Case 2: LBO Sweep with Interest on Average Balance and Taxes

Excel File that Addresses Advanced Issues Including Alternative Currencies and Translation Adjustments in Models

Case 3: Simple Corporate Model with Short-term Debt and Cash Balance and Average Balances

In this example we work through the classic corporate model circular reference. In the example below I illustrate a case where a loan is repaid with a cash flow sweep.  The interest is computed on the average debt and not the initial debt. As the interest expense is a deduction for cash flow calculation.  So the interest drives the cash flow and the cash flow drives debt which drives interest.  The first screenshot below illustrates the interest calculation.

The UDF can resolve this circular reference in a very easy way.  You just create a little program that defines the sweep as an algebraic formula which is shown first.  Alternatively, the second function demonstrates how to work through the same equations in parallel.  You begin by writing an equation for the sweep and then work backwards.



The screenshot below illustrates how to implement the UDF.  Note that you can find the inputs with either the Shift and F3 short-cut or with the fx thing in the formula bar.

Case 4: More Complex Corporate Model with Taxes and Net Operating Losses

With taxes, the taxes affect the cash flow, but the taxes are affected by the interest expense.  Here, the tax calculations must be part of the UDF.  The UDF function is demonstrated below the screenshot.  I hope that you can see that the formulas are just the same as the formulas in the spreadsheet with some convergence routines added.

Function average_interest_taxes(interest_rate, cash_flow, opening_debt_balance, tax_rate, opening_NOL_Balance)

For Iteration = 1 To 30
last_sweep = sweep

average_interest_taxes = ((opening_debt_balance + closing_debt_balance) / 2) * interest_rate

taxable_income = cash_flow - average_interest_taxes
NOL_created = WorksheetFunction.Max(0, 0 - taxable_income)
NOL_used = WorksheetFunction.Min(opening_NOL_Balance, WorksheetFunction.Max(0, taxable_income))
closing_NOL_Balance = opening_NOL_Balance + NOL_created - NOL_used
adjusted_taxable_income = taxable_income + NOL_created - NOL_used
taxes_paid = adjusted_taxable_income * tax_rate

sweep = WorksheetFunction.Min(cash_flow - average_interest_taxes - taxes_paid, opening_debt_balance)

closing_debt_balance = opening_debt_balance - sweep

Next Iteration

converge = Abs(sweep - last_sweep)

If converge < 0.000001 Then Exit Function

End Function

Case 5: Interest During Construction with Loop

This exercise demonstrates how to address the classic circular reference loop in a project finance model.  The screenshot below demonstrates how the debt size comes from the total cost which in turn comes from the interest during construction. Then, the pro-rata percent for determining how much debt is issue comes from a ratio of the total expenditures including IDC.  To solve this circular reference problem this time you need a loop.  With a loop you need to re-set things to zero and be more carefule with the order of things. The UDF function is in line 19 and is an array function.  Arguments for the UDF function include the debt to capital percent, the interest rate and the series of capital expenditures.

The circular reference is illustrated below.  The total debt drives the IDC which drives the pro-rata percentage.  The pro-rata percentage level then drives the IDC which in turn drives the pro-rata percent.  When the blue arrows go back and forth like they do in the diagram below, you probably need a loop to solve the problem.  You also need to put the output of the UDF into an array variable rather than a single variable. The file that has this example is attached to the button below.

Excel File with Simple Example of Solving Circular Reference from Interest During Construction with UDF

The code below illustrates computation of a UDF in the loop.  I have put comments in the loop.  Note that you do not need to put anything in the UDF to say that it is an array output.  Note also that the total debt is computed separately from the debt balance.

Option Base 1 ' Use option base 1

Function idc_result(int_rate, pct_debt, cap_exp) ' The output is an array variable

Dim idc(10) As Double ' Put arrays on variables you need to remember

For iter = 1 To 20 ' This is arbitrary; it should be modified to check convergence

      project_cost = 0 ' Reset the project cost before the loop

     For i = 1 To cap_exp.Count
         project_cost = project_cost + cap_exp(i) + idc(i) ' Cap Exp is an array variable because of the way it is read in
     Next i

     debt_balance = 0
     debt = project_cost * pct_debt ' You need the project cost to get the debt

     For i = 1 To cap_exp.Count
         uses = cap_exp(i) + idc(i)
         pro_rata = uses / project_cost ' Compute the pro-rata
         draws = pro_rata * debt ' Draws from pro-rata

         idc(i) = debt_balance * int_rate

         debt_balance = debt_balance + draws
     Next i

  Next iter

idc_result = idc ' The result does not have to be an array

End Function

Case 6: Sculpting with Taxes

Sculpting with taxes creates a circular reference because the interest expense is deductible for taxes and the taxes drive the amount of CFADS which in turn drives the balance of debt