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