Basic Debt Calculation



BackgroundAccording 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.ProformaWhile 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.PurposeThere 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 UsesTo 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. PermanentJust 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. OperationsTo 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 ponentsThe following is a list of core pages, tabs, and schedules included in a Financial Model and their basic usage.Development Budget – a detailed line item budget estimating each development costMonthly 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 interestSubordinate Budgets – complementary detailed line item budgets used for estimating complex line items such as Development Impact FeesPermanent Sources – a list of sources of capital used to pay for development costsPermanent Loan – calculations sizing the conventional permanent loanTax Credit Equity – calculations determining the proceeds generated from syndicating the low-income housing tax credits generated by the development Cash Flow Analysis – a detailed analysis modeling income, expenses, debt service, and the usage of resulting cash flowOperating Expense Budget – a detailed line item budget estimating the apartment’s operating expensesRent Schedule – a schedule used to calculate rental income given certain quantities, bedroom sizes, income levels, utility allowances, and subsidy payments for certain unitsInputs Tab – a tab that allows the analyst to input many assumptions in a single locationCalculations Tab – a tab that allows the financial model author to build complex calculations without the pressure to make them concise or visually appealingCoding: As a general rule financial model authors should separate calculations, inputs, and reports. This allows authors maximum freedom to calculate, modelers convenience 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 ReferencesAs 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.Avoid them by not making the model sophisticated – this will require your modelers to perform many calculations by hand, decreasing accuracy and increasing effort.Allow circular 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.Break the 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.Break the circle, provide suggestions, and build a macro to automate the 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 HandlingAnytime 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 VersionsFinancial 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 Model template 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.DeviationsAs 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. ConclusionFinancial 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:What are total project costs and how much are they per unit?How many units are there and how many are restricted to 30% AMI?What is the TCAC 9% tiebreaker?What price does it assume the project will receive per tax credit?What is the sum of the construction loan draws and what month is it repaid?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:What amount of Section 8 subsidy is anticipated in year 1?Why doesn’t this project get the 130% boost?Does the project exceed TCAC max DCR of 1.25 in the first three years?What are the total Direct Construction Cost per door?What is the interest rate on the Perm Bond and when does it fund?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.william@951.538.6244rusty@714.357.1637 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download