Recursively Breaking Circularity

With the arrival of spilled array formulas in Excel it is now possible to both use good practice modelling techniques such as FAST and dramatically reduce the number of formulas in your workbook. When combined with the judicious use of custom LAMBDA() functions to centralise generic calculation logic, the result is a professional model that not only is much faster to build but also much easier to audit. As inspiration, Craig Hatmaker has a great series of articles and YouTube videos explaining the "5g Modelling" concept on his Beyond Excel website.

The main way this is achieved by only using only one formula for each time series. But this can get tricky because each formula must be able to calculate the entire time series based only on the inputs initially provided. This makes the traditional corkscrew calculation block, where the closing value is simply a SUM() of the opening value from the prior period and period movements impossible. Instead you need to use the new SCAN() function to generate the closing balance for each period based solely on the initial opening balance and period movements.

Other modelling challenges such as figuring out the movements of a revolving credit facility require techniques such as using the difference of a cumulative sum of cash flows. Once you learn the basic techniques though, it's straight forward to incorporate them into your model and get rid of the excess formula bloat that causes problems.

The Problem of Circularity

But all experienced modellers know that there is one problem in financial models that is hard to avoid: circularity. The classic example of this is to calculate the interest expense for a period on a revolving credit facility. Calculating this requires being able to calculate the size of the cash sweep at the end of the period to the facility, which requires knowing the cash available, which requires knowing the interest expense.

Over the years the main approaches to solving circularity in financial models has boiled down to the following approaches:

1. Simplifying the model - e.g., using only the opening balance instead of the average balance.
2. Solving algebraically - best approach, but not always possible.
3. Creating a worksheet that manually iterates to the answer for every period - disadvantages of both formula bloat and the need to estimate in advance how many iterations are needed.
4. Using iterative calculations - fastest approach but a bad idea for many reasons not least of which is that Excel will stop warning you about any additional circularities you add into your model.
5. Adding a "break" or hardcoded value - this is achieved by using VBA or Office Script to iteratively copy and paste a live calculated value until the delta between the live and hardcoded values is below some threshold. It's simple and reliable, but with the downside that with every assumption change the process needs to be repeated.

An additional ingenious approach using a comprehensive VBA User Defined Function (UDF) to create a parallel model has been detailed by Edward Bodmer . This approach can take a while to get your head around, but can handle an astonishing amount of financial modelling complexity. The only problem is that it is reliant on VBA so is restricted to the desktop versions of Excel.

The LAMBDA() Approach

As part of developing an internal course for financial analysts, I recently created a three statement model based on the FAST modelling approach but with spilled array formulas. It's a relatively simple model, but it needs to cope with a revolving credit facility, the ability to earn interest on cash balances as well as incur costs on average borrowed funds, and deal with a tax asset created from prior/current period losses.

In the first version of the model, an Office Script was used in place of the traditional VBA script to create an interest expense break that would work anywhere. While functional the frustration of having to run the script every time an assumption was updated soon became apparent and I looked for a better way.

Inspired the VBA UDF approach and the sample formula in Craig Hatmaker 's article LAMBDA Recursion, I set out to see if a LAMBDA() UDF could be the universal solution I had been looking for. The result is a prototype LAMBDA() UDF called CircularityBreakλ() with the following inputs that can be single values or an array of period values:

• CashBeforeIntTax - the free cash flow before interest and tax.
• NonCashExpenses - non-cash expenses that can be deducted for tax purposes.
• OpeningDebt - the opening balance of the revolving credit facility.
• DebtRate - the nominal annual interest rate for the RCF.
• OpeningCash - the opening balance of cash on hand.
• [CashRate] - optional the nominal annual interest rate for excess cash.
• [DaysInPeriod] - optional the number of days in the period which is used to convert the nominal interest rates to daily compounding effective rates.
• [OpeningTaxAsset] - optional the opening balance of the tax asset.
• [TaxRate] - optional the tax rate to apply to earnings after non-cash expenses.
• [CashMinimum] - optional the minimum amount of cash to hold at the end of the period.
• [Labels?] - optional boolean value as to whether or not to include labels for the formula output.

You can find the full code for the prototype LAMBDA() on my Gist, but here is what it looks like in practice to use prior to full testing:

As you can see it returns a table of values that can optionally be brought into another part of the model with the CHOOSECOLS() function. If you want to see it in action check out the CircularityBreakλ Tester.xlsx workbook.

Tricks of the Trade

The function is a bit long to paste in a LinkedIn article but it includes a couple of interesting tricks I wanted to highlight:

1. The power of the accumulator in the REDUCE function is used to pass the multiple closing balance sheet values into the next iteration of the loop.
2. The recursive function, cbRecursionλ, is defined within a LET statement within the helper LAMBDA() of the REDUCE() function. While this may not be optimal in terms of efficiency, it does mean that the function has access to all of the previously defined variables without having to explicitly pass them. In fact the only parameters for this function are the function itself, avgDebt, and avgCash.
3. The output value ( _outputMulti ) is wrapped in a LAMBDA() with no parameters. The advantage of doing this is that it means that this complex step is not evaluated unless explicitly called (it becomes a Thunk). So if earlier error checks result in an error or some other value being returned, time isn't wasted on the recursion.

Final Thoughts

Creating this function has been a very interesting exercise that has really confirmed to me the potential for LAMBDA() UDFs to replace many VBA UDFs in practice. In particular, the power of the accumulator in REDUCE() to pass multiple values and the use of recursion to iterate has really impressed me. With a function like this the desktop constraint of finance models may soon be a thing of the past.

Feel free to use and modify this function with attribution in your own models, and let me know in the comments if you find it useful!