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.
By default, Google Sheets doesn’t allow circular references and will give you an error. To use them, you need to enable iterative calculations:
Click on File in the menu.
Go to Settings.
Click on the Calculation tab.
Open the dropdown menu under Iterative calculation and select "On".
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).
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.
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.
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
Drag this formula from cell B2 down to cell B14 to apply it to all months.
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.
