Circular Reference Calculation Error/Problems - Iterative Calculations Enabled

[lopo88]

I am having a problem with a real estate development spreadsheet in which I suspect circular reference to be the issue. The circular references exists because the monthly loan interest is calculated into the total cost of the development. Also, there are line item fees that are based on the total cost of the development yet are also included into the total cost.

The problem is that cells are not calculating properly such that my check-point section is detecting a few dollars difference. For example, the debt service (loan payment) is calculated and I then have a cell within the development budget that references this cell, yet the number returned is $1 off. This also occurs in the fee line item section. Literally one cell apart, setting to equal the cell, is off by a couple of dollars. Any idea why this might be happening? I have a helpful screenshot but this forum doesn't seem to allow that.

Any help would be greatly appreciated!

 

[dUBBINS]

Suspecting circular reference leads me to believe that Excel is not giving you such a warning; is this correct?

Are any of your formulas using ROUND()?

Can you post the formulas from one row? And any of importance? Or would that be tedious?

 

[lopo88]

There is some rounding within the workbook and within some of the line items that make up the total development costs, but none in those formulas which are having issues. I suppose that could still be an issue though...do you think?

No, I am not getting a warning.

=(Q86+Q91)/2*$I$89/12

^that is the formula to calculate the interest expense (average of the beginning and ending loan balance of that period multiplied by the monthly interest rate)

Then the other cell is told to equal that cell. I even tried avoiding the reference and input the same formula (=(Q86+Q91)/2*$I$89/12) and still come out different.

I'll try removing the round() formulas to see if that fixes the issue.

I appreciate your input.

 

[lopo88]

Eliminating rounding did not fix the issue. Any other ideas?

 

[lopo88]

bump

 

[dUBBINS]

Are you saying that using the same formula in two different cells is resulting in two different numbers? If so, I would suspect it is a formatting issue. Right click and see if one is formatted differently. And what happens if you simply reference the other cell? Still different?

If not...

I cannot get mine to error, but a thought. Try and put the second half in parenthesis. I cannot think of why the order of operations would affect this formula, but you are getting odd results, so try:

=(Q86+Q91)/2*($I$89/12)