Many finance problems inherently involve circular calculations. The most common of them is balancing the balance sheet. We all know that Assets should always equal Liabilities and Stockholder's Equity, don't we? But in order to prepare the balance sheet we need to prepare the income statement—since retained earnings should be carried to the balance sheet from the income statement. At the same time, without the balance sheet being ready, we can't figure out the interest expense associated with the amount of external financing—an item required to prepare the income statement. So how do we typically solve such kind of modeling problems?
There are four basic approaches to handle circular calculations in Excel:
Deliberate use of circular references
Goal seek/VBA macros
Algebraic (closed-form) solutions
Manual iterations (trial and error)
The first two techniques are somewhat risky and inefficient, the third is rarely practical in real world scenarios and the fourth is recommended.
DELIBERATE USE OF CIRCULAR REFERENCES
Circular references are usually considered by experts a bad thing to have in a spreadsheet model. The FAST standard—published by the FAST Standard Organization, a nonprofit promoting standardization in financial modeling—article 1.01-11 states clearly to “Never release a model with purposeful use of circularity”. The drawbacks of circular references are very well documented in Jonathan Swan's Practical Financial Modeling, A Guide to Current Practice—another authority on the use of spreadsheets as a financial analysis tool—and summarized here:
Inefficiency. They can be very memory intensive, as Excel will trigger recalculation of all the dependent worksheets—on each iteration—every time something changes in the model.
Risk. Once you enable iterations to allow Excel to calculate deliberate circular references, it becomes very difficult to distinguish between “mistakes” that have created inadvertent circular references and the intentional circular reference—since Excel won't warn you about the existence of such circular references.
GOAL SEEK / VBA MACROS
Goal seek and VBA macros are extremely powerful tools in Excel. However, they are considered static procedures—if the inputs change, the procedures have to be repeated. Therefore, their use to solve circularity problems will put the user at the mercy of his/her memory to remember when to execute the macro or initiate the goal seek procedure. How often have you found yourself tweaking the assumptions of a model to finally reach satisfactory results, but then to belatedly realize that you haven't pressed that macro button that will initiate the calculations to converge the results to the right solution—invalidating all the trials you were trying to do? But wait, it happens to the best of us.
ALGEBRAIC (CLOSED-FORM) SOLUTIONS
Closed form solutions are probably the most elegant of all four techniques, but they aren't possible in most practical cases—where finding a solution to an algebraic expression relating many variables over a large number of time periods is simply impossible without iterations.
MANUAL ITERATIONS (TRIAL AND ERROR)
The manual iterations technique entails solving the circularity problem by computing successive approximations to the solution starting from an initial guess. The Excel implementation of such iterative solutions involves building manually—hence the name of the technique—blocks of calculations that will ultimately converge to the solution within an acceptable margin of error. However, once implemented, the methodology becomes completely automatic and doesn't require the intervention of the user.
The beauty of the manual iterations technique is its similarity with Circular References of Excel—in the sense that it's iterative and automatic—but without the drawbacks of Circular References as explained above. Moreover, being automatic makes the technique superior to the Goal seek/Macro methodology—that requires manual intervention of the user with every change in the inputs.
Lets take an example to describe the methodology. Suppose a real estate developer is planning to build a condominium on a piece of land that was just bought for $1,000,000. On top of that, the developer budgeted a total of $2,800,000 to build all the site improvements including the building. In return, the developer expects to sell the apartments for a total net sale—after deducting all expenses—of $6,400,000. The total building and selling of the project is expected to take 12 months. The table below summarizes the projected monthly draws to pay the construction costs along with the net sales proceeds.