Reading in Variables and Creating and Iteration Loop

This page shows you you can efficiently read data into a user defined function with a table of variables that includes scalar variables, time series variables and logical (bo0lean) variables.  On this page I also discuss how to create an iteration loop.

Reading Data into User Defined Function

In this section I explain how to get data into the UDF.  When working with a UDF, there is a limitation of something like 128 characters.  This means that making a big program that reads in a whole bunch of options is just about impossible.  The good news is that you can read variables from a spreadsheet into a large multiple dimension array.

 

Set-up the Structure of the UDF

The first excerpt below demonstrates how to set-up a UDF where the output will go to more than one cell and be an array function. To do this, you begin by defining the function as a Variant as shown in the function definition below.  Then I define an output array.  The dimensions of the array are the row first and the column second.  The array dimensions just have to be large enough to cover the total number of rows and columns that will be created by the function.  As shown in the example, some sort of definition must be made of the output array.  Finally, the name of the function is assigned to output array.  The Option base 1 is defined at the very top of the page. When you use Option Base 1 the first row will be the item of the array with the number 1 rather than the number 0.

Option Base 1

Function mult_sculpt() As Variant

Dim output(10, 100) As Single

' PUT IN THE CODE AND DEFINE OUTPUT

output(1, 1) = debt_irr
output(2, 1) = debt_balance

mult_sculpt = output

End Function
.
.

Work Backwards

Once you have created the structure of the UDF program, begin at the end and start working backwards. In this case, the end is computing the debt IRR.  The debt IRR is in turn computed from the debt cash flow which must be defined. Note that when you write the code, you can generally use excel functions with the WORKSHEETFUNCTION statement.  After you start at the end, keep working backwards until you have defined all the equations that are needed.

.

Option Base 1

Function mult_sculpt() As Variant

Dim output(10, 100) As Single
Dim debt_cf(100) As Single

' Keep Going Backwards

debt_cf(i) = -debt_balance

For i = 2 To 40
   For k = 1 To 3
     debt_cf(i) = debt_cf(i) + debt_service(i, k)
   Next k
Next i

debt_irr = WorksheetFunction.IRR(debt_cf)

output(1, 1) = debt_irr
output(2, 1) = debt_balance

mult_sculpt = output

End Function

.



Continue to Work Backwards and Define Required Inputs

The next insert illustrates how to keep working backwards and compute the debt service for the different issues. You can make a loop that goes around the different debt issues and make the different calculation for the capture debt issue and the other debt issues.  In the calculation for the captured debt issue, the CFADS and the target DSCR are necessary.  These are inputs that will have to be read into the function.  For the non-captured debt, the sum of the debt service is necessary.
.

Option Base 1

Function mult_sculpt(cfads,target_dscr) As Variant

Dim output(10, 100) As Single
Dim debt_cf(100), debt_balance_issue(10), debt_cf(10, 100), cfads(100), llcr(10), non_capture_ds(100) As Single

' Keep Going Backwards

capture_k = 2

For k = 1 To 3
   If k <> capture_k Then
      For i = 1 To 40
         debt_service(k, i) = cfads(i) / llcr(k)
         non_capture_ds(i) = non_capture_ds(i) + debt_service(k, i)
      Next i
   End If
Next k

For k = 1 To 3
   If k = capture_k Then
      For i = 1 To 40
         debt_service(k, i) = cfads(i) / target_dscr - non_capture_ds(i)
      Next i
   End If
Next k

debt_cf(i) = -debt_balance

For i = 2 To 40
   For k = 1 To 3
      debt_cf(i) = debt_cf(i) + debt_service(k, i)
   Next k
Next i

debt_irr = WorksheetFunction.IRR(debt_cf)

output(1, 1) = debt_irr
output(2, 1) = debt_balance

For i = 1 To 40
   output(3, i) = debt_cf(i)
Next i

mult_sculpt = output

End Function

.

.

Create and Iteration Loop for Circular Reference

After you have worked through the equations you can make an iteration loop.  I am often lazy about this, but it is best to put in a loop that goes around until the circular reference is solved.  When I am lazy I just use a simple FOR NEXT loop.  But it is best to find one of the sources of circular reference. Then you can see when the difference between the current iteration and the last iteration for that variable declines to zero or almost zero.  To do this you better make sure that the very first iteration does not go to zero.

Further Information and Learning: Request Resource Library (Free), Details About Courses

.

last_debt_balance = 999
debt_balance_difference = 999

Do While abs(debt_balance_difference) > .010

last_debt_balance = debt_balance

' All the stuff

debt_balance_difference = last_debt_balance - debt_balance

.

.

Final Circular Resolution

.

.

Function mult_sculpt(cfads, target_dscr, target_pct1, tenure1, int_rate1, _
target_pct2, tenure2, int_rate2, target_pct3, tenure3, int_rate3) As Variant

Dim output(10, 100) As Single
Dim debt_cf(100), cfads1(100), debt_balance_issue(10), llcr(10), non_capture_ds(100) As Single
Dim debt_service(10, 100), debt_service_capture(100), target_pct(10), tenure(10), int_rate(10) As Single
Dim pv_cash_flow(10), cfads_issue(100), overall_debt_service(100) As Single

' Keep Going Backwards

capture_k = 2

tenure(1) = tenure1
tenure(2) = tenure2
tenure(3) = tenure3

max_tenure = WorksheetFunction.Max(tenure1, tenure2, tenure3)

target_pct(1) = target_pct1
target_pct(2) = target_pct2
target_pct(3) = target_pct3

int_rate(1) = int_rate1
int_rate(2) = int_rate2
int_rate(3) = int_rate3

last_debt_balance = 999
debt_balance_difference = 999
iter = 0
debt_irr = int_rate(1)

' Do While Abs(debt_balance_difference) < 0.01

For j = 1 To 15

   iter = iter + 1

   If iter > 20 Then Exit Function

      last_debt_balance = debt_balance

' All the stuff

      For i = 1 To 40
         If target_dscr <> 0 And i <= max_tenure Then overall_debt_service(i) = cfads(i) / target_dscr
         non_capture_ds(i) = 0
         debt_cf(i) = 0
      Next i

      overall_debt_balance = WorksheetFunction.NPV(debt_irr, overall_debt_service)

      For k = 1 To 3
         If k <> capture_k Then
           For i = 1 To 40
             cfads_issue(i) = 0

             If i <= tenure(k) Then
                cfads_issue(i) = cfads(i)
             End If
           Next i

           debt_balance_issue(k) = target_pct(k) * overall_debt_balance

           llcr(k) = 1
           pv_cash_flow(k) = WorksheetFunction.NPV(int_rate(k), cfads_issue)

           If (debt_balance_issue(k) <> 0) Then llcr(k) = pv_cash_flow(k) / debt_balance_issue(k)

           For i = 1 To 40
              If i <= tenure(k) Then
                 debt_service(k, i) = 0
                 If (llcr(k) > 1) Then debt_service(k, i) = cfads(i) / llcr(k)
                     non_capture_ds(i) = non_capture_ds(i) + debt_service(k, i)
                 End If
            Next i
         End If
     Next k

For k = 1 To 3
If k = capture_k Then
For i = 1 To 40
If i <= tenure(k) Then debt_service(k, i) = cfads(i) / target_dscr - non_capture_ds(i)
debt_service_capture(i) = debt_service(k, i)
Next i
debt_balance_issue(k) = WorksheetFunction.NPV(int_rate(k), debt_service_capture)
End If
Next k

debt_balance = 0
For k = 1 To 3
debt_balance = debt_balance + debt_balance_issue(k)
Next k

debt_cf(1) = -debt_balance

For i = 2 To 40
For k = 1 To 3
debt_cf(i) = debt_cf(i) + debt_service(k, i - 1)
Next k
Next i

debt_irr = WorksheetFunction.IRR(debt_cf, 0.01)

debt_balance_difference = last_debt_balance - debt_balance


' Loop

Next j

output_1:

output(1, 1) = debt_irr
output(2, 1) = debt_balance

For i = 1 To 30
output(3, i) = debt_cf(i)
Next i

For k = 1 To 3
output(4, k) = debt_balance_issue(k)
Next k
output(4, 4) = overall_debt_balance

For i = 1 To 40
output(5, i) = cfads(i)
Next i

For i = 1 To 40
output(6, i) = overall_debt_service(i)
Next i

For i = 1 To 40
output(7, i) = non_capture_ds(i)
Next i

For i = 1 To 40
output(8, i) = debt_service_capture(i)
Next i

For k = 1 To 3
output(9, k) = llcr(k)
Next k

For k = 1 To 3
output(9, k + 4) = pv_cash_flow(k)
Next k

mult_sculpt = output

End Function

.

.

Setting Up Iteration Loop