Embedded Files
Description
Many 5g functions require an opening balance, performing calculations on that opening balance that then result in a closing balance. The closing balance for the current period then becomes the opening balance for the next period. This common calculation structure is referred to as a Corkscrew.
This template is specifically designed for creating corkscrew calculations. To achieve this with dynamic arrays requires processing each column one by one and stacking them together as we progress through each period.
Learning Objectives
At the end of this course, learners will know:
What a corkscrew is and how to create it
Prerequisites
Understanding of the 5g basic source structure template.
Microsoft's free add-in: Advanced Formula Environment (AFE)
Discussion
This is derived from the 5g basic source structure template. To learn more about the numbers in white, go back to that class.
This template is designed to create a two dimensional array by stacking columns together. This process allows us to reference prior columns in the result which is required for corkscrew calculations. It leverages REDUCE() to avoid the dreaded 'array of arrays' error when we try to do this using SCAN().
The Template
Below is BXL's 5g staker template. It can be downloaded using AFE from this URL:
https://gist.github.com/CHatmaker/3e6cba6414fadfabfa3725255da2ba74
NOTE! The circled numbers are NOT part of the code. There are added to mark sections for discussion.
REDUCE( [intial value], array, lamba) - This function's arguments are:
Initial Value - When REDUCE() starts, it places this value in the accumulator. It is optional but often it is better to provide an initial value even if the LAMBDA function (see third argument) ignores it.
array - REDUCE() processes each value in this array one by one. For corkscrews, this is often a counter which is used as a flag when starting up and an index with which to retrieve other values from other arrays.
lambda - The LAMBDA() must accept two arguments from REDUCE():
Accumulator - This holds the initial value when REDUCE() starts. After processing the first value in the array, REDUCE() places the LAMBDA() result in the Accumulator.
Array Value - REDUCE() will send each value, one at a time, to LAMBDA() for processing.
In this case, we are using array as a counter; thus, we can use the well accepted n name for our counter's value.
Individual Values out of Arrays - This section of our LAMBDA handles two special situations:
_Opening - When REDUCE() starts, we want to use its initial value. After processing array's first value, we want _Opening to contain the ending balance. The ending balance must be in the Accumulator. The Accumulator is the last result of our LAMBDA() and will be an array. The ending balance can be anywhere in the Accumulator's last column but it is most often in the last row. TAKE( Accumulator, -1, -1) gets that value from the Accumulator.
_Argument2 - This could be named anything. If it contains a row of values, then we can get the appropriate value from that row using:
INDEX( ArgumentName, n)
Calculations - Here is where the real work happens. These calculations are for the one period currently being processed.
Formatting the Result - After performing our calculations, we need to place the results we want to display in a column
Result - If this is the first value in our array, we need to return our stacked column which will be this LAMBDA()'s first result and will be placed in the Accumulator. If this is NOT the first value in our array, we need to horizontally stack this column on the end of the Accumulator. In this fashion, we add a column to the Accumulator for every value in REDUCE()'s array. When all values have been processed, the Accumulator will have a two dimensional array.
Example
Example
Using this template, we can create a function that produces the simple loan amortization displayed in the animation at the top of this post. It doesn't animate. The animation is only to emphasize how this method works. It stacks columns horizontally, one by one, until we have a finished array. The code for that function is below.
NOTE! Only three lines of this code perform 'calculations' (_Payment, _Interest, _Closing). The rest is inline help and array manipulation (selection and stacking).
Summary
Summary
Using this template we can easily convert traditional formulas to a simple function that anyone of any skill level can use in any workbook to produce a simple dynamic amortization schedule.
Assessment
Did you get the important points of this section? Take this quiz:
1) What are the arguments for REDUCE() (select all that apply)?
a) Accumulator
b) Initial Value
c) Array
d) LAMBDA
2) What does REDUCE() pass to the LAMBDA when REDUCE() starts (select all that apply)?
a) Initial Value
b) Accumulator
c) Array
d) The first value in the array
3) What does REDUCE() pass to the LAMBDA after REDUCE() has processed the first value in the array (select all that apply)?
a) Initial Value
b) Accumulator
c) Array
d) The next value in the array
4) What is in the LAMBDA()'s Accumulator argument when it is called by REDUCE() for the first time?
a) Initial Value
b) The LAMBDA()'s previous result
c) Array
d) The next value in the array
5) What is in the LAMBDA()'s Accumulator argument when it is called by REDUCE() after the first time?
a) Initial Value
b) The LAMBDA()'s previous result
c) Array
d) The next value in the array
6) What is in the LAMBDA()'s second argument when it is called by REDUCE() the first time?
a) Initial Value
b) The LAMBDA()'s previous result
c) Array
d) The first value in the array
Assessment Answers
Assessment Answers
1) b, c, d
2) a, d
3) b, d
4) a
5) b
6) d
Page updated
Google Sites
Report abuse