When solving a math problem, there are two broad ways to go about doing it: a direct way, and using iteration.
The simplest possible example is probably multiplying a number by an integer. You can either use multiplication directly (say, 9 * 39 * 3), or you can iteratively add nine together three times (9 + 9 + 99 + 9 + 9). Both methods arrive at the same result, though the direct method is easier and faster.
However, it isn’t always easy, or even possible, to use a direct method, meaning iteration may be your only option. Calcapp’s XIRR function, for instance, uses iteration to calculate its result.
Using iteration in Excel
Iteration is fairly easy to use in a spreadsheet like Microsoft Excel. Let’s say that we have a starting balance of $10,000 and that we gain an unrealistic one percent in interest every day. We wish to calculate what our balance is on every day for the next 28 days.
(For the moment, let’s ignore the fact that there is a straight-forward direct way of calculating the result, using either the FV function or the ^ operator.)
In Excel, we have a starting balance in cell B1:
The formula of cell B2 references the value in cell B1 and multiplies it by 101% (B1 * 101%B1 * 101%). The formula of cell B3, in turn, references the value in cell B2 and multiplies it by 101% (B2 * 101%B2 * 101%).
It continues like that, all the way to day 28. Excel makes it easy to create all these formulas, using its Auto Fill feature.
Using iteration in Calcapp
If we have solved a problem in Excel using iteration, how can we get Calcapp to calculate the same results?
One way would be to add many hidden fields to a form screen, using the exact same type of formulas as used with Excel. While that works for a few hundred iterations, you will find that adding thousands or even tens of thousands of hidden fields to an app makes Calcapp Creator, our app designer, sluggish (though the resulting apps tend to run well). Also, duplicating and subtly changing thousands of formulas — without the benefit of Auto Fill — means a lot of tedious, repetitive work.
A better solution is to use the REDUCE function, which we introduced last year. (Excel gained initial support for REDUCE a few months before Calcapp did, which was too late for us to be influenced by Microsoft’s work. That means that they are slightly different.)
This function reduces a value to a single value using a user-supplied formula. It takes an array as its first parameter and then runs the formula fragment given as its second parameter once for every element of the array.
REDUCE keeps track of the value returned from the second parameter and adds it to its so-called accumulator (which is initially set to the value you provide as the third parameter). The result returned from REDUCE is the value of the accumulator after all elements of the array have been processed.
This formula calculates the sum of all values of the array { 1, 2, 3 }{ 1; 2; 3 }, 6:
The initial accumulator is set to 0 (the third parameter above). The
second parameter, Accumulator + ValueAccumulator + Value, is then
invoked with Value set to 1, which causes the second
parameter to return 0
+
1 = 10 + 1 = 1. As a result,
REDUCE stores 1 as the accumulator before continuing.
REDUCE then invokes the second parameter once more, this time with
Accumulator set to 1 and Value set to 2.
This time around, the second parameter returns 1 + 2 = 31 + 2 = 3, which again is
stored as the accumulator.
Finally, the second parameter is invoked with
Accumulator and Value both set to 3,
meaning that the second parameter returns 3 + 3 = 63 + 3 = 6, which is also
the value returned from REDUCE.
(If you want to learn how to calculate averages, minimum and maximum values and more, refer to our REDUCE documentation.)
We can use the same technique to calculate interest. This formula returns the balance on day 28 — $13,212.91 — provided that the interest is set to one percent:
The goal is to get REDUCE to run the second parameter 28 times. In order for that to happen, the first parameter must be an array with 28 elements. The SEQUENCE function is used to return such an array. (Only the size of the array is important, not the values of its elements, which are not used here.)
Returning all intermediate values as an array
The formula above and the Excel spreadsheet both return the same number for day 28. However, what if we also need to retain all the intermediate values? The goal is for an array to be returned containing these values (exactly matching the Excel spreadsheet):
Here’s a version of the formula above that does just that:
There’s a lot going on above, so let’s take it one step at a time.
First, the initial accumulator, in the form of the third parameter,
is set to { 10000 }{ 10000 } instead of
10000. In other words, it is set to an array containing
the single element 10000. This enables REDUCE to return
an array of numbers instead of a single number.
When the second parameter accesses the Accumulator
value, it is an array, initially containing just 10000.
The goal is to add elements to this array at the very end. To achieve
this, | is used, which is
formally known as the array concatenation operator.
| is similar to &, which joins text strings together. This formula returns “Test”:
This formula returns { 1, 2, 3, 4 }{ 1; 2; 3; 4 }:
The part on the right-hand side can also be a single value. This formula returns { 1, 2, 3 }{ 1; 2; 3 }:
In the REDUCE formula above, | is used to add
elements to the array which is ultimately returned.
Accumulator is initially set to { 10000 }{ 10000 }, meaning
that what | does is that it adds an element that follows
the first element, 10000. When the second parameter is
run subsequently, every new calculated value is added to the end of
the accumulator array.
The final part of the puzzle is how we calculate the next element to add to the array. The first REDUCE formula, introduced at the beginning of this post, multiplied the last value by 101 percent, and this is what we need to do here too.
The Accumulator value accessible to the second
parameter, though, no longer represents the running total. Instead,
the accumulator is an array of all values calculated so far. What we
need is the last element of this array.
In order to retrieve it, we use this formula fragment:
The INDEX function returns an element from an array at the given index. INDEX({ 10, 20, 30 }, 2)INDEX({ 10; 20; 30 }; 2), for instance, returns 20.
The Index value available to the second parameter is a
special value indicating the position of the array element currently
being processed. It happens to be equal to the size of the
Accumulator array. As a result, the formula above
returns the last element of the array, which is what we want.
The final order of business is to multiply this value by 101 percent, after which REDUCE returns the array containing the balances of all days.
To access the balance of a specific day, use INDEX. Let’s assume that there is a field named DayNumber (possibly with a slider), holding the number of the day the user is interested in. This final formula returns the balance on that particular day: