Videos and files on this page introduce structuring and debt sculpting in project finance. Developing a model that can efficiently evaluate these project finance debt structuring issues like sculpting with a DSRA can be tricky. This page introduces structuring and sculpting through addressing fundamentals of sculpting and use of the LLCR to compute sculpted repayments when the debt to capital is in place. Circular references that arise from taxes and sculpting are also covered. These issues provide an introduction structuring that provides a basis for the more complex subjects covered on the next page.
Modelling is not About Fancy Excel Formulas, Artistic Colouring or Using Solver with VBA, it is about Understanding Finance
- If you think that addressing the complex structuring issues are about being really good in excel, you are wrong.
- If you think you need to be really good at creating VBA code to solve the issues, you are wrong.
- If you think you can develop models that produce debt repayments from structuring with some kind of copy and paste macro, iterative technique or the solver add-in, you are wrong.
- If you do not understand that the NPV of debt service from COD on-wards is equal to the nominal value of the debt, you have no business making a financial model that evaluates structuring.
I am sorry to be so harsh, but I have learnt these things the hard way. If you look at some of the more complicated issues below you should see that you first need to understand some financial equations like the fact that the NPV of debt service at the interest rate is equal to the debt balance at COD. Without this, you models will be a big mess.
Introduction and Overview of Modelling Sculpting and Structuring Issues: Become a Top Modeller Rather than a Copy and Paster
This page is long and I do not in any way shape or form apologize for this. Some of the subjects like incorporating DSRA cash changes in sculpting formulas; adjusting debt sizing for fees on an LC that is used for the DSRA or incorporating multiple debt issues with different tenures and interest rates are complex. There is simply no way to address these things in a five minute video which seems to be the standard these days. Further, if you do not understand some of the finance theory (like why DSRA movements should really be an adjustment to debt service), then you probably should not try to make a model.
Sculpting debt and sizing debt together with accounting for interest income on a debt service reserve account, fees on letters of credit, multiple debt issues, balloon payments, income taxes with interest expense deductions, alternative timing of operating and financing cash flows, consideration of DSRA changes as cash flow available to pay debt service and other issues is addressed in the videos below.
To effectively and artistically solve problems with these issues I think you need two things.
- First, you absolutely need to use and understand equations for sculpting (not only the basic sculpting equations of Target Debt Service = CFADS/DSCR and Debt at COD = NPV(Interest Rate, Target Debt Service). There are different equations for changing timing, reflecting multiple debt issues, reflecting debt fees, and incorporating changes in the DSRA. Instead of trying to force things with excel techniques, equations should be used.
- Second, because of multiple circular references that arise from taxes, interest income, fees and changes in the DSRA I think that you should use my method of functions rather than clumsy copy and paste macros.
This will make you sculpting much more elegant from an artistic perspective. This lesson set walks through very basic sculpting exercises to modelling challenges that I think are quite difficult. A video providing an overview of the structuring and sculpting issues is presented just below the button.
There is one key file where I put the financial formulas, modelling examples and the VBA code for cases where you run into circular references. You can file this file on the google drive in the Project Finance Section under exercises and then Section D for the Sculpting course. This file with debt structuring mechanics, UDF’s and formula is available for download by pressing the button below.
The screenshot below illustrates where you can find the file if you have requested the google drive. Most of the project finance models are in Chapter 1. There are a few other files in this folder that are legacy files from earlier tries at explaining some of the sculpting concepts. The file for this exercise is really the key file that will allow you to become a top modeller rather than an average old copy and paster. The concepts in this file named “Sculpting Final” are behind the circular template that you can add to any of your models. If you really want to become good at this I suggest in all modesty that you sign up for my sculpting course.
Before addressing the complex issues I have begin with some simple cases. The first couple of exercises deal with pretty easy debt sculpting stuff involving fundamental project finance equations and computing present value with changing interest rates. But things get more difficult as the DSRA comes into play along with LC fees and other issues. After this introduction, subsequent exercises move into much more advanced stuff.
Sculpting Exercise 1: Fundamental Case with No Taxes, No DSRA, No LC Fees, No Debt to Capital Constraint, a Single Debt Issue and Constant Interest Rates
If you have not put sculpting in one of your models, or you have forced sculpting with some kind of solver equation or goal seek equation, you should work through an exercise using the following two equations,
Compute target debt service as : DS = CFADS/DSCR
Compute debt as NPV of the target debt service using the interest rate
The screenshot below demonstrates how you can work through the sculpting and structuring issues. In this case you should create a debt switch (if you want to be fancy and call it a mask, you can but I have no idea why you call the true and false switch a mask). Then the debt service is the CFADS divided by the DSCR multiplied by the switch. The closing balance in the loan schedule is the present value of the debt service and the repayment is the target debt service minus the interest expense.
The video below walks through the fundamental sculpting equations. The video is designed so that you can fill in the blanks in yellow by yourself as discussed above. This is the easiest case and it is the base for much more complicated situations that deal with debt to capital constraints, DSRA movements, L/C fees etc.
Sculpting Exercise 2: Non-Constant Interest Rates
Some term sheets include step-up credit spreads. Others allow a portion of the interest rate to be un-hedged. In these cases, the first thing I hope you think about and understand is why structures such as a step-up structure occur. What is generally happening is a strong incentive to re-finance. It is probably absurd for you to leave the step-up credit spread in the base case scenario.
In cases when the interest rate changes, a simple present value formula cannot be used. Instead, an interest rate index can be created that accounts for prior interest rate changes as follows:
Interest Rate Indext = Interest Rate Indext-1 x (1+Interest Ratet)
Debt Amount at COD = ∑ CFADSt/Interest Rate Indext
The video below walks through how to create an interest rate index and then use the SUMPRODUCT function to multiply the index by the target debt service. You can use the SUMPRODUCT with a divide sign which is very helpful — SUMPRODUCT(Debt Service/Compound Factor). Note that you cannot use a discounting factor that separately values the debt service. In general when discounting cash flows with different rates you should compute some kind of compounding factor. To see this think about inflation Zimbabue. You cannot discount a future cash flow without considering what happened to the currency in the past.
Sculpting Exercise 3: Use of LLCR with Debt Capacity Constraint
One of the fundamental principles of project finance structuring is that debt size may be either driven by a maximum debt to capital constraint or a minimum DSCR. If you do not fully understand this, you should not be making project finance structuring models. If the maximum debt to capital constraint limits the amount of debt rather than the DSCR as in the example above, then the formulas in the previous section will not work.
A typical situation in sculpting is where the debt to capital is given, but repayments are driven by sculpting. In this case you cannot input a DSCR which itself determines the debt size. Instead, you can use the idea the with sculpting, the LLCR = DSCR. Here, the issue is how to compute sculpted debt repayments when debt is sized with the debt to capital ratio and the DSCR is not given. When the Debt is Sized by Debt to Capital the LLCR = NPV(CFADS)/Debt can be used to size the debt, if you assume that the debt repayments will be structured to provide the same DSCR over time. Formulas in this case include:
Principle 1: is that with sculpting, the LLCR is equal to the DSCR
Principle 2: The LLCR can be expressed as PV of CFADS/Debt
Using these two principles, you can derive the target DSCR using the above formula where the amount of debt is defined as the total project cost multipled by the maximum debt to capital ratio. This is because the PV of debt service is equal to the debt at COD. Once you have the DSCR applied you can use this DSCR in sculpting:
Target Debt Service = CF/LLCR
LLCR = NPV(Interest Rate, CFADS)/Max Debt from Debt to Capital
DSCR Applied = MAX(Target DSCR,LLCR with Max Debt)
These equations are demonstrated in the screenshot below from the same sculpting file that is available for download from the button in the introductory section. Note that this time you need to compute the PV of CFADS and not only the PV of the debt service. Once you compute the PV of the debt service, put it in the yellow slots.
Important note: You do not necessarily have to assume that the repayments will be designed on a sculpted basis. Instead, you can design the sculpting so that the minimum DSCR will still be obtained, but that: (1) the total debt will be repaid (NPV of debt service) = debt; and (2) the average loan life is less than a specified loan life in the term sheet or loan agreement. You can go to a more advanced page to see how this can be accomplished.
The video below works through this example in case you need further explanation. The video includes discussion of how you can make some kind of message that documents whether the DSCR constraint or the debt to capital constraint is operative. In the circular reference complete template you can choose whether the debt to capital constraint is applied, the DSCR constraint, or both. Note the LLCR constraint as describe only works when the DSCR is constant over time.
.
Exercise 4: 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 itmes, 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.
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.
The second issue addressed is how to use sculpting if a debt constraint applies. Other issues include how to apply models where monthly operating cash flows are used and the debt repayments are semi-annual. Some of the most difficult issues involve interest income and fees on letters of credit. The various lessons evaluate pros and cons of copy and paste macros relative to functions for solving circularity that arises with sculpting. The different lessons build up to a final case includes options for funded DSRA accounts, net operating loss, bullet repayments and other issues. If you can work through all of the exercises in this lesson I think you can be considered a genuine top notch modeller. I have completed just about all of the videos for this lesson but they are in the process of editing the files.
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.
.
.
.
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
| Subject in Structured Sculpting Course | Sheet Name in Sculpting Course File | Video Link | ||
| Sculpting Course Overview — Introduction to Course File, Videos and Exercises | Overview | |||
| NPV-Basic | ||||
| Use of LLCR with Debt Capacity Constraint | Debt to Cap and LLCR | |||
| Multiple Debt Issues and Sculpting | Multiple Debt Issues | |||
| Sculpting Discount Rate Adjustment with Monthly Model | Sculpting and Timing | |||
| Sculpting and Debt Fees | Sculpting and Fees | |||
| Sculpting DSRA and Interest Income | Interest Income on DSRA | |||
| Sculpting and Fees on Letter of Credit for DSRA | LC Fees and Copy/Paste Macro | |||
| Function for LC Fees or Funded DSRA with Interest Income | LC Fees, Interest Income | https://www.youtube.com/watch?v=7HZA9EDcBaw | ||
| Sculpting with Income Taxes | Sculpting and Taxes Function | https://www.youtube.com/watch?v=Gdbc6-Vx1cw | ||
| Sculpting with Taxes Adjusted for Depreciation | Taxes and Depreciation | |||
| Sculpting with Bullet Payment and DSRA used as final payment | Sculpting with Bullet Payment and DSRA | |||
| Changes in DSRA as Cash Flow Available for Debt Service | DSRA Movements | |||
| Other Sculpting Videos | File for Other Sculpting | Video Link | ||
| Modelling Debt Sculpting – Simple Introduction | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=gAlIIzgIUyc | ||
| Debt Sculpting – Changing Interest Rates and Varying Tenor | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=bVfFiJ5WUeM | ||
| Debt Sculpting with Interest Income on DSRA | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=Z5B6r-oelBo | ||
| Sculpting and Income Taxes – Copy and Paste Method | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=91Mzu0jhM5U | ||
| Sculpting and Income Taxes – Function Method | Exercise 17: Debt Sculpting – Advanced | https://www.youtube.com/watch?v=E4TT6tymIVE | ||
| Sculpting with Negative Cash Flows and Pct of Time Function | Negative Sculpting.xlsm | https://www.youtube.com/watch?v=YsmN_KFKVeo | ||
| Sculpting with Negative Cash Flows and Constraints | Negative Sculpting 1.xlsm | https://www.youtube.com/watch?v=cZf9Cr_gqOE | ||
| Sculpting and Leverage Constraint | Sculpting with Constraint.xlsm |
Files associated with Comprehensive Sculpting Analysis Lesson Set
Exercises associated with sculpting are included in a single comprehensive file which is the first file named “sculpting course.” The file which is the only one you really need includes many pages that work through the various different sculpting issues. The file has a lot of different tabs that move from simple cases without circular reference problems to more complicated cases. For the various tabs, I have put a second tab for you to try and complete the exercise. As shown below, the completed exercise shown on the video is on one sheet and the exercise is shown on the subsequent sheet with a yellow tab. Key take away points for each part of the sculpting analysis are shown at the top of exercise page. If you can fill in each of the exercises including the functions and macros you should feel really good. Other files are a mishmash of different sculpting exercises and files that I have constructed over the years that are probably redundant but may help you out a bit.
Sculpting with Negative Cash Flow 1.xlsm
Exercise 14 – Debt Sculpting.xls
Sculpting with Negative Cash Flow.xlsm
Sculpting and Leverage – Simple.xlsx
Exercise 16 – Basic Sculpting.xlsm
The videos use a few key formulas to derive debt repayments and the level of debt from a target DSCR that are described below:
Basic Formulas
One of the main ideas about the repayment process in project finance is that the modelling is much more effective when you combine formulas with other excel techniques. If you try and solve these things with a brute force method that uses a copy and paste method or goal seek things will get very messy. Formulas used for repayment and debt sizing are listed below: The fundamental two sculpting formulas are:
(1) Target Debt Service Per Period = CFADS/DSCR
(2) Debt Amount at COD = PV(Interest Rate, Target Debt Service)
Multiple Debt Issues (Bullet Repayment)
Another issue arises when there are multiple debt issues and one of the debt issues (defined as Last or the sculpting capture issue) is used for sculpting. In this case the basic formula can be adjusted and the process if straightforward. You can start with the DSCR formula and derived the debt service for the last formula. Note that if you are sculpting two debt facilities at the same time and these facilities have different interest rates and different tenures, then the process is difficult because for the NPV formula you need a common interest rate.
(8) DSCR = CF/(Other DS + Last DS)
(9) Other DS + Last DS = CF/DSCR
(10) Last DS = CF/DSCR – Other DS
(11) DS for Last Facility = CF/DSCR – Other DS
Note that if there is a bullet repayment at the end of the debt term (say 15% of the repayment), then the bullet repayment can be considered a separate debt facility. So, if the bullet repayment is 15% then the PV of the repayment is a separate facility with interest over time etc. The NPV of the remaining debt should subtract the interest and the repayment on this separate debt. Since the bullet repayment affects the amount of sculpting and the NPV of the debt multiplied by 15% drives the bullet repayment, the bullet repayment causes a circular reference.
Adjusting LLCR for Debt Fees
Sculpting and Changes in the DSRA balance including Final Repayment
After working through letters of credit for the DSRA, taxes, interest income and other factors that cause difficult circular references, the final subject addressed is using the DSRA to repay debt. A similar result occurs when changes in the DSRA are included in CFADS. Incorporating these changes in a financial model without massive circularity disruptions can be tricky, but it can be solved by separately computing the present value of changes in the DSRA. Changes in the DSRA can be modelled using the following equations:
(18) Debt Adjustment = PV(Interest Rate, Change in DSRA/DSCR)
(19) Repayment = Repayment from Normal Sculpting + Change in DSRA/DSCR