Financial Models

Background

According to Wikipedia, Financial Models are an abstract representation of a real world financial situation. It’s a mathematical simplification of the performance of a financial asset. In affordable housing we use Financial Models to ensure two fundamental things (a) will there be enough funding to develop the apartments and (b) will there be enough income to operate the apartments. This whitepaper and accompanying examples are intended to give you an overview of the purpose, use, and creation of financial models.

Proforma

While the term Financial Model is most accurate, it isn’t often used. You will normally hear models referred to as “the numbers” or “the proforma”. Proforma comes from a Latin phrase Pro Forma, which means, “for the sake of form.” The term is used in many fields but I believe it came to mean Financial Model in our industry because early models were developed by accountants who were practiced at building “Proforma Financial Statements,” forward looking financial statements using a “what if” scenario. In my opinion, the term proforma in our industry means, “a projection of the financial outcomes for an apartment development, given a set of assumptions.” Feel free to use proforma and Financial Model interchangeably.

Purpose

There are two primary purposes of a Financial Model because there are two distinct phases of the venture—developing the asset and operating the asset. If there will be insufficient funds for either, the venture will fail. Therefore developers, owners, investors, lenders, and other stakeholders look to the model to prove its financial feasibility. While feasibility testing is the primary purpose, models are also used for other things like measuring risk (underwriting), testing for compliance with regulations, calculating rates of return, and depicting the project’s capital structure.

Sources and Uses

To determine the sufficiency of development sources to cover development uses, you must list and quantify each source and use. You’re in good shape if the total sources is greater than the total uses. In concept this is simple enough but the hard work comes in calculating the sources (given assumptions) and estimating the uses.

Typical sources for affordable housing include: tax credit equity, conventional permanent loan, subordinate loans, and deferred developer fee. Typical uses include construction costs, architecture, permit fees, loan fees, interest, consultants, and developer fee.

Construction vs. Permanent

Just because you have enough sources (aka Permanent Sources) to cover your uses doesn’t mean you will be able to complete the project. It is important to know that you will receive the sources before (or at least concurrent with) the uses. Imagine being three months from completing construction and the general contractor stops working because you haven’t paid its last pay application because you’re waiting for equity to fund after construction is complete. Timing problems like this mean you need a bridge loan.

To identify such a problem you should prepare a month by month sources and uses schedule. To ballpark this check some underwriters request a list of “Construction Sources” (the sources you expect to receive during the construction period). This theoretical list of partial sources will include bridge loans and a concept “Cost Incurred After Completion”.

Warning: not everyone defines Construction Sources and the Construction Period the same. Prepare a month by month schedule and you will be able to address everyone’s timing concerns.

Operations

To verify the apartments will have sufficient funds to operate you must perform a cash flow analysis. Start by quantifying your income from rents, subsidies, laundry, etc. Next, reduce your income for vacancy to arrive at Adjusted Gross Income. Reduce this by the project’s operating expenses to arrive at Net Operating Income. Reduce this by the sum of all must pay debt service to arrive at Cash Flow. Assuming a 1.15 debt coverage ratio, your Cash Flow should be at least 15% of the sum of all must pay debt service. If not the project is infeasible as modeled.

Your cash flow analysis will need to be extrapolated to future years, incorporating inflation factors for income and expenses. This is done to ensure the apartments will continue to have the resources to pay its obligations until the end of the LIHTC compliance period and the maturity of the conventional permanent loan.

Components

The following is a list of core pages, tabs, and schedules included in a Financial Model and their basic usage.

  1. Development Budget – a detailed line item budget estimating each development cost
  2. Monthly Sources and Uses – a month by month schedule incorporating the timing of funding and expenditures to determine bridge loan needs and to calculate construction loan interest
  3. Subordinate Budgets – complementary detailed line item budgets used for estimating complex line items such as Development Impact Fees
  4. Permanent Sources – a list of sources of capital used to pay for development costs
  5. Permanent Loan – calculations sizing the conventional permanent loan
  6. Tax Credit Equity – calculations determining the proceeds generated from syndicating the low-income housing tax credits generated by the development
  7. Cash Flow Analysis – a detailed analysis modeling income, expenses, debt service, and the usage of resulting cash flow
  8. Operating Expense Budget – a detailed line item budget estimating the apartment’s operating expenses
  9. Rent Schedule – a schedule used to calculate rental income given certain quantities, bedroom sizes, income levels, utility allowances, and subsidy payments for certain units
  10. Inputs Tab – a tab that allows the analyst to input many assumptions in a single location
  11. Calculations Tab – a tab that allows the financial model author to build complex calculations without the pressure to make them concise or visually appealing

Coding: As a general rule financial model authors should separate calculations, inputs, and reports. This allows authors maximum freedom to calculate, modelersconvenience to input, and consumers concise reports to read.

However, authors should deviate from this rule(sparingly) when placing an input near a calculation or on a report page will help the modeler decide the input.

Circular References

As a Financial Model becomes more sophisticated, formulas become dependent upon each other and therefore reference themselves. In Excel this problem is called a Circular Reference. Let’s illustrate the problem using CTCAC fees. Since the CTCAC allocation fee is calculated using total eligible basis, it increases with eligible basis. Construction draws and therefore construction interest are calculated using costs like CTCAC fees and so interest cost goes up when CTCAC fees goes up when eligible basis goes up. Lastly, interest during construction generates eligible basis and now we have a circular reference. Eligible basis goes up then CTCAC fees goes up, then construction interest goes up, then eligible basis goes up then…it repeats the process in a circle forever. Below you will find a lists of strategies to deal with this problem ordered from least recommended to most.

  1. Avoidthem by not making the model sophisticated – this will require your modelers to perform many calculations by hand, decreasing accuracy and increasing effort.
  2. Allowcircular references to exist and enable iterative calculation – this built in feature of Excel will allow the model to calculate in a circle thousands of times until the change in such cells becomes minute. However, this causes Excel to crash or produce #REF errors that are timely to troubleshoot.
  3. Breakthe circle by only calculating a suggestion and require the modeler to type the suggestion into the input cell manually– this technically avoids the circular reference because a human is dictating if another calculation iteration is necessary. However, this adds significant effort for modelers as they will need to do this for 5-10 cells 2-3 times whenever they want to publish a model.
  4. Breakthe circle, provide suggestions, and build a macro to automatethe human typing the suggestions into the input cells 3-4 times – this solution enables the sophistication, avoids the circular reference, and avoids the time consuming human interaction. However, it means modelers need to “enable macros” when opening the file.

Error Handling

Anytime you build a tool in Excel you should consider teaching it to warn you of errors. This is commonly done using conditional formatting to grab the modeler’s attention when something is awry. Consider making a table with tests for many known problems such as actual DCR is below 1.15 or Deferred Developer Fee exceeds Developer Fee. When one of these tests triggers an error, provide a visual warning to the modeler and an explanation for what is wrong. The modeler can then fix the error (or override it) to finalize the model without the error message.

Coding: If you find yourself often overriding an error, consider revising the formula that tests for the error to only do so in relevant situations. Think twice about error sensitivity.

Updating Versions

Financial Models must be updated on a regular basis to remain effective. Every year rent limits change, government regulations change, as do cost structures and rates. Furthermore, you will want to add a feature to your model or revise a preset calculation that will necessitate you updating the model.

First and foremost, maintain a single Financial Modeltemplate that you make all updates to and you use to start all new projects with. Do not copy the model from a similar deal as that model is not likely the most up to date version.

Second, when you make updates change the version number for the model and keep a list of what projects are using what version numbers. This will help if you find a flaw in your model version 7.5; you can fix the master template but also fix the projects using that version.

Lastly, create warnings that alert the user to the fact that the model being used is more than 12 months old. At this point the modeler can either override the error or port the financial assumptions to the most recent version.

Deviations

As a general rule modelers (model users) should only edit input cells (cells with blue text) and not edit calculation cells (black text). However, every deal is different and underwriting techniques vary by capital provider. So don’t be surprised if you need to make a modification here and there.

Warning: when you must customize a calculation, be sure to test it thoroughly.

Conclusion

Financial Models (proformas) are necessary tools for developing affordable housing. If built properly and in the right hands such models can prove a deal’s financial feasibility or warn you to back away from a bad investment. If built poorly, not updated, containing errors, or in untrained hands, the Financial Model can misinform decisions, increase risk, cause noncompliance, and or falsely reject opportunities. Therefore, take your time to develop it, test it, update it, and train your organization to use it.

Practice

Review the accompanying model for a hypothetical new construction project and answer the following questions:

  1. What are total project costs and how much are they per unit?
  2. How many units are there and how many are restricted to 30% AMI?
  3. What is the TCAC 9% tiebreaker?
  4. What price does it assume the project will receive per tax credit?
  5. What is the sum of the construction loan draws and what month is it repaid?
  6. How much is deferred developer fee and how long until it’s repaid?

Review the accompanying model for a hypothetical acquisition rehab project and answer the following questions:

  1. What amount of Section 8 subsidy is anticipated in year 1?
  2. Why doesn’t this project get the 130% boost?
  3. Does the project exceed TCAC max DCR of 1.25 in the first three years?
  4. What are the total Direct Construction Cost per door?
  5. What is the interest rate on the Perm Bond and when does it fund?
  6. How much NOI is estimated in month 10 and is it used to pay development costs?

Questions?

If you have questions, call your helpful housing analysts at Kingdom Development, Inc., a California nonprofit public benefit corporation.

951.538.6244

714.357.1637