### Replies (3)

### Please login or register to join the discussion.

In regard to the building development example, why not just use a simple algebraic solution rather than add risk to your spreadsheet by enabling circular references?

The formula in cell H2 (to calculate the developer's fee @ 3%) needs to be:

=$H$1/(1-$I$2)*$I$2 (Note that I've made the references absolute just so they can be easily distinguished as the column reference I looks a lot like a 1)

Probably because a lot of people don't think in algebraic terms, but your solution is elegant. For those of us that might want the steps involved to how Col D got it, do the following:

We know that Developer fee X + 100,000,000 = Y (total cost).

We know that the developer fee is 3% of Y

So we can rewrite equation in terms of Y

.03Y + 100,00,000 = Y

subtract .03y from both sides

100,000,000 = 0.97Y

Divide by .097

Y = 103,092,784

Times by .03 to get developer fee or subtract 100,000,000.

Developer fee = 3,092,784.

Once you do that and understand it, you can make the equation that Col describes easily.

As a long-time daily user of Excel, I strongly recommend against allowing circular references in your spreadsheets (and against enabling iterative calculations). As you note, it can lead to errors, which would be pretty embarrassing if you're analyzing a $100 million transaction. In the example you cited I agree with Col D that it is less risky to use algebra so that there is no circularity.

Plus since spreadsheets are commonly shared, you would have to make sure that everybody you sent it to knows how to enable the iterative calculation.

However, sometimes circular references are harder to avoid. Let's look at your building example. Suppose the lender charges a 1% loan fee and provides an 80% loan to cost. That part you can still handle with the algebra, but now add another wrinkle: because it's new construction, the lender will be funding the interest as well. Thus the interest itself is another part of the cost of the project, and so the loan fee is in part based on the interest, and at least some of the interest is based on the loan fee.

What I strongly recommend is essentially doing the iterative calculation yourself. Plug in a number for interest in calculating the total cost, then check that against the number the spreadsheet has calculated elsewhere. Not right? Try again. I'll usually put a checker in the next cell. If the two interest amounts are close enough it displays "Ok" and if not it says "Error".

It's annoying to have to do it, and it gets even worse when you add additional layers like mezzanine financing; sometimes you need to go back and forth between two plugged numbers several times before both are within the rounding tolerance. But it's better than having errors creeping in from circular references.