There are cases when a formula is dependent on the resultant cell of itself and we really need to keep circular reference. However the challenge is that using circular reference, excel converts the formula result to zero. Let's see how to resolve such a scenario.
To begin with. For let us understand the meaning of circular reference...
Circular Reference : When a formula refer back to its own cell either directly or indirectly, it is called as circular referencing.
Example : Let's take a simple example, a person invest an amount of 1 Lac in a Fixed Deposit for one year at an interest rate of 9%. So at the end of one year he gets an interest of Rs. 9000/-. This can be calculated in excel using IPMT function.
To begin with. For let us understand the meaning of circular reference...
Circular Reference : When a formula refer back to its own cell either directly or indirectly, it is called as circular referencing.
Example : Let's take a simple example, a person invest an amount of 1 Lac in a Fixed Deposit for one year at an interest rate of 9%. So at the end of one year he gets an interest of Rs. 9000/-. This can be calculated in excel using IPMT function.
|
FD
Plan
|
|
|
FD Amount
|
100000
|
|
Interest Rate
|
9%
|
|
Years
|
1
|
|
Interest Amount
|
₹
9,000.00
|
The formula used to calculate Interest Amount is = IPMT(Interest Rate,1,Years,FD Amount)
* Here 1 stand for the period for which interest is calculated
* FD Amount is taken as negative value
Let's say there is an auto-renewal process and we need to add the interest amount back into the principal for the calculation of next year. This Excel will not allow prompting it as a circular reference error.
Solution : To avoid such prompt message and to continue with the loop based calculations, we can enable the option of "Enable Iterative Calculations".
To enable this we need to follow the below mentioned path (Excel 2013)
File Tab --> Options --> Formulas --> Enable Iterative calculations
And we can now easily use circular referencing.
