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).
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 .
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
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