The economy should be circular. Your debt sizing shouldn’t.

The economy should be circular. Your debt sizing shouldn’t.

We’ve all been there.

The ominous three-tone ping of doom. The maze of blue arrows crisscrossing each other like a failed Tobias Fünke cat’s cradle. The pop-up dialog box that haunts our dreams and wakes us from our fitful sleep, sweat-drenched and screaming:

The dreaded circular reference.

A circular reference in Excel occurs when a formula refers to itself. This may happen directly – for example, entering =A1 in cell A1 – or indirectly, where the circular reference occurs through two or more sequential formulas which eventually link back to their starting point. If A1 is set to equal B1, B1 is set to equal C1, and C1 is set to equal A1, the combination of those three formulas is circular.

Within project finance models, there are some calculations which are inherently circular. Pivotal180 students will recognize construction funding costs and yield-based flip tax equity sizing as two such instances. When a circular reference occurs, modelers have three choices:

1. Replace the circular formula(s) with a non-circular alternative which may not be 100% correct but provides an answer accurate enough for decision-making.
2. Build a macro to automate a process (usually copy-and-paste) which removes the circularity.
3. Enable iterations within Excel’s ‘Options’ menu, allowing circular formulas to be calculated.

Depending on modeler preferences and the impact of the circularity in question, either of option 1 or 2 may be an appropriate choice. But to choose option 3 and enable iterations? To paraphrase The Bard: that way madness (and errors) lies.

Yes, Microsoft Excel can solve circular models, iterating calculations over and over until equilibrium is reached. Some modelers feel comfortable with circularities, but at Pivotal180, we strongly recommend against allowing them for several reasons:

• Circular models – especially big ones – can take a long time to recalculate and solve, because Excel has to repeat calculations over and over to reach a final result.
• Data tables – hallmarks of any well-designed model – will take even longer to update, as each case requires Excel to resolve again.
• Goal seeks may not work or may solve to multiple different solutions on different computers or from different starting points.

Above all, most circular references are created accidentally, with the modeler not realizing the relationship between all of the calculations involved. When iterations are enabled, Excel will no longer warn the user about any new circularities created with the friendly dialog box above. This can lead to new circularities the modeler is unaware of, which almost always results in errors down the line.

One calculation that is often circular in many models we encounter is a most critical one: debt sizing. But while many of us in the renewable energy industry aim to enable a less wasteful and more circular economy, any Pivotal180 Renewable Energy Project Finance Modeling graduate can attest that debt sizing need not be circular at all.

Circular debt sizing

Spreadsheets may be two-dimensional, but you don’t need to understand Membrane Theory in order to see that debt sizing shouldn’t be a flat circle. Many modelers, though – beginners and experienced alike – make that mistake.

In contrast to most calculations which run forward (left to right) through the model timeline, circular debt sizing is done in reverse, from right to left. This can be seen within the debt account. Where traditionally each period’s opening debt balance would equal the prior period’s closing balance, in a circular debt sizing model each closing debt balance is equal to the next period’s opening balance. In the last model period, for example, the closing balance is equal to the (blank) opening balance in the next column, and so on from right to left, until the first period closing debt balance is equal to the second period opening balance.

Traditional project finance debt sizing always starts with cashflow available for debt sizing (CADS or CFADS), which is then divided by a lender-assigned debt service coverage ratio (DSCR) to determine the total maximum debt service in each period. The next step is to determine how much of that maximum debt service is needed to pay interest in each period, and therefore how much is left to repay principal on the loan. Interest expense is a function of the interest rate multiplied by the opening loan balance in each period. Interest is then deducted from the maximum debt service to determine how much principal can be repaid. This is where the circular approach breaks down.

Using the first period as an example, the closing balance would be equal to the period 2 opening balance. In order to calculate the opening debt balance in period 1 – which is, ultimately, the size of the loan – debt repayments are added to that period’s closing balance. Debt repayments, though, are calculated as maximum debt service (CADS/DSCR) less interest, and interest is calculated on the opening debt balance – the very value we were trying to solve at the start! In other words, the (A) opening balance depends on (B) repayments, (B) repayments depend on (C) interest, and (C) interest depends on the (A) opening balance. This circularity, as shown in Exhibit 1, repeats in every year of the debt account.

Exhibit 1: Circular debt sizing; red arrows indicate circular calculations, while green arrows indicate non-circular ones

While this approach – deriving the opening balance in each period from the sum of that period’s closing balance and scheduled principal repayments – seems logical, it is inherently circular. Debt sizing is often the first financing calculation done in a model, and choosing to enable circular calculations at such an early stage is very likely to result in new ones being built later that the modeler does not notice.

Fortunately, though we may be simple beings who experience time linearly, a more linear approach can size debt just as well and without circular references.

The present (value) is a gift

Equity investors are intimately familiar with the concept of present value (PV). When evaluating an investment opportunity, modelers often calculate the net present value of forecast project cash flows using the NPV function. The NPV of a project’s future cash flows discounted at the investor’s hurdle rate yields the present value of the project to that investor – in order words, how much they would be willing to buy the project for.

What practitioners of circular debt sizing models might not realize is that lenders are, like equity, just investors who earn a guaranteed hurdle rate – their interest rate. Considered in that way, the trick to sizing debt simply becomes discounting a set of future cash flows promised to the lender at the interest rate.

As mentioned earlier, project finance debt sizing always starts with dividing CADS by a DSCR to determine the total maximum debt service in each period. That maximum debt service number – which will then be split into interest and principal – is the total cashflow paid to the lender in each period! Armed with that understanding, debt sizing is just the present value of those cash flows.

Exhibit 2: Non-circular debt sizing; all calculations are non-circular

As you can see, the same debt size is calculated using this method, but we avoid the trap of circular references.

But wait, my model is quarterly and has a variable interest rate – what should I do?

If you want to learn more about modeling debt sizing and other critical concepts to become a world-class project finance modeler, enroll in a Pivotal180 course! We offer a range of training programs for modelers of different backgrounds and experience levels:

• Renewable Energy Project Finance Modeling
• Infrastructure & Project Finance Modeling
• Introduction to Project Finance Modeling

Looking to take your modeling skills up a notch? Our Advanced Debt short course covers a range of other debt modeling concepts including:

• leverage limits
• cash sweeps
• leverage sensitivities
• multiple covenant debt sizing
• sources and types of debt
• loan agreements and terms
• loan life coverage ratio (LLCR) and debt covenants
• debt service reserve accounts (DSRA).

Check out the links below to learn more about Pivotal180 including all of our financial modeling courses and services. Come model with us!

• Additional Short Courses – including our new program on post-IRA tax equity!
• Tax Equity Modeling
• Intro to Battery Storage & Financial Modeling
• Financial Modeling Advisory & Consulting Services