Excel Tips - Iterative Calculations

Importance of Excel Iterative Calculations

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.