Corality
View More Content
SHARE

Debt sculpting to target DSCR without VBA

Debt sculpting to target DSCR without VBA
CategoriesTutorials

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:


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)
 

Financial-modelling-of-target-principal-repayment
Screenshot 1: Financial modelling of target principal repayment


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.
 

Calculation-of-actual-repayment
Screenshot 2: Calculation of actual repayment


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.
 

Plot-of-DSCR
Screenshot 3: Plot of DSCR


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.

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.

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 Training Academy - SMART Campus

Visit our website to take advantage of various training courses and free resources to assist you in reducing spreadsheet risk and efficiently building your models with confidence.

Some of our related training courses for this topic include:

 


Rickard Wärnelid
by Rickard Wärnelid

Rickard's passion for financial modelling is built on specialist roles in the highly quantitative fields of derivatives and project finance, a career path complemented by an academic grounding in engineering physics. Born in Sweden and with global consulting and leadership experience, Rickard is an internationally recognised authority, speaker and thought-leader on the organisational benefits of best practice financial modelling.

Contact Rickard Wärnelid

view all