Iterative calculation Excel

Have you ever noticed that your Excel formulas are not updating or you see the Excel recalculate notice? Frequently when working in a complicated Excel file, you will notice that the calculations do not seem to be updating when changing an assumption. You often see the word “Calculate” at the bottom left of the screen or the dreaded Circular Reference Error pop up box may appear. More than likely, this is due to iterative calculations not being enabled.

What are Iterative Calculations?

Iterative calculations are repeated calculations until a specific numeric condition is met. Iterative calculations help Excel find the solution to formulas by performing the same calculation repeatedly using previous results. By analyzing the previous results, Excel can find the likelihood of possible solutions. In most institutional-quality financial models in Excel, there are calculations that need to be circular. This means that a formula directly or indirectly refers to its own cell. If there is a circular reference in your file, the formula is stuck in a loop calculating repeatedly, attempting to find a solution. When iterative calculations are enabled the Excel file stops calculating after a determined amount of iterations and the circular formula has found a stable result.

When do I need Iterative Calculations?

At Top Shelf Models (TSM), we use iterative calculations everyday to calculate construction interest. For example, in our development models, the construction budget sums the hard and soft costs, financing fees, and the construction interest. The construction interest is calculated based on the interest rate of the loan which is derived as a percentage of the budget. In short, the construction interest refers to the total construction budget and the total construction budget refers to the construction interest. I.e., a circular reference. Without iterative calculations turned on, the Excel file would continually calculate and never reach a solution. In turn, this causes the rest of the model to not calculate fully. By enabling iterative calculations, the circular reference is avoided. Typically, when I am analyzing real estate models, I often see that the author has hard coded the construction interest instead of running a calculation. This can create a variance of millions of dollars and can simply be fixed by turning on iterative calculations.

How to Enable Iterative Calculations in Excel

The first step in enabling iterative calculations in Excel is to select “File” in the top left hand of the ribbon. Next select options at the bottom left of the screen. An “Excel Options” pop up will appear. On the left-hand side select “Formulas.” Under calculation options, select Enable iterative calculation. Usually I set the “Maximum Iterations” to 100, but you can set this to any desired number. When selecting the number of iterations, keep in mind that the higher the iteration the longer the calculation. Choosing a large amount of iterations can sometimes crash Excel or have the recalculation stake a long time. Select “OK” once you have turned on iterative calculations and set your preferences.

Why do we need Iterative Calculations?

Iterative calculations is an important feature within Excel that very few know about. It is a powerful tool that can be used to solve circular references in order to find stable results. By enabling iterative calculations circular references are a thing of the past.

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions on iterative calculations or if he can help you with your modeling needs.

Eric BerginSeptember 13, 2022Top Shelf Models, LLC

Facebook0 Twitter LinkedIn0 Reddit

If you want to enable iterative calculation in Excel, you have come to the right place. Here, we will walk you through 2 easy and quick steps to do the task smoothly.

Table of Contents

Download Practice Workbook

Enable Iterative Calculation.xlsx

What Is an Iterative Calculation?

When repeated calculations occur until a specific numeric condition is met, it is called iterative calculation. This calculation uses previous results to solve a problem. And the calculation runs repeatedly. Iterative calculations help Excel find a solution quickly. Therefore, we need to enable iterative calculation in Excel in many situations.

2 Steps to Enable Iterative Calculation

In this article, we will describe 2 steps so that you can enable iterative calculation effortlessly. Here, we used Excel 365. You can use any available Excel version.

Step-1: Using Formula to Initiate Iterative Calculation

The following table shows the values of Cost Price, Selling Price. Here, we will enable the iterative calculation. After that, we will calculate the Other Expense and Profit.

Steps:

• First, we will write the following formula in cell C6 to calculate Other Expenses.

=C7/4

Here, C7/4 will divide the Profit by 4 and find out the Other Expense. The result will be $0.

• After that, press ENTER.

• Afterward, we will type the following formula in cell C7.

=C5-C4-C6

Here, C5-C4-C6 returns the Profit in cell C7. This subtracts the Cost Price and the Other Expense from the Selling Price. The result is $0.

• Then, we will press ENTER.

As soon as we press ENTER, a warning arrives. This indicates that we have to enable iterative calculation.

Here, we will see a join arrow mark colored blue in cells C6 and C7. This indicates that iterative calculation will occur in these cells.

Read More: How to Allow Circular Reference in Excel (With 2 Suitable Uses)

Step-2: Using Excel Options to Enable Iterative Calculation

Now, to resolve the circular reference warning, we will enable iterative calculation from Excel Options.  And this will calculate the Profit and Other Expenses.

• First, we will go to the File tab.

• Then, we will select Options.

An Excel Options window will appear.

• After that, we will select Formulas.
• Afterward, we will mark Enable iterative calculation.

Here, you can set Maximum Iterations and Maximum Change according to your needs. We keep these as it is.

• Then, click OK.

Finally, we can see that a calculation took place in cells C6 and C7. And we can see the values for Other Expense and Profit.

Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)

Things to Remember

• You should limit the iteration number according to your needs. While more precise results come from a higher iteration count, this can take a huge amount of calculation time.
• Along with that, if we do not enable iterative calculations in Excel, a warning will appear. This is because circular references are most of the time considered user errors.

Conclusion

Here, we tried to show you how to enable Iterative Calculation in Excel. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website ExcelDemy to explore more.

What does enabling iterative calculations do?

How do you use the iteration function in Excel?

What is the maximum change in Excel iterative calculation?

How to do iterative calculation in Excel Mac?