This webpage addresses the problem that occurs when taxes are included in sculpted debt analysis. With the payment of corporate income taxes the issue of sculpting changes from a simple matter of evaluating a couple of mathematical formulas (debt service = CFADS/DSCR and PV of Debt Service = Debt), into a circular reference issue. The problem arises because income taxes are deducted in computing CFADS. But the income taxes are affected by interest expense and therefore the level of debt. I discuss the issue of this circular reference below and show how you can solve the issue with a copy and paste macro or a user defined function.
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. Some of 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).
.
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.
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
Playlist on Sculpting Issues
If you are in the mood for torture or maybe if you are having trouble sleeping, you can look through the sculpting playlist. I have put together various sculpting videos that I have made over the years. I have tried to put the more basic videos first (with the exception of the very first). The videos all apply the fundamental formula that the PV of debt service over the repayment period is equal to the debt size at the beginning of the repayment period (i.e. the period just prior to the commercial operation date). Over time I have learnt more about sculpting issues that can involve curved DSCR’s, multiple debt issues, incorporation of on-going fees, alternative debt sizing options, complex income taxes and computation of DSRA moves as part of the CFADS. I hope I have covered a lot of these issues in the videos. As with other items, you can always send me an email at edwardbodmer@gmail.com.
.
.