Incorporating Taxes in Debt Sculpting and Simple UDF Functions

This page addresses the problem that occurs when taxes are added to sculpting.  With taxes the issue of sculpting changes from a simple matter of evaluating a couple of mathematical formulas, into a circular reference issue.  The problem arises because income taxes are deducted in computing CFADS and income taxes are affected by interest expense and therefore the level of debt.

There are a couple of key files where I put the financial formulas, modelling examples and the VBA code for cases where you run into circular references.  You can file these file on the google drive in the Project Finance Section under exercises and then Section D for the Sculpting course.  The files are available for download by pressing the button below.  The second file includes the comprehensive circular reference resolution (sorry that this sounds like a foreign language).

Excel File with Focused Separate Sculpting Exercises and Analysis from Basic Debt Sizing to Advanced with VBA

Sculpting and Circularity from Income Taxes with Deductible Interest

I have recently heard from some people that taxes are just too complicated to incorporate in the UDF method.  This is complete BS.  As with other items, as long as you can work through the stuff in excel, you can easily replicate the formulas in a UDF function.  Once again, the important thing is that you understand how taxes work, which items are deductible (the problem is that interest and fees are deductible and CFADS is after tax).  Incorporating a NOL calculation into the UDF is also easy. Indeed, if the only problem you are having with circular references is tax (which is very unlikely in a sophisticated model), then you should be able to write a UDF function for solving the problem in a few minutes.

I have included two videos below that demonstrate how to write a UDF to deal with taxes.  The first video is very simple and does not even include depreciation.  The second is a bit more complex with depreciation added in the equation.

Sculpting With Taxes when Debt Amount is Fixed

If the amount the debt is fixed and the period by period DSCR or the LLCR is derived, the circular reference from interest, debt balance and taxes does not go away. In this case the LLCR drives the debt service, but the debt service affects the tax and the CFADS. The PV of CFADS can be fixed with a copy and paste macro or a function can be used that is similar to the function in the case when the debt is not fixed.

Technical VBA Issues, Sculpting and Taxes

After a very simple case where circular references can be resolved without taxes, the case of sculpting and taxes is perhaps the simplest case of resolving a circular reference with a loop. This occurs because you have to work through the lifetime of the loan to establish the present value of debt service (which in turn comes from CFADS).  This is because the interest expense and therefore tax depends on the loan size.  But the interest expense and loan size also depend on the period by period CFADS.  So, you can create a loop as shown in the code below that works through the CFADS and the tax payment.  On top of this loop you can create an iteration loop that re-computes debt from the tax payments.

The box below demonstrates the VBA code you can create to resolve the tax circularity issue.  Note first that the EBITDA is read into the function as a vector.  This means in the function the EBITDA has an index and is an array.  A second point is that interest expense is computed first followed by taxes and finally by the CFADS. Finally, the target debt service, the repayment, the NPV of the debt service and the debt balance is computed.  These elements should be present in any

Function npv_taxes(int_rate, ebitda_vector, tax_rate, dscr)

Dim debt_service(100)

For iter = 1 To 10
 For i = 1 To ebitda_vector.Count
   interest = debt_balance * int_rate
   ebt = ebitda_vector(i) - interest
   taxes = ebt * tax_rate
 
   cfads = ebitda_vector(i) - taxes
   debt_service(i) = cfads / dscr
   repayment = debt_service(i) - interest
   debt_balance = debt_balance - repayment
 Next i
 
 debt_balance = WorksheetFunction.NPV(int_rate, debt_service)
Next iter

npv_taxes = debt_balance

End Function
.