## About Brian DeChesare

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.

In Project Finance, Debt Sculpting means that the required principal repayment in each period changes based on the available cash flows, the interest expense, and the targeted coverage ratio; Debt Sizing means that you set the initial Debt balance such that it is completely repaid on a certain date based on these sculpted principal repayments and the other Debt terms.

Debt Sculpting vs Debt Sizing Definition:In Project Finance, Debt Sculpting means that the required principal repayment in each period changes based on the available cash flows, the interest expense, and the targeted coverage ratio; Debt Sizing means that you set the initial Debt balance such that it is completely repaid on a certain date based on these sculpted principal repayments and the other Debt terms.

In most financial models for “normal companies,” such as leveraged buyout models and debt vs. equity models, the initial Debt balance is based on a multiple of EBITDA or a percentage of the purchase price.

**But in Project Finance, Debt tends to be sculpted and sized based on the future cash flows because these cash flows are very predictable due to contracts that lock in prices and volumes, such as power purchase agreements (PPAs) in the energy sector.**

Some assets, such as solar and wind plants, are *seasonal*, so their cash flows fluctuate each month, but they are still *predictable* because we know the high and low seasons in advance.

Linking the **Debt Service** – the Interest Expense + Principal Repayments – to the cash flow in each period **reduces the risk for the lenders** because it allows for more repayment when the cash flows are stronger and less when they are weaker.

Equity investors also like this approach because it often means they can **use more Debt** to fund deals, which increases their potential returns if a development or acquisition performs well.

Debt based on a multiple of the project’s initial EBITDA **would ignore future cash-flow growth** and, therefore, increase the required Equity by reducing the initial Debt.

By linking the Debt to these future cash flows, investors ensure the maximum Debt possible is used, within the constraints desired by the lenders.

- Debt Sculpting vs Debt Sizing – Presentation Slides (PDF)
- Simple “Cash Flow Only” Debt Sculpting vs Debt Sizing with No VBA or Circular References (XL)
- Debt Sizing and Sculpting with VBA/Macro Support (XLSM)

**0:00:**Introduction**1:09:**Part 1: The TL;DW of Debt Sculpting and Sizing**3:07:**Part 2: Simple Debt Sculpting Example**4:49:**Part 3: DSCR-Based Debt Sizing**7:10:**Part 4: LLCR-Based Debt Sizing**9:25:**Part 5: VBA to Automate Debt Sizing and Avoid Circ Ref’s**17:24:**Recap and Summary

“Sculpting” Debt is not complicated; the tricky part is sizing it initially.

Here’s a simple example of Debt Sculpting: Let’s say the Cash Flow in a period is $150, and the targeted Debt Service Coverage Ratio (DSCR) is 1.5x.

The asset can, therefore, support Debt Service of $150 / 1.5x = $100.

If the initial Debt balance is $800 with a 10% interest rate, the Interest Expense is $80.

Therefore, the “sculpted” principal repayment in this period is $100 – $80 = $20. This will increase each year as the Cash Flow grows and the Interest Expense falls.

The **hard part** is the Debt Sizing – in other words, determining that this initial balance *should be* $800 (see below).

The simplest approach to Debt Sizing is to base it on a key credit metric in Project Finance: **The Debt Service Coverage Ratio (DSCR) or the Loan Life Coverage Ratio (LLCR).**

The required formulas are as follows:

**Cash Flow Available for Debt Service (CFADS)**= EBITDA – Maintenance Capex +/- Change in Working Capital – Cash Taxes**Debt Service Coverage Ratio (DCSR)**= CFADS in One Year / Debt Service in One Year**Loan Life Coverage Ratio (LLCR)**= Present Value of All CAFDS in Remaining Debt Tenor / Current Debt Balance

The LLCR is the “Present Value version” of the DSCR, and **the two are equivalent when we size and sculpt the Debt based on one or the other** (with some exceptions in more advanced cases).

We’ll start with a simple Debt Sizing example based on the DSCR.

If we’ve projected the CFADS for an asset, Step 1 is to “guess” the initial Debt balance ($800 in this ongoing example).

Then, we calculate the Interest Expense, Max Debt Service, and Debt Amortization in each period based on the interest rate (10%) and the minimum or targeted DSCR (1.50x here).

Max Debt Service = CFADS / DSCR, so it is $150 / 1.5x = $100 in Year 1.

We “back into” the Max Debt Amortization based on the Max Debt Service minus the Interest Expense this year:

**With these assumptions, the Debt balance reaches $0 by Year 10, but the DSCR is 1.52x in this final year, which means we used too little Debt initially.**

Therefore, we need to *resize* the initial Debt balance.

To do this, we can set the initial balance to a higher number, such as $850, and then use **Goal Seek** (Alt, A, W, G in PC Excel) in the Year 10 cell to find the initial balance that results in a $0 balance in Year 10.

This is the simplest possible method for Debt Sizing, but it lacks flexibility and is cumbersome to use in models because we need to use Goal Seek whenever anything changes.

We can resolve some of these issues by sizing the Debt based on the **Loan Life Coverage Ratio (LLCR)** instead.

We can solve for the initial Debt balance using algebra:

**Initial LLCR**= Present Value (PV) of All CFADS in Entire Debt Tenor / Initial Debt Balance**LLCR * Debt Balance**= PV of All CFADS**Debt Balance**= PV of All CFADS / LLCR

Here’s the Excel setup:

The Present Value from Years 1 to 10 is $1,201.4, so the Initial Debt Balance = $1,201.4 / 1.50x = $800.9.

**We do not need to change anything else because the targeted DSCR and targeted LLCR are equivalent.**

**So, we can use the targeted LLCR to determine the Max Debt Service in each period; it’s still CFADS / 1.50x.**

This method is better than Goal Seek because everything updates automatically if the CFADS, Interest Rate, or LLCR change.

However, it works only if we ignore the **circular relationship** between Interest, Taxes, and Debt Sizing, which comes up when we calculate the Cash Flow Available for Debt Service (CFADS) “the real way.”

To explain these issues, we must calculate CFADS by starting with Revenue, subtracting Operating Expenses, and then deducting Maintenance CapEx, Cash Taxes, and the Change in Working Capital.

Since the Interest Expense is tax-deductible, the Cash Taxes will change based on the Interest Rate, the initial Debt balance, and the Debt principal repayments.

If we set it up this way and make the Interest Expense a tax deduction when calculating Cash Taxes, we get the following “circular death loop”:

We could solve this issue by **enabling circular references in Excel**, but that makes models less stable, and some groups do not accept models with circular references (for more, see our tutorial on circular references in Excel).

Another option is to use **pre-tax numbers** and ignore the tax deduction for the Interest Expense, but that results in less accurate Debt balances.

The **real way** to fix this problem is to use VBA to create a “copy / paste macro” that “tricks” the model into using a hard-coded of the CFADS rather than the calculated one.

To do this, we’ll **copy** the calculated CFADS, **paste** it as hard-coded values, and **feed** this hard-coded version into the model.

Then, we’ll calculate the CFADS series again, copy and paste them as hard-coded values, feed them into the model, and keep doing that until the calculated and pasted CFADS are the same.

Essentially, we’re “tricking” Excel into thinking that the pasted CFDAS is the same as the calculated CFADS, thereby avoiding true circular references.

To set this up, start by creating a separate “Macros” tab in Excel that can store the calculated and pasted CFADS, and **name each range of cells** so you can easily refer to them in the macro.

We’ll also need a “Check / Comparison” cell that sums up each series and determines the difference between them:

Next, on the main Model tab, link the pasted CFADS from the Macros tab and change all the formulas referencing the CFADS to use the *pasted version* instead:

Also, change the NPV formula used in the “Present Value of CFADS Over Debt Tenor” in the top area to reference these pasted CFADS values.

To **automate** this process, we can write a macro to repeat these steps until the sums of the two cell ranges (“CFADS_Paste” and “CFADS_Links”) are equal (i.e., the “Check / Comparison” cell will be 0).

Start by going to the VBA Recorder (Alt, L, R in PC Excel) and setting this macro to the Ctrl + Shift + S shortcut. Then, go to the Macros tab, do the manual copy/paste, and stop the recording.

Go into the VBA Editor (Alt, L, V), delete the recorded code, and enter the following code to make the process repeat until the pasted and calculated CFADS equal each other:

*Do*

*Range(“CFADS_Paste”).Value = Range(“CFADS_Links”).Value *

*Loop Until Range(“Check_CFADS”).Value = 0*

It should look like this in the VBA Editor:

Now, whenever we change an assumption, such as the minimum LLCR, the Interest Rate, or the Tax Rate, we can press Ctrl + Shift + S to change all the CFADS values and resize the initial Debt balance automatically.

This article is just an introduction to the topic.

There are dozens of more advanced points related to Debt Sizing and Debt Sculpting, such as:

**Cash flow sweeps**, i.e., optional Debt principal repayments based on cash flows.**Variable issuance and maturity dates**, which require a system of “flags” to mark different periods.**Monthly and quarterly models**, which shift the formulas.**Multiple Debt tranches**, such as 1^{st}lien and 2^{nd}lien loans that are both sized and sculpted – or perhaps only one tranche is, and the other is not.**Construction Loans**and how they are sized during the development period of a project and linked to the post-development financing.

We cover all these points and more in the introductory and more advanced case studies in our Project Finance & Infrastructure Modeling course.

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.