Using Iterative Calculations in Excel

The iterative or looping calculation is the repeated calculation of a worksheet using previous results until a specified numeric condition is met. It simulates the Excel VBA loop.

When we want to use iterative calculation, we create a formula that either directly or indirectly refers to the cell containing the formula. We deliberately set up circular dependency.

However, by default, Excel cannot calculate a formula that refers to the cell containing the formula. Such an occurrence is known as a circular reference and Excel considers it an error.

Circular references

The following example dataset has a formula in cell E2 that has a circular reference:

The formula =C2-B2-D2 in cell E2 refers to cell D2 which contains the formula =E2/5 which refers to cell E2. Thus, the formula =C2-B2-D2 indirectly refers to the cell in which it is contained. This circular reference can cause the formula to recalculate indefinitely.

When we press the Enter key to display the result Excel does not calculate the formula but instead displays a warning message:

Enable iterative calculations

Although by default Excel considers circular references as errors, there are times that we may want to use circular references because we want our formulas or functions to iterate or recalculate until a specified numeric condition is met. Iterative or looping calculations can slow down the computer considerably and therefore they are normally turned off in Excel.

We have to enable iterative calculations if we want to use them. Excel allows us to enable iterative calculations in the Excel Options dialog box and control the maximum number of iterations and the acceptable maximum change or accuracy of the results.

We use the following steps to enable iterative calculations in Excel:

1. Click File.

2. Click Options on the sidebar.

3. In the Excel Options dialog box, select Formulas, and under the Calculation options section click Enable iterative calculation.

There are two additional options under Enable iterative calculation:

• Maximum Iterations. This setting determines the number of times Excel recalculates the workbook. It is set to 100 by default.
• Maximum Change. This option specifies the maximum difference between values of iterative formulas. The smaller the number the more accurate the results. The default value is 0.001.

Iterative calculations stop at the numeric condition that is met first. By default settings, Excel stops calculating either after 100 recalculations or when there is a maximum difference equal to or less than 0.001 between the results.

When for example we accept the default settings, we are telling Excel, “Do the recalculation a hundred times then stop, or do it as many times as it takes for the result to stop changing by more than 0.001 then stop.”

4. We can leave the default settings as they are or change them to the numbers we desire and click OK. Please note that the higher the number of Maximum Iterations and the smaller the number of Maximum Change, the longer the time calculations take.

With iterative calculations enabled, Excel now calculates the profit using the formula with a circular reference in the example dataset we referred to earlier:

Example use cases of iterative calculations

Calculate the future value of an investment

We give an example to contrast the use of a formula and iterative calculations to calculate the future value of an investment.

Suppose we invest $ 25,000 with a 15% annual interest rate. What will be the value of our investment at the end of 5 years?

We can apply the formula: (Gross figure) x (1 + Interest rate per period) as shown below:

The formula can work well when we are working with a small dataset. It is not efficient when working with large datasets.

We use the following dataset to demonstrate the use of iterative calculations to achieve the same result more efficiently:

We use the following steps:

1. Enable iterative calculation as explained earlier and set the Maximum Iterations to 4 to find the value of the investment at the end of 5 years.
2. Select cell E5 and type in the formula =E3*(1+E4) as follows:

3. Press the Enter key to enter the formula.

4. Select cell E3 which contains the value of Initial Investment and replace the value with the cell reference E5 to create a circular dependency between cell E3 and E5.

5. Press Enter. Iterative calculation happens and the value of the investment at the end of 5 years is returned.

Add timestamps to a dataset

Suppose we want to add timestamps to the bank accounts entered in the table below.

We use a circular reference to add a time stamp when the details of a new bank account are added.

We use the following steps:

1. Enable iterative calculation in the workbook, with the default Maximum Iterations and Maximum Change values.
2. Select cell G2 and type in the formula:

Press the Enter key.:

Note that the formula returns the 00/01/1900 00:00 time stamp for bank account details that are already entered. When we enter a new record, we get an updated time stamp as shown below:

Explanation of the formula

The formula checks cell A2 and whenever it is not empty, it runs the circular formula F(G2=””,NOW(),G2). This circular formula fetches the value returned by the NOW function only if cell G2 doesn’t already have a value, thus generating timestamps.

Conclusion

The iterative calculation is the repeated calculation of a worksheet using previous results until a specified numeric condition is met. It simulates the Excel VBA loop.

To use iterative calculation, we create a formula that refers to the cell that contains the formula either directly or indirectly. We set up a circular dependency on purpose to solve a mathematical problem.

However, by default, Excel cannot calculate a formula that refers to the cell containing the formula. Such an occurrence is known as a circular reference and Excel considers it an error because the recalculation can go on indefinitely.

To use iterative calculation, we have to enable iterative calculation in Excel and control the maximum number of iterations and the maximum acceptable change between the formula results.

We also looked at some use cases of iterative calculations.