Modeling development transactions can be tricky if you want to avoid allowing circular references in your spreadsheets. Circular references can distort your calculations dangerously in that they mislead your expectations for your cash equity requirements.
The primary driver of development pro-forma circular references is the fact that construction loans (a Source of Funds) accrue interest, which itself is a Financing Cost (a Use of Funds). In other words, construction loans “fund their own interest” (i.e., they are “self-financing”) (See this post for more basics on construction loans).
Since Financing Costs are part of the Total Development Cost, and the Total Development Cost is funded by both Equity and Debt, one cannot help but refer to the Debt size (the dollar amount) in its own calculation. This causes the circular reference.
For example, if in our model we state that the Debt amount is to be 65.00% of Total Development Cost, then the calculation of the actual dollar amount of the loan rests on the inclusion of the loan’s interest cost as part of the Total Development Cost.
Matters are often compounded with secondary circular references that are created when soft cost items such as Insurance and Developer Fee (each of which are part of the Total Development Cost) are calculated as a % of Total Development Cost.
Here’s one way to avoid allowing circular references:
We know in retrospect that Total Sources of Funds and Uses of Funds are equal.
When forecasting, if we assume the Equity amount in whole dollars as opposed to as a % of Total Development Cost, then we can backsolve for the Total Debt dollar amount (which is composed of both Principal and Interest), because we know that the Total Uses of Funds and Sources of Funds are equal. Refer to the graphic below when reading the backsolving logic below the graphic.
The backsolving logic and algebra are as follows:
If: $30 + x + y = $90 + z = $100
And: x = z
Then: $30 + y = $90 = $100 – z
Therefore: y = $60
And consequently: x = z = $10, as driven by the interest cost associated with the loan draws as they are necessitated by the Uses of Funds in each month and accumulated over the entire loan period.
Every dollar of Interest generated on the construction loan (technically interest dollars are “Sources of Funds”) is carried in the Uses of Funds as a Financing Cost to match the Sources of Funds interest amount in that period.
All of REFM’s Excel Model Templates use this backsolving methodology to avoid distortion of line item values and resulting equity and debt requirements. We also build these dynamics into consulting client models upon request.