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 cashflows in each period. This ensures that the DSCRs are less volatile than may otherwise be the case.

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 manual adjustment is often unnecessarily overcomplicated using a combination of Visual Basic and the Goal Seek functionality within Excel. The algebraic approach is simpler than it sounds and generally should be the first attempted solution.

The Algebraic Approach to Debt Sculpting

We need to keep in mind that either of the following two relationships can be re-arranged

DSCR = Cash Available / (Principal + Interest)

LLCR = NPV (Cash Available) / Debt Balance

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

Debt Sculpting Applications

Common instances where sculpting is required include: 

• Irregular, but well understood cashflows, for example in Oil and 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 cashflow and how to debt sculpts 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 (31-Dec-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) 

Screenshot #1: Example of Debt Sculpting to Achieve Target DSCR

Step 3 – Re-Calculate the DSCR

The last step for checking purposes is to re-calculate the DSCR to make sure that the Target DSCR is achieved in every period.

Screenshot #2: Re-calculation of the DSCR

Step 4 – Create Graphs as 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 cashflow, thus the sculpted debt repayment needs to be matched to the pattern of the cashflow in each period.

Screenshot #3: Debt Sculpting graphs

Summary of debt sculpting calculations

Sculpting is often overcomplicated in many financial models, however it can be handled quite simply using the straight forward logic above.

DSCR (Debt Service Cover Ratio) Definition : The ratio of the Cash Available to service and repay debt obligations to the Principal and Interest obligations themselves.

LLCR (Loan Life Cover Ratio) Definition : The ratio of the Net Present Value of Cash Available for Debt Service during the life of the loan to the Debt Balance outstanding in any period.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...