Manual vs Automatic Calculations Question

[EdwardSnowden]

Hi everyone. Attached you will find a very simple worksheet. Please make sure that formulas are set to be calculated manually. I have been working on a financial analysis for a company and noticed that one of the analysts uses example 1 for an interest calculation that requires calculations be set to manual. The other uses the simple math in example 2. What is happening in example 1 to get to the net total? I can't seem to deduce 51,020.41 by hand. Additionally, why would we ever use example 1 rather than example 2? Thanks so much!

[shg]

Column C is calculating 1.02 * C3, and column B is calculating B3 / (1 - 0.02) in a ridiculous way.

[EdwardSnowden]

shg,

I made a mistake, the formula in B4 should be =(B3+B5)/2*.02. Can you explain what is going on here with the formula being changed? The interest is calculating off of a sum, the sum incorporates the interest that hasn't been calculated yet, ... It's just very confusing to me for some reason. I appreciate your help

[MrShorty]

I assume you have iteration enabled for this? Otherwise you would be getting a circular reference error and nothing would calculate.

I'm also not sure why manual/automatic calculation seems crucial (unless the iterative process is slow due to other calculations in the real spreadsheet).

What you have here is an algorithm called "iteration", "simple iteration", or "successive approximations". Basically (warning -- math lesson coming):

1) Start with our basic equation B5=B3+0.02*(B3+B5)/2=50k+0.02*(50k+??)/2. Rather than do the simple algebra to solve this for B5, we decide to use the equation in this form to estimate B5 until we convince ourselves of the true value (or close enough) for B5.
2) As a first guess, let's guess that B5 is the same as B3 B5=50k+0.02*(50k+50k)/2=50k+1k=51k
3) Now, we have a new guess for B5, so let's put that back into the expression and get a new value for B5 B5=50k+0.02*(50k+51k)/2=51010
4) Take our new guess for B5, put it back in, and keep repeating that process until we convince ourselves that we know what B5 is.

In those cases where it works, it is a relatively straightforward way to solve a problem where it is impossible to solve the problem algebraically, or the algebra is more complex and we decide that this approach is easier, or we decide we are too lazy to bother with the algebra.

The process should work just fine whether in manual or automatic calculation mode. The only reason I see to be in manual calculation mode is if, because of other slow calculations in the sheet, the sheet is slow to calculate (iterative calculations like this can make a spreadsheet slow to non-responsive), but this is more about dealing with the slow spreadsheet rather than being necessary for the algorithm.

Edit to add: If you set your max iterations setting to 1, you can watch this process in Excel by repeatedly pressing F9.

[EdwardSnowden]

MrShorty:

Thanks!