Do Circular References Still Have Us Stuck on Copy-Paste?

Let’s be honest, copy-paste is still the go-to for dealing with circular references in our financial models. We all know it’s not perfect, right? Back in the day, I thought I was a genius for using some copy-paste code. Iterative models seemed like the dark ages! But that dream didn’t last long. I faced a dead-end with copy and paste when I built my first project finance model for a solar project in Burkina Faso back in 2010.

The challenge was a typical issue with renewable projects, meaning needing to size and sculpt the debt based on the P90 energy level, then getting the equity IRR at P50, having multiple tranches of debt, different instruments, etc. Talk about circular! That’s when I knew copy-paste just wouldn’t cut it. Thankfully, I stumbled upon Professor Edward Bodmer’s Parallel model technique, a transparent and flexible solution.

The Challenge: Getting Everyone on Board

The kicker? Convincing clients and auditors to accept a model with Ed’s UDF Parallel model. It’s a real struggle.

Why Fight a Solution That Works?

Here’s the thing that blows my mind. We have a solution to a real-world problem – not some academic thing – and few seem interested! I even went above and beyond for a private equity client. I showed them a model with Ed’s UDF alongside the copy-paste version (even though it wasn’t in my contract, just to test it out). I explained how the UDF could make the model faster, avoid spreadsheet errors, and even automate debt sizing and sculpting.

The manager loved it, but then he turned to his financial modeler and asked about maintaining the model with a Parallel UDF. The modeler shut it down flat. Conversation over.

UDF Parallel Model Success Story: A Client Wins

Let me tell you about the power of the UDF Parallel model in action. In 2018, I collaborated with Ed for 3 days to learn how to modify the code. We then implemented the UDF parallel model in a financial model for a hydro project. We were at a crucial stage – tariff negotiation. Imagine the struggle with a copy-paste model! The UDF parallel model, however, was a game-changer. Within a few seconds, we could size the debt, sculpt it, test different debt terms, and calculate the step-down tariff. This agility was impossible with copy-paste.

The UDF Parallel model became my go-to throughout the negotiations, from tariffs to termsheets with lenders. For transparency, I kept the copy-paste version alongside the UDF model. This way, users could see that the UDF code wasn’t some magic trick but replicated the familiar Excel formulas while eliminating the circular reference issue.

Here’s the kicker: everyone loved it! In fact, during one negotiation round, I jokingly removed the UDF from a model version. The client immediately called me out, wanting the “fast model” back. Happy users often indicate a successful product or solution!

The LAMBDA Hope and the Road Ahead

With the LAMBDA function out now, everyone’s buzzing about using it for circular references. Maybe with some time, it’ll offer a real alternative to copy-paste. But let me tell you, replicating the user-friendliness, transparency, and flexibility of Ed’s well-developed code with LAMBDA? That’s going to take some serious work.

Give UDF Parallel Model a Try 

If you’re open to exploring this alternative method, especially if you work for an auditing firm, contact us! We can test it out together using your own project finance models.