Debt sculpting tutorial workbook
The algebraic debt sculpting example from this tutorial, built out in Excel and ready to follow along with your own numbers. Excel binary workbook (.xlsb).
Enter your details and we will email you the workbook.
Loading the form…
The form did not load. Send us an enquiry and we will email you the workbook.
Debt sculpting means the principal repayments are set so that principal and interest match the strength and pattern of the cash flow in each period. This tutorial sets out an algebraic approach to debt sculpting and shows how to use it to reach a target DSCR.
Debt sculpting can be done in several ways. The most common are:
- Adjusting the principal repayment in each period by hand
- Solving the principal repayment algebraically to reach a target DSCR
The algebraic approach to debt sculpting
Two relationships can each be rearranged to solve for the amount being sculpted:
DSCR=cash availableprincipal + interest
LLCR=NPV (cash available)debt balance
Here DSCR is the debt service coverage ratio, LLCR the loan life coverage ratio and NPV the net present value. Targeting a DSCR or an LLCR, they become:
Principal=(cash available ÷ DSCRtarget)− interest
Debt balance=NPV (cash available)LLCRtarget
Or, for a target DSCR of 2.00x:
Principal=(cash available ÷ 2.00)− interest
For how sculpting differs from sizing, read our article on debt sizing and debt sculpting in project finance transactions.
Learn this in a room
Best Practice Project Finance Modelling
Build and sensitise robust cashflow models from scratch, faster in Excel. Hands-on, practitioner-led, in person or online. See dates and book your place.
When projects need sculpted repayments
Sculpting is commonly needed for:
- Irregular but well-understood cash flow, for example in oil and gas projects
- Seasonal demand, common in power, agriculture and manufacturing
- The ramp-up period of a new project, such as a toll road
- An unusual but expected payment, such as the major overhaul of an asset
Interest, always calculated as the interest rate multiplied by the opening balance, is not sculpted directly. Its amount and timing still depend on the principal repayments in every earlier period.
Debt sculpting to a 1.50x target DSCR, step by step
This example takes a project with irregular cash flow and sculpts the debt to reach a target DSCR of 1.50x.
Step 1: solve the principal repayment
The principal repayment is calculated as:
Principal=(CFADS ÷ DSCRtarget)− interest
Step 2: cap the principal at the debt balance
To make sure the debt is fully repaid by the final maturity date (30 June 2017 in this example), the principal from step 1 is adjusted:
Principal applied=MIN (calculated principal, debt balance b/f)
Step 3: recalculate the DSCR
The last step, as a check, is to recalculate the DSCR and confirm the target is reached in every period. Unless there are other optimisations to run, the DSCR for the final repayment will be higher than the target. Depending on your situation and constraints, this may or may not be acceptable, so check with your team.
Step 4: chart the result as a check
A chart is a useful check during sculpting. This one shows that the project has irregular cash flow, so the sculpted repayment has to follow the pattern of the cash flow in each period.
Why sculpting rarely needs VBA
Many financial models make debt sculpting more complicated than it needs to be. The logic above handles it simply. Once you understand how sculpting works, you can often solve it algebraically, with no VBA or macro at all.
Debt sculpting questions
What does debt sculpting mean in project finance?
Shaping the principal repayment schedule so that principal and interest match the strength and pattern of the project’s cash flow in each period, instead of repaying on a flat or fixed schedule however the cash flow varies.
Can debt sculpting be solved without VBA or a macro?
Yes, in many cases. Rearranging the DSCR or LLCR formula to solve for the principal repayment gives an algebraic solution, which avoids the circularity and complexity a copy-paste macro is otherwise used to resolve.
Why is the final period’s DSCR often higher than the target?
Because the final principal repayment is capped at the debt balance brought forward, so the loan is fully repaid at final maturity without overshooting. The final period therefore does not follow the same targeted formula as the earlier periods, and its DSCR often comes out above the target.
When is debt sculpting typically needed?
Wherever cash flow is irregular but reasonably well understood: oil and gas projects, seasonal industries such as power, agriculture or manufacturing, the ramp-up of a new asset such as a toll road, or a known future event such as a major overhaul.