5g: Source Structure - Corkscrew

Function Name  - All 5g functions require a name. The template is named "CorkScrewλ." When creating your function, modify this name to suit your needs.

Function Arguments  - With rare exceptions, All 5g functions require arguments (aka parameters).  The template has three arguments: Opening, Gains, and Losses. 

• Opening - This is the closing amount from the last period before this model's timeline (if any). It is a single number.

• Gains - This is a row of values for each of the model's periods that increase the corkscrew's closing balance.

• Losses - This is a row of values for each of the model's periods that decrease the corkscrew's closing balance.

You function may require more than two flows, in which case, you will need to add more arguments for each flow. You should also change these names to better describe what they are.

LET(  - This is where our LAMBDA's formula starts. The LET() function allows us to split complex formulas into smaller formulas known as LET steps. LET steps are like formula cells. In traditional modeling we have a calculation block with several small formulas in cells. In 5g functions we have a function with small formulas in LET steps. 

Pre calcs  - This section is for performing calculations that help speed up, or simplify the main calculations. In this example we combine the flows into one row once, rather than combining the elements of each flow for each period in the Close formula. 

Close - A corkscrew calculation creates a pseudo circular equation when performed by dynamic arrays alone. I say pseudo because the value by value calculation flow is not circular. But Excel views an array as a single object, and not individual values within the array. So to Excel, a closing balance array that depends on as opening balance array and an opening balance array that depends on a closing balance array, is circular. To get around this situation, we perform all the calculations in one formula using SCAN(). 

SCAN()

SCAN is a LAMBDA helper function that processes each element in an array, one by one. It has three arguments. The first value is an initial value. The second is an array of values. The third argument is a LAMBDA function. 

SCAN's LAMBDA

There is a special case for the LAMBDA which is known as an "eta reduced LAMBDA." This special syntax allows us to omit entering the LAMBDA function and, instead, only enter the name of the function the LAMBDA would use. For example, if SCAN's third argument was LAMBDA( a, b, SUM( a, b)) we could simply enter SUM. Excel will know what to do.

SCAN's Calculations

On SCAN's first pass it sends its initial value and the first element from the array. The LAMBDA processes those two values and puts its result in an accumulator. On SCAN's subsequent passes, it sends the accumulator and another element from the array. SCAN repeats this until all elements in the array have been processed. 

In our template, the initial value is our CorkScrewλ's Opening argument. The second argument is our pre calc's (See #4) Flows array. These two values are summed and placed in the SCAN's accumulator. At the point, the accumulator holds the opening balance for the next period and so, when SCAN processes the second array argument, it is summed with the prior period's closing balance which is the current period's opening balance. SCAN is, essentially, performing the corkscrew calculation all by itself. 

In financial modeling, it is not sufficient just to calculate the results. We must also display the 'ingredients' of the calculations which are the opening, gains, losses, and closing so clients can visually see how the calculations work. And that takes us to the next LET step.

Open - Open is, essentially, our opening argument placed in front of our close array shifted over one column. HSTACK places opening next to Close. DROP removes Close's last value so the Open array has the same number of columns as all other arrays.

Result - At this point, we have our Open, Gains, Losses, and Close rows. VSTACK stacks these rows on top of each other which makes them look exactly like a traditional corkscrew calculation..

The last line in a LET function is what the LET function returns.