Debt Sculpting to Target DSCR without VBA

Debt sculpting is a commonly used term in project finance. It means that the principal repayment obligations have been calculated to ensure that the principal and interest obligations are appropriately matched to the strength and pattern of the cash flows in each period.

This tutorial outlines an algebraic approach to debt sculpting, debt sculpting applications and explains the use of debt sculpting to achieve target DSCR.

Debt sculpting can be achieved in several ways, the most common being:

• Manually adjusting the principal repayment in each period
• Algebraically solving the principal repayment to achieve a desired DSCR

The algebraic approach to debt sculpting

We need to keep in mind that either of the following two relationships can be rearranged:

• DSCR 1 = cash available / (principal + interest)
• LLCR 2 = NPV (cash available) / debt balance

Where:

• DSCR is the debt service coverage ratio
• LLCR is the loan life coverage ratio
• NPV is the net present value

In turn, this means that assuming we are targeting a DSCR or an LLCR:

• Principal = cash available / DSCR(target) – interest; or,
• Debt balance = NPV (cash available) / LLCR(target)

Or, for a target DSCR of 2.00x:

• Principal = cash available/ 2.00 – interest
• Principal = bal (Period 2) – bal (Period 1) = [NPV(cash)]1 /2.00 – [NPV(cash)]2 /2.00

You may also be interested in our post in project finance debt sculpting vs. debt sizing.

Debt sculpting applications

Common instances where sculpting is required include:

• Irregular, but well understood cash flows, for example in oil & gas projects
• Seasonal demand factors (common in power, agriculture, manufacturing industries)
• The ramp-up period of a new project, such as a toll road
• An unusual but expected payment, such as a major overhaul of an asset

The interest cost, always being calculated as interest rate x opening balance, is not sculpted directly, although its amount and timing will be directly influenced by the principal repayment schedule in all preceding repayment periods.

Example of debt sculpting to achieve target DSCR

An example below illustrates a project with irregular cash flow and how to debt sculpt to achieve the target DSCR of 1.50x.

Step 1 – Solve the principal repayment

To recap, the principal repayment is calculated as:

Principal = CFADS / DSCR (Target) – interest

Step 2 – Adjust the principal calculated in Step 1

To ensure that the debt is fully repaid by the final maturity date (30-Jun-17 in this example), the principal repayment calculated using the formula above is further adjusted as:

Principal (Applied) = minimum (calculated principal, debt balance b/f)

Step 3 – Recalculate the DSCR in the financial model

The last step for checking purposes is to recalculate the DSCR to make sure that the target DSCR is achieved in every period. Unless there are other optimisation procedures to run, you will find the DSCR corresponding to the final repayment is higher than the target. Depending upon your situation and constraints this may or may not be OK so check with your team.

Step 4 – Create graphs as a checking tool

Graphs are often useful during the debt sculpting process as a checking tool. The graph below clearly demonstrates that the project in this example has irregular cash flows, thus the sculpted debt repayment needs to be matched to the pattern of the cash flow in each period.

Summary

Debt sculpting is often overcomplicated in many financial models. However, it can be handled quite simply using the straightforward logic above. A fundamental understanding of debt sculpting means you can solve this algebraically in many situations.

1 Definition of debt service cover ratio (DSCR): The ratio of the cash available to service and repay debt obligations to the principal and interest obligations themselves.

2 Definition of loan life cover ratio (LLCR) : The ratio of the net present value (NPV) of cash available for debt service (CFADS) during the life of the loan to the debt balance outstanding in any period.

Corality Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

Some of the more popular courses that relate to this topic include:

• Project Finance: Concepts and Applications
• Financial Modelling Techniques for Valuation Analysis