In this article:

Iterative Calculation in Google Sheets: A Simple Guide for 2024

May 8, 2024

What Is Iterative Calculation in Google Sheets? 

Iterative calculation in Google Sheets is a powerful feature that allows you to have circular references in your formulas – that is, a cell can depend on its own value to calculate a new value. 

A circular reference occurs when a formula refers back to its own cell, either directly or through a chain of references. For example, if cell A1 has a formula that says =A1+1, it's a circular reference.

Iterative calculations can be used for various complex tasks like solving equations, creating amortization tables for loans, and running simulations or forecasts.

How to Enable Iterative Calculation in Google Sheets

By default, Google Sheets doesn’t allow circular references and will give you an error. To use them, you need to enable iterative calculations:

Step 1: Open the File Menu

Click on File in the menu.

iterative calculation google sheets

Step 2: Choose Settings

Go to Settings.

google sheets iterative calculation

Step 3: Go to the Calculation Tab

Click on the Calculation tab.

iterative calculation in google sheets

Step 4: Turn On Iterative Calculation

Open the dropdown menu under Iterative calculation and select "On".

set up iterative calculation google sheets

Step 5: Configure Your Iterations

You can set the Maximum number of iterations (how many times the formula should recalculate) and the Convergence criterion (the smallest change between iterations that is considered significant).

iterative calculation google sheets example

Example

Imagine you have a savings account with an initial balance of $10,000. You want to withdraw $500 every month for expenses, but the account also earns an interest of 1% per month on the remaining balance.

You want to calculate the balance of the account at the end of each month for a year.

Step 1: Set Up Your Spreadsheet

In cell A1, type Initial Balance.

In cell A2 through A14, type Month 1 through Month 12.

In cell B1, type $10,000 as the initial balance.

In cell C1, type Interest Rate.

In cell C2, type 1%.

In cell D1, type Monthly Withdrawal.

In cell D2, type $500.

how to do iterative calculation in google sheets

Step 2: Apply the Iterative Calculation

In cell B2, type the formula to calculate the balance for Month 1. This formula will subtract the monthly withdrawal, then add the monthly interest to the previous month's balance:

=B1 - $D$2 + (B1 - $D$2) * $C$2

how to enable iterative calculation google sheets

Drag this formula from cell B2 down to cell B14 to apply it to all months.

enable iterative calculation in google sheets

Step 3: Enable Iterative Calculation

If not already done, enable iterative calculation as described above (File > Settings > Calculation).

After setting the formulas, you should see the balance at the end of each month calculated based on the initial balance, the interest accrued, and the monthly withdrawal.

Google Sheets will process the circular reference in cell B2 (and subsequent cells) iteratively, using the balance from the previous month to calculate the next month's balance.

In this example, each month's balance is dependent on the previous month's balance. Google Sheets uses the value from the previous iteration to calculate the next one until it has processed all months. This is a common financial calculation and is a perfect use case for iterative calculation in spreadsheets.

We hope that this article has helped you and given you a better understanding of how iterative calculation works in Google Sheets. If you enjoyed this article, you might also like our articles on how to center text in Google Sheets and how to use ISNA in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->