Circular References: Real Estate Development
When you underwrite the cash flows of new development, certain assumptions will require you to utilize a formula that references the cell you are solving for (directly or indirectly). Excel does not like this. In real estate development, the two usual suspects that cause circular references are:
Developer Fee as a percentage of the project cost
Capitalized Interest
Circular Reference Excel: Developer Fee
For example, if a developer charges a 3% development fee on a $30,000,000 project, you will quickly deduce that the developer intends to earn $900,000. However, in Excel's world, this logic is circular because the development fee depends on the total development cost, which depends on the developer fee. See how it circles back?
This circular logic is why when you open up an Excel spreadsheet with these types of calculations, you will get "circular reference er” and random arrows pointing in every direction.
Circular Reference Excel: Capitalized Interest
Capitalized interest is another item that has circular logic. Capitalized interest is the money borrowed to pay the loan interest expense while the property has no cash flow. It depends on how much the total project cost is, which depends on how much interest expense is borrowed. Again, it circles back.
Iterative Calculations Excel
Iterative calculations are just a fancy way to say " fast guessing." There is a method to alter Excel so that the circular references are not errors; rather, Excel will understand that the circular formulas are necessary and must find the correct answer by trial and error. To adjust this setting, go to:
File > Options > Formulas > Enable Iterative Calculations
Check the box.
By checking this, Excel will know to iterate through solutions until it finds the answer that makes the circular formula balance out. Stated differently, it will guess solutions until it balances the equation. I set the maximum iterations at 32,000 (which is the limit and means Excel will make up to 32,000 different guesses).
Caution with Iterative Calculations
There are dangers with iterative calculations:
They can slow the financial model down as Excel will always be calculating
Future circular reference errors that are unintended could go unnoticed
I was working on a formula that had an unintended circular reference. There was no solution, so Excel got stuck in a loop trying to solve the impossible, and my computer slowed down to a snail's pace. It was evident that something was off. Once I fixed the error, things returned to running smoothly.
Summarizing Excel’s Circular Reference
The circular reference in the Tactica Multifamily Development Model shouldn't be too labor-intensive. I have not noticed a slow-down from standard work procedures unless multiple other workbooks are open. In any development model for any asset class, you will likely need to turn on iterative calculations to avoid all the annoying and endless error messages.
