Corkscrew LAMBDA Template

Create complex corkscrew calculations easily with this template.

What is the difference between complex Corkscrews and Simple Corkscrews?

A simple corkscrew has flows (a series of values such as Revenue or Expenses) that are independent of the opening balance. In simple corkscrews the flows are added or subtracted from an opening balance then summed to create a cumulative closing balance. That can be done with a simple SCAN() LAMBDA.

A complex corkscrew calculates flows based on the opening balance or some other flow which are then totaled to create an ending balance, which, because this is a corkscrew, is a cumulative balance, or running total. To perform this type of calculation requires a method like the one discussed here.

Can you show me an example?

An amortization schedule is a simple example of a complex corkscrew. We need the opening balance to calculate the interest amount. To do this with a dynamic array, we need to calculate each column one at a time and stack the results together horizontally. The animation below emphasizes the stacking of columns in this approach.

We can do this with recursive LAMBDAs but using REDUCE() is simpler and easier to understand. Here is the template completed for a simple loan amortization schedule.

The parts of the template are:

1. Calculate anything that is independent of the opening balance or other flows.
2. Use REDUCE( initial value, array, lambda) to perform the period calculations. We do not need an initial value. The array is a column counter, which, in this example is a number for each month in the loan's Term.
3. Extract single values from array arguments. In this example our opening balance will be the principal amount when processing the first column (n=1). For all other columns, the opening balance is the last period's closing balance which is in the last row and last column: TAKE( Accumulator, -1, -1).
4. Perform period calculations.
5. Vertically stack the values into a column.
6. If this is the first column, the LAMBDA's result is the stacked column, otherwise, we horizontally stack the column against the prior columns.

The results of our LAMBDA() are placed in the Accumulator. Thus, the first time the LAMBDA() is called by REDUCE(), the LAMBDA() returns a single column. That goes in the Accumulator. The second time REDUCE() calls the LAMBDA(), the Accumulator has one column in it until the LAMBDA() finishes, at which time, the Accumulator will have two columns. This process continues until we have processed the last month in the Loan's term.

Summary

I use the 5g version of this template to quickly create many of my 5g functions. It is shown below. The 5g version contains inline help, version control, and lots of comments. If you intend for anyone other than yourself to use your LAMBDA, for their sakes, please consider following 5g standards. Want to know more about this template? See 5gModeling.Com