How does one enter equations into Excel to generate a series of iterations? One problem is that Excel won’t permit circular references in equations, but you MUST have a circular reference at some point - by the very nature of an interation!
Please help me, Mr. Excel! (And, how do I scramble an egg in its shell?)
Thanks to all who reply…
I have Excel 97 SR-1 and the Help thingy explains it pretty well. What precisely are you trying to do?
Tools, Options, Calculations gets you to enabling iterations (default is no) and you can set the number of calculations you want.
Of course, you could also use VBA to do a simple program. It might require some learning, but once you’re into it, the sky’s the limit.
A board about excel: http://www.mrexcel.com/board/index.php
Here’s a simple example of using iterations. I can get a mortgage loan with a certain interest and a certain number of points. I want to calculate what is the true interest rate I am paying taking the points into account. I set up a first column and calculate the monthly payment with the formula
=R[-6]C*R[-2]C/1200/(1-1/(1+R[-2]C/1200)^R[-1]C)
which yields 1045.64 in this case. That is the monthly payment.
Loan A Equiv. Loan
Loan $: 103092.78 100000.00
Points: 3 0
Net Loan: 100000.00 100000.00
Interest rate: 9.00 9.52
Months: 180 180
Payment: 1045.64 1045.64
Now in the second column I set up a loan with the same net principal and I want to determine the interest rate which will yield the same monthly payment. There is no straight formula to do this but an iteration will do it easily. I define the interest rate in the second column as
=RC+15*(R[2]C[-1]/R[2]C-1)
which is, obviously a self reference and therefore a circular reference. Then I go to Tools / Options / Calculation and enable iteration. I set the max iterations at 100 and the max change at 0.001. Try whatever numbers work for you in each specific case.
The sheet recalculates and in each iteration the difference between the target (known monthly payment in the first column) and the calculated payment in the second column is added to the interest rate until the two payments re the same.
I hope this helps
Hey, Sailor…buy me a drink? But seriously: Thanks for the example, but how do you avoid the circular reference error? How does Excel distinguish between a circular reference error and an iteration?
Should I first set Excel to manually calculate? And/or, if I set the iteration parameters (i.e.: number of iterations, etc.) does this trigger Excel to avoid the circular reference error?
Thanks again,