Function Compute_LLCR() ' This is initial LLCR with sculpting from constant DSCR Dim output1(1, 20) As Single ' First iterate around the LLCR factor to find close llcr = min_dscr ' Set iniital DSCR for first iteration For iter = 1 To 3 ' Three iterations should be enough to get reasonable estimate`debt_balance = debt_amount ' Debt amount comes from input in the read data file`

`pv_debt_service = 0 ' Re-set the PV for each iteration`

`pv_cfads = 0`

`compound_factor = 1`

`For i = 1 To tenure`

`interest_expense(i) = debt_balance * interest_rate(i) ' The debt balance starts high and then goes down`

`debt_bal(i) = debt_balance ' Debt balance in array`

`ebit = ebitda(i) - dep_exp(i)`

`' Ebit is from subtracting depreciation ebt = ebit - interest_expense(i) taxes(i) = ebt * tax_rate`

`' Change this and include the ebt`

`cfads(i) = ebitda(i) - taxes(i)`

`If llcr <> 0 Then debt_service(i) = cfads(i) / llcr - other_debt_service(i)`

`' Now compute the debt service as pv of cfads`

`repayment(i) = debt_service(i) - interest_expense(i)`

`' repayment is from CFADS and thus tax`

`debt_balance = debt_balance - repayment(i)`

`compound_factor = compound_factor * (1 + interest_rate(i))`

`' Overall debt IRR`

`pv_cfads = pv_cfads + cfads(i) / compound_factor`

`' PV of debt service is debt`

`pv_debt_service = pv_debt_service + debt_service(i) / compound_factor`

`' PV of debt service is debt Next i If debt_amount <> 0 Then llcr = pv_cfads / debt_amount`

Next iter End Function

## Goal Seek Function

Function Goal_Seek_for_LLCR_Factor() ‘ Goal seek to find the LLCR factor

Dim output1(1, 20) As Single

ReDim repayment_sum(tenure), debt_period(tenure)

‘ First iterate around the LLCR factor to find close

Increment_to_LLCR_Factor = (max_llcr – 1) / 20 ‘ This is to try different increments to find the LLCR

Count = 1

Count_of_Iterations = 0

Linear_Interpolate_Option = True

pv_debt_service = 0

NPV_of_Debt_Service = 0

Debt_Balance = debt_amount

Start_LLCR_Factor = 1

End_LLCR_Factor = max_llcr

re_start: ‘ After you have found a NPV that exceeds the amount, go back and restart

Count_of_Iterations = Count_of_Iterations + 1

‘ The first few iterations use the slope and intercept method

If Count_of_Iterations > 5 Then GoTo slope_intercept: ‘ Start with simple raw iteration

‘—————————————————————————————————————————

‘—————————————————————————————————————————

‘

‘ This is the key loop for finding the LLCR Factor that Results in PV of Debt Service = Debt

‘

‘—————————————————————————————————————————

‘—————————————————————————————————————————

For LLCR_Factor = Start_LLCR_Factor To End_LLCR_Factor Step Increment_to_LLCR_Factor ‘ Go from small to big and go until hit the debt amount

```
Count = Count + 1
last_pv = NPV_of_Debt_Service
Last_LLCR_Factor = LLCR_Factor - Increment_to_LLCR_Factor ' Rember the last increment because that may be the good one
```

‘ Create the 2 x 2 matrix for the Interpolate

```
period_range(1) = 1 ' This is table for interpolate
period_range(2) = end_time
dscr_range(1) = llcr * LLCR_Factor
dscr_range(2) = min_dscr
compound_factor = 1
pv_debt_service = 0
Debt_Balance = debt_amount
For i = 1 To tenure
debt_period(i) = i
interest_expense(i) = Debt_Balance * interest_rate(i)
Debt_Balance_Array(i) = Debt_Balance
If Linear_Interpolate_Option = False Then Curved_DSCR(i) = interpolate_look_up1(i, period_range, dscr_range)
If Linear_Interpolate_Option = True Then Curved_DSCR(i) = interpolate_look_up_linear1(i, period_range, dscr_range)
ebit = ebitda(i) - dep_exp(i)
ebt = ebit - interest_expense(i)
taxes(i) = ebt * tax_rate
cfads(i) = ebitda(i) - taxes(i)
If Curved_DSCR(i) <> 0 Then debt_service(i) = cfads(i) / Curved_DSCR(i) - other_debt_service(i) ' Now compute the debt service as pv of cfads
repayment(i) = debt_service(i) - interest_expense(i)
repayment_sum(i) = repayment(i) ' Dimensioned for the
Debt_Balance = Debt_Balance - repayment(i)
compound_factor = compound_factor * (1 + interest_rate(i)) ' Overall debt IRR
pv_debt_service = pv_debt_service + debt_service(i) / compound_factor ' Accumulate PV of debt service over the debt life
Next i
NPV_of_Debt_Service = debt_amount - pv_debt_service
If NPV_of_Debt_Service > 0 Then Exit For ' When NPV moves to positive, stop the loop
```

Next LLCR_Factor

‘ This is after you have exited the FOR loop and you will change the increment

Start_LLCR_Factor = LLCR_Factor – Increment_to_LLCR_Factor ‘ Try a new LLCR Factor

End_LLCR_Factor = LLCR_Factor + Increment_to_LLCR_Factor

Increment_to_LLCR_Factor = Increment_to_LLCR_Factor / 10 ‘ Reduce the Increment to get more precise

GoTo re_start:

slope_intercept:

‘

‘ Now try slope and intercept

‘ Slope is pv_debt_service/LLCR_Factor

‘

If (LLCR_Factor – Last_LLCR_Factor) <> 0 Then _

Slope = (NPV_of_Debt_Service – last_pv) / (LLCR_Factor – Last_LLCR_Factor)

```
Intercept = NPV_of_Debt_Service - Slope * LLCR_Factor
If Slope <> 0 Then LLCR_Factor_final = -Intercept / Slope
sum_prod = WorksheetFunction.SumProduct(debt_period, repayment_sum)
If debt_amount <> 0 Then average_life = sum_prod / debt_amount
output1(1, 1) = LLCR_Factor_final
Goal_Seek_for_LLCR_Factor = output1
```

End Function