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 that Includes Sculpting Exercises from Basic 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
.
When you make a UDF like this for taxes, you need to include all the stuff that goes into the tax calculation.  If there  in mezzanine debt that has interest deductible for interest, this must be included; if there is a limit on the taxes as a function of EBITDA, this must be included; if there is an NOL this must be included; if the NOL has an expiration (one of the really tricky modelling issues), this must be included.  The video below illustrates this process with only tax depreciation.

Further Information and Learning: Request Resource Library (Free), Find Details About In-Person Courses, Make Suggestions on Course Subjects and Locations

The reason I have worked on this website is so that you consider an in-person class which is by far the best way you can become a top project finance analyst.  If you click on the button below, you will be forwarded to a website that describes some of unique courses.

Click on this Button to Find Information on Courses that Will Enable You Become a Top Modeller Instead of a Copy and Paster by Understanding UDF's and Gaining the Skill to Modify the Comprehensive Project Finace UDF Template

If you click on the right button you can quickly send an e-mail to edwardbodmer@gmail.com and request the resource library (no charge).  The google drives include more case studies, financial models, risk analysis files and other materials than are included on the website. I promise not to pester you if you do send me an e-mail.

I would really like to know what courses may be most interesting to you and where you would like the courses to be held. If you click on the left button below, I have a form that I will use to try and put together a class with a few people.

If you are a student, I would be honoured to come to your university or your business club and give you a hands-on guest lecture. If you click on the button on the left below, you can do me a big favor by giving me some information about your institution.


Click on this Button to Send Me and E-mail and Request Resource Library that Contains Google Drives and Zipped Files (No Charge for this) Click on this Button and Do Me a Favour by Suggesting Your Preferred Course Locations, Subjects and Possibilities of Guest Lectures and Your University