Sculpting with Fixed Debt and Changing DSCR – User Defined Function – Edward Bodmer

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