This page explains how you can construct a project finance model where there are multiple debt issues and one of the debt issues is used for sculpting. In the case where there is one debt issue with a balloon payment, the balloon payment can be split from the rest of the debt issue and then be modelled as a two different debt issues, one of which operates as the sculpted debt issue.
Formulas and mechanical techniques for a basic case of sculpting with two debt issues are included in the file that has various different exercises. You can file this file on the google drive in the Project Finance Section under exercises and then Section D for the Sculpting course. The file is also available for download by pressing the button below. This file does not address balloon payments which are a little more difficult because the size of the balloon payment affects the DSCR on the non-balloon portion which in turn affects the size of the balloon payment and you have a good old fashioned circular reference.
Formulas for Multiple Debt Issues
Very often, there is more than one debt issue in project finance transactions. 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.
You can use the following couple of equations to resolve the case where there are multiple debt facilities and repayment for one of the debt issues is determined from sculpting. For the equations, the term Other DS is the debt issue on the non-sculpted issue (which could be balloon portion of the sculpted issue). The term Sculpted Issue DS represents the debt service for the debt issue where repayments are determined from sculpting.
DSCR = CFADS/(Other DS + Sculpted Issue DS)
Other DS + Sculpted Issue DS = CFADS/DSCR
Sculpted Issue DS = CFADS/DSCR – Other DS
The modelling of multiple debt issues using the sculpting exercise file that you can download above is illustrated in the screenshot below. In this example there are three debt issues, the last one of which has sculpted repayments driven by sculpting from CFADS. If the debt is separated in this manner, developing sculpting is pretty easy. The problem comes when the first issue is a function of the sculpting itself. This is the issue with balloon payment.
Modelling Balloon Payments with Sculpted Debt Issues
If there is a bullet repayment at the end of the debt tenure (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.
You can see more details of how to model balloon payments in a separate section of the website. The button below provides a link to the balloon repayment section which includes VBA necessary to make the models.
Computing LLCR for Sculpting with Balloon Payments
If the amount of debt is fixed (maybe because of a debt to capital constraint) and the repayments are computed from the LLCR producing a constant DSCR, then adjustments must be made. First, I am sorry about all of the acronyms (LLCR, DSCR, constant DSCR). If you want the amount of repayments to add up to the loan that is reduced because of a balloon payment, then the LLCR that is used for sculpting will be higher. This is because the the addition of the repayments must sum to a lower amount. If the sum must be lower and the CFADS is the same, then the denominator must be higher.
To explain concepts like this I use an absurdly simple example. Assume that the interest rate is zero, the project cost is 1000 and CFADS is 600 for only two years. The reason for assuming a zero interest rate is that you can compute the NPV by a simple sum and that the debt service is just the repayments. In the case without the balloon payment, the LLCR is 600 x 2 divided by the assumed debt of 800. This produces an LLCR of 1.5 as shown in the screenshot below.
The second screenshot shows the same case that includes a balloon payment. In this case, the LLCR increases to 1.85 which produces an amount of debt of 650 instead of 800. The screenshot illustrates that the formula for the LLCR with balloon payments or for other debt can be expressed as follows:
LLCR = PV of CFADS/(Sculpted Debt – PV of Non-Sculpted Debt)
If there is interest on the balloon debt or the non-sculpted debt, the formula for the LLCR in the above simple examples is not correct. For an issue with a balloon payment, to the extent that the balloon repayments are not included in the sculpting formula, the balloon repayment should be expressed in terms of present value.
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