Introduction
This page presents tools and guidance to help you understand the financial feasibility of your development. The first section below presents a back-of-the-envelope calculator you can use. The second section provides guidance for more detailed financial modeling. See Chapter 7: Financial Feasibility for more information about what financial feasibility means for a development and the metrics different types of funders will use to determine feasibility.
Back-of-the-Envelope Calculation
A back-of-the-envelope calculation is an informal or rough estimate based on easily obtained information and simple assumptions. This may be a helpful approach when you need a quick answer, and the accuracy or reliability of the estimate is not a concern. This is a common practice in the earliest phases of development when many details are not yet known.
Clicking the button below will download an excel-based back-of-the-envelope calculator created for this guide. It is intended to help you understand the basic feasibility of your development and think through sources of funding that may be needed beyond a loan. Note that because this tool does not contain an operating pro forma, it does not include estimates for returns on equity, which will be a key feasibility metric if your development will have outside equity sources.
Detailed Development Financial Model
A detailed development financial model can be an intimidating set of information to collect, input and interpret. There are many values. Terms are not always well explained. And it can be difficult to understand the significance of the model’s outputs for your development.
The Colorado Division of Housing (DOH) has provided a helpful starting point for detailed financial modeling with their Housing Development Analysis spreadsheet. This is also a useful template to become familiar with because it is a component of some DOH funding applications.
DOH has local Housing Development Specialists that can assist with completing this template and recommends contacting the one for your region early in the development process.
The information below can help you adapt your knowledge about your development into a detailed financial model such as DOH’s. Start by using the interactive orientation below to help you explore the primary components of the financial model and how they relate to one another.
When you are ready to begin your own financial model template, you will need to download a copy from DOH using the link above.
Supplementary descriptions and considerations are included in each tab below to help you as you complete it.
- Development Costs
- Operating Pro Forma
- Permanent Financing Sources
- Permanent Sources and Uses
- 15-Year Operating Pro Forma
The Development Costs worksheet (which may be referred to as a development budget in other financial model templates) captures all costs required to build your project and place it in service (or have it generally ready for occupancy). This includes costs associated with all predevelopment, design, construction, financing and lease-up activities. It does not include the costs associated with operating your property once it is occupied, which are captured in the Operating Pro Forma.
The sum of costs across each category represents your total development cost (TDC).
Not all costs will be relevant for every development. For example, if you already own the land you will be developing, acquisition costs may not be a relevant consideration.
Note that the guidance provided here corresponds to the information in DOH's template. If you are using a different template, there may be differences in specific line-item amounts since different financial model templates categorize and aggregate these items in different ways. For example, a model developed by another organization may combine architectural and engineering fees into a single line item or may label some soft costs differently.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
|
1
|
HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
|
|||||||||
|
2
|
Project Name: | |||||||||
|
3
|
Date: | PAGE #1 | ||||||||
|
4
|
Applicant: |
Development Costs
|
||||||||
|
5
|
Spreadsheet Version: |
APPLICANTS COMPLETE
|
||||||||
|
6
|
||||||||||
|
11
|
||||||||||
|
12
|
Development Costs by Line Item |
Total Cost
|
Cost/Unit | Cost/Sq Ft |
Total Square Feet in Units
|
0 | ||||
|
13
|
ACQUISITION COSTS |
Non Living Square Footage
|
0 | |||||||
|
14
|
The total cost to acquire the land. This should be available from the broker sheet or other property advertisement. If unknown and you are doing a back-of-the-envelope calculation, you can look at the property tax assessments for the site if this information is available. However, there are often disparities between assessed and market values, so use these with caution.
|
Land | 0 | #DIV/0! | #DIV/0! |
Total Project Square Feet
|
0 | |||
|
15
|
The total cost to acquire any existing structures on the site. This should be available from the broker sheet or other property advertisement. If unknown and you are doing a back-of-the-envelope calculation, you can look at the property tax assessments for the site if this information is available. However, there are often disparities between assessed and market values, so use these with caution. Note that many sources of information will not separate out the cost of land and structures and will just include a single acquisition cost.
|
Existing Structures * | 0 | #DIV/0! | #DIV/0! |
Number of Units
|
0 | |||
|
16
|
The total cost of acquiring all land and structures. This will typically comprise 10 to 20 percent of your total development costs
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
17
|
SITE IMPROVEMENTS | |||||||||
|
18
|
This category includes the costs of infrastructure that will be developed outside the site boundary such as improvements to roads and sidewalks or utility work to connect the site to other infrastructure. The costs of this will vary with the scope of the improvements needed.
|
Off Site Infrastructure * | 0 | #DIV/0! | #DIV/0! | #DIV/0! | ||||
|
19
|
On-site infrastructure includes site improvements beyond the building itself such as utility connections, walkways, roads, drains and other improvements. This will vary across sites and costs will scale with the scope of improvements needed.
|
On Site Infrastructure * | 0 | #DIV/0! | #DIV/0! | |||||
|
20
|
The cost of demolishing any existing structures on the site. Demolition costs will vary with the scale and type of structure that must be demolished and cleared from the site and will need to be estimated by a qualified contractor.
|
Demolition* | 0 | #DIV/0! | #DIV/0! | |||||
|
21
|
The total cost of improvements beyond the primary buildings
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
22
|
CONSTRUCTION | |||||||||
|
23
|
The fees associated with obtaining building permits from local jurisdictions. These costs may be listed on the City or County governments websites or be available upon request.
|
Building Permit Fees * | 0 | #DIV/0! | #DIV/0! | |||||
|
24
|
Tap fees are charged by local governments to connect a site to a municipal water source. The fee will typically vary based on the size of the piping and/or the type of development (e.g., residential or commercial). Impact fees are a more general fee charged by local governments to cover other types of broader infrastructure and resource impacts of development.
|
Tap Fees * / Impact Fees * | 0 | #DIV/0! | #DIV/0! | |||||
|
25
|
This is the total cost of construction or rehabilitation (excluding the other items listed in this Construction section such as contractor profit, and contingencies). Your general contractor can help you estimate these costs based on your scope of work.
|
Construction / Rehabilitation * | 0 | #DIV/0! | #DIV/0! | |||||
|
26
|
General requirements capture any construction-related costs that don't fall into the other categories listed, including inspections, project management costs, certification fees, etc. Your general contractor can help you estimate these costs.
|
General Requirements* | 0 | #DIV/0! | #DIV/0! | |||||
|
27
|
The portion of the construction costs that go directly to pay the contractor's operating costs and enable them to earn a profit for their work. These will usually be calculated as a fixed rate applied to the total construction costs. A common value to encounter is 10% for overhead and 10% for profit.
|
Contractor Overhead & Profit* | 0 | #DIV/0! | #DIV/0! | |||||
|
28
|
Cash set aside within the contractor's budget to cover unaccounted for costs within the construction scope of work, such as those that are unexpected or where there is variation between expected and actual construction costs. Your contractor will estimate this based on the nature of the scope of work and the uncertainty involved.
|
Contractor Construction Contingency* | 0 | #DIV/0! | #DIV/0! | #DIV/0! | % of construction | |||
|
29
|
Cash set aside within the developer's budget to pay for changes that must be made to the construction scope of work. Expect to reserve between 5 and 10 percent of the total construction budget for contingencies.
|
Owner Hard Cost Contingency* | 0 | #DIV/0! | #DIV/0! | #DIV/0! | % of construction | |||
|
30
|
The cost of furniture, fixtures and equipment. These are construction related expenses that will typically fall outside of your contractor's construction budget. Your architect or interior designer can help you estimate these costs.
|
FFE | 0 | #DIV/0! | #DIV/0! | |||||
|
31
|
Any other costs you anticipate incurring as part of construction.
|
Other (Devo specify) | 0 | |||||||
|
32
|
The total construction costs for your development. This will typically account for 50 to 70 percent of your total development budget.
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
33
|
PROFESSIONAL FEES | |||||||||
|
34
|
The total cost to acquire the land. This should be available from the broker sheet or other property advertisement. If unknown and you are doing a back-of-the-envelope calculation, you can look at the property tax assessments for the site if this information is available. However, there are often disparities between assessed and market values, so use these with caution.
|
Architect Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
35
|
The total cost to acquire any existing structures on the site. This should be available from the broker sheet or other property advertisement. If unknown and you are doing a back-of-the-envelope calculation, you can look at the property tax assessments for the site if this information is available. However, there are often disparities between assessed and market values, so use these with caution. Note that many sources of information will not separate out the cost of land and structures and will just include a single acquisition cost.
|
Engineering Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
36
|
The total cost of acquiring all land and structures. This will typically comprise 10 to 20 percent of your total development costs
|
Real Estate Attorney Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
37
|
The fees for surveys such as those seeking to establish the property's boundaries and easements. | Surveys | 0 | #DIV/0! | #DIV/0! | |||||
|
38
|
This category includes the costs of infrastructure that will be developed outside the site boundary such as improvements to roads and sidewalks or utility work to connect the site to other infrastructure. The costs of this will vary with the scope of the improvements needed.
|
Green Planning and Design Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
39
|
On-site infrastructure includes site improvements beyond the building itself such as utility connections, walkways, roads, drains and other improvements. This will vary across sites and costs will scale with the scope of improvements needed.
|
Other (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
40
|
The cost of demolishing any existing structures on the site. Demolition costs will vary with the scale and type of structure that must be demolished and cleared from the site and will need to be estimated by a qualified contractor.
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
41
|
The total cost of improvements beyond the primary buildings
|
CONSTRUCTION FINANCE | ||||||||
|
42
|
Also called Builder's Risk Insurance, this is a type of property insurance that protects you, your general contractor, lender and potentially other parties from risks associated with buildings under construction. An insurance agent or broker can give you a cost estimate for this expense. | Construction Insurance | 0 | #DIV/0! | #DIV/0! | |||||
|
43
|
The fees associated with obtaining building permits from local jurisdictions. These costs may be listed on the City or County governments websites or be available upon request.
|
Construction Performance & Payment Bonds | 0 | #DIV/0! | #DIV/0! | |||||
|
44
|
Tap fees are charged by local governments to connect a site to a municipal water source. The fee will typically vary based on the size of the piping and/or the type of development (e.g., residential or commercial). Impact fees are a more general fee charged by local governments to cover other types of broader infrastructure and resource impacts of development.
|
Construction Loan Orig. Fee | 0 | #DIV/0! | #DIV/0! | |||||
|
45
|
This is the total cost of construction or rehabilitation. Your general contractor can help you estimate these costs based on your scope of work.
|
Construction Interest | 0 | #DIV/0! | #DIV/0! | |||||
|
46
|
General requirements capture any construction-related costs that don't fall into the other categories listed, including inspections, project management costs, certification fees, etc. Your general contractor can help you estimate these costs.
|
Attorney Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
47
|
The portion of the construction costs that go directly to pay the contractor's operating costs and enable them to earn a profit for their work. These will usually be calculated as a fixed rate applied to the total construction costs. A common value to encounter is 10% for overhead and 10% for profit.
|
Title and Recording | 0 | #DIV/0! | #DIV/0! | |||||
|
48
|
Cash set aside within the contractor's budget to cover unaccounted for costs within the construction scope of work, such as those that are unexpected or where there is variation between expected and actual construction costs. Your contractor will estimate this based on the nature of the scope of work and the uncertainty involved.
|
Taxes During Construction | 0 | #DIV/0! | #DIV/0! | |||||
|
49
|
Cash set aside within the developer's budget to pay for changes that must be made to the construction scope of work. Expect to reserve between 5 and 10 percent of the total construction budget for contingencies.
|
Insp. Fees & Power/Telecom Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
50
|
The cost of furniture, fixtures and equipment. These are construction related expenses that will typically fall outside of your contractor's construction budget. Your architect or interior designer can help you estimate these costs.
|
Other (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
51
|
Any other costs you anticipate incurring as part of construction.
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
52
|
The total construction costs for your development. This will typically account for 50 to 70 percent of your total development budget.
|
PERMANENT FINANCE AND SYNDICATION | ||||||||
|
53
|
Fees and other expenses such as origination fees or other upfront costs associated with your permanent financing loan. Note that this should not include any interest unless you are paying some form of interest or points with the loan origination. This information will be available from your permanent financing lender.
|
Loan Fees & Expenses | 0 | #DIV/0! | #DIV/0! | |||||
|
54
|
The costs of any legal fees associated with the closing of your permanent financing.
|
Attorney Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
55
|
Costs associated with the title search, title insurance, transfer taxes and other expenses associated with closing your permanent financing. Your lender or title company can provide an estimate of these costs.
|
Title and Recording | 0 | #DIV/0! | #DIV/0! | |||||
|
56
|
Any other costs associated with your permanent financing.
|
Other (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
57
|
The total costs for your permanent financing.
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
58
|
SOFT COSTS | |||||||||
|
59
|
The cost of appraisal services and professional market study preparation. These costs should be provided by the companies you select for these services.
|
Appraisals & Market Study | 0 | #DIV/0! | #DIV/0! | |||||
|
60
|
The costs associated with your Phase I environmental assessment and any other environmental studies you are required to do.
|
Environmental Reports | 0 | #DIV/0! | #DIV/0! | |||||
|
61
|
The cost of tests associated with testing soil strength to support your development's foundation.
|
Geotechnical/Soils Tests | 0 | #DIV/0! | #DIV/0! | |||||
|
62
|
The cost of the assessment itself, which estimates the future costs of property maintenance so that you can create your operating budget or pro forma model.
|
Capital Needs Assessment | 0 | #DIV/0! | #DIV/0! | |||||
|
63
|
The cost associated with relocating any current tenants you will temporarily displace during construction. Costs will vary with the length of displacement.
|
Temporary Relocation (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
64
|
The cost associated with permanently relocating any current tenants who will not be able to return to the property post-construction.
|
Permanent Relocation (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
65
|
Fees associated with any tax credits your development will utilize.
|
Tax Credit Fees | 0 | #DIV/0! | #DIV/0! | |||||
|
66
|
The cost of marketing your development to prospective tenants.
|
Marketing | 0 | #DIV/0! | #DIV/0! | |||||
|
67
|
Cash that is set aside to cover unexpected soft costs associated with your project. For example, if your project ends up requiring more intensive environmental assessment than you expected. This is typically 3 percent of your total soft costs.
|
Soft Cost Contingency | 0 | #DIV/0! | #DIV/0! | |||||
|
68
|
The cost of an accountant to itemize and certify the actual costs of development for your own and your lenders records.
|
Cost Certification | 0 | #DIV/0! | #DIV/0! | |||||
|
69
|
Any other soft costs you incur during development.
|
Other (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
70
|
The total amount of all soft-costs.
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
71
|
DEVELOPER FEE / PROFIT | |||||||||
|
72
|
The fee that you will collect in the development to cover your own costs and profit.
|
Developer's Fee | 0 | #DIV/0! | #DIV/0! | |||||
|
73
|
The fees of any additional consultants that are part of your project team, such as a more experienced developer that you are working with to help you through the process or the cost of contractors that you bring on to fill in gaps in your staffing that will come out of your effective development fee.
|
Consultants | 0 | #DIV/0! | #DIV/0! | |||||
|
74
|
Any fee associated with project administration tasks or services the developer will provide.
|
Admininstration Fee | 0 | #DIV/0! | #DIV/0! | |||||
|
75
|
Your total effective developer fee (this is typically between 5 and 20 percent of the Total Development Cost)
|
SUBTOTAL (ie - maximum developer fee) | 0 | #DIV/0! | #DIV/0! | #DIV/0! | % of Total (less Dev. Fee, Reserves and | |||
|
76
|
RESERVES | Acquisition) | ||||||||
|
77
|
Cash that is set aside to cover the costs of operating your development for a specified period of time (typically 6 months) should your operating income be insufficient to cover costs for some reason (e.g. due to vacancies)
|
Operating Reserve | 0 | #DIV/0! | #DIV/0! | #DIV/0! | Months of expenses & debt | |||
|
78
|
Cash that is set aside to cover debt service for a specified period of time (typically 6 or 12 months) should your operating income be insufficient due to temporary vacancies or other reasons.
|
Debt Service Reserve | 0 | #DIV/0! | #DIV/0! | #DIV/0! | Months of debt | |||
|
79
|
Cash that is set aside to cover rents not collected during the lease-up period
|
Lease-up Reserve | 0 | #DIV/0! | #DIV/0! | |||||
|
80
|
Cash that is set aside up front to cover the replacement of major building systems should they fail.
|
Replacement Reserve | 0 | #DIV/0! | #DIV/0! | |||||
|
81
|
Any other reserve you want to account for
|
Other (Devo specify) | 0 | #DIV/0! | #DIV/0! | |||||
|
82
|
The total amount of all reserves
|
SUBTOTAL | 0 | #DIV/0! | #DIV/0! | |||||
|
83
|
The total estimated development cost
|
TOTAL DEVELOPMENT EXPENSES | $0 | #DIV/0! | #DIV/0! | |||||
|
84
|
||||||||||
|
85
|
Hard Cost Per Unit | #DIV/0! | #DIV/0! | |||||||
|
86
|
Land Cost Per Unit | #DIV/0! | #DIV/0! | |||||||
|
87
|
Soft Cost Per Unit | #DIV/0! | #DIV/0! | |||||||
|
88
|
Hard Cost Per Square Foot | #DIV/0! | ||||||||
|
89
|
Soft Cost Per Square Foot | #DIV/0! | ||||||||
|
91
|
Land Cost Per Square Foot | #DIV/0! | ||||||||
|
92
|
*costs included in hard cost evaluation. | |||||||||
The Operating Pro Forma worksheet details the costs and revenues that will accrue over your project’s lifetime once it is operating (occupied). This includes both information about the specific rent amounts for each unit (or type of unit), the levels of vacancy you expect, as well as the various administrative, maintenance and utility expenses you will need pay.
This information is also used in concert with information from the Financing tab to determine your development's debt service coverage ratio.
Note that the information in this tab is specific to rental developments. If your development includes for-sale units, will need to modify the information to account for this since you will not be collecting rents or realizing expenses over time in the same way. The Colorado Division of Housing provides guidance on modifying their template to reflect this.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
|
1
|
HOUSING DEVELOPMENT ANALYSIS SPREADSHEET | ||||||||
|
2
|
Project Name: | What | |||||||
|
3
|
Date: | 1/21/2022 | PAGE #2 | ||||||
|
4
|
Applicant: | Who | Operating Proforma | ||||||
|
5
|
Spreadsheet Version: | 1/29/2020 | APPLICANTS COMPLETE | ||||||
|
6
|
|||||||||
|
7
|
STABILIZED FIRST YEAR INCOME | EXPENSES | |||||||
|
8
|
Bd/Bath | % AMI | # of units | Sq. Ft. | Monthly Rent | Total Annual Rent | Administrative Expenses | ||
|
9
|
The total annual cost of your property manager
|
0 | 0% | 0 | 0 | 0 | Management Fee | 0 | |
|
10
|
The annual salaries and benefits associated with any administrative staff you maintain for the property (or a pro-rated share of these for staff split across multiple properties)
|
0 | 0% | 0 | 0 | 0 | Salaries and Benefits | 0 | |
|
11
|
Any legal and accounting fees you pay annually.
|
0 | 0% | 0 | 0 | 0 | Legal & Accounting | 0 | |
|
12
|
The annual cost of marketing your development to new tenants.
|
0 | 0% | 0 | 0 | 0 | Advertising | 0 | |
|
13
|
The annual cost of office supplies that you will require to operate your development
|
0 | 0% | 0 | 0 | 0 | Office Supplies | 0 | |
|
14
|
The annual cost of telephone and internet service for any administrative functions at your property.
|
0 | 0% | 0 | 0 | 0 | Telephone | 0 | |
|
15
|
The annual cost of audit services for this property.
|
0 | 0% | 0 | 0 | 0 | Audit | 0 | |
|
16
|
Any other administrative costs you anticipate as part of operating this property.
|
0 | 0% | 0 | 0 | 0 | Other (please specify) | 0 | |
|
17
|
The total cost of all administrative services for your development
|
0 | 0% | 0 | 0 | 0 | Total Administrative | 0 | |
|
18
|
0 | 0% | 0 | 0 | 0 | Operating Expenses | |||
|
19
|
The total annual cost of all utilities you will pay on behalf of tenants. For example, if your development has a single meter for water and all tenants are charged a share of this as part of their rent payment, this would be included here.
|
0 | 0% | 0 | 0 | 0 | Utilities | 0 | |
|
20
|
The annual cost of trash removal service for the development, if any.
|
0 | 0% | 0 | 0 | 0 | Trash Removal | 0 | |
|
21
|
Any other administrative costs you anticipate as part of operating this property (annually).
|
0 | 0% | 0 | 0 | 0 | Other (please specify) | 0 | |
|
22
|
The total operating cost of the property
|
0 | 0% | 0 | 0 | 0 | Total Operating | 0 | |
|
23
|
0 | 0% | 0 | 0 | 0 | Maintenance Expenses | |||
|
24
|
The total annual cost of materials required to maintain the units. For example, the cost of new plumbing fixtures, paint or windows.
|
0 | 0% | 0 | 0 | 0 | Maintenance Supplies | 0 | |
|
25
|
The total annual cost of maintenance staff you maintain or contract services for.
|
0 | 0% | 0 | 0 | 0 | Maint. Salaries, Repairs, and Contracts | 0 | |
|
26
|
The total average annual cost of any extermination services you use.
|
0 | 0% | 0 | 0 | 0 | Extermination | 0 | |
|
27
|
The total annual cost of any other landscaping, snow removal and other services you pay for to maintain the landscaping and grounds of the property.
|
0 | 0% | 0 | 0 | 0 | Grounds (inc. snow removal) | 0 | |
|
28
|
Any other annual maintenance expenses you incur in operating your property.
|
0 | 0% | 0 | 0 | 0 | Other (please specify) | 0 | |
|
29
|
Your total annual maintenance expenses for the property.
|
0 | 0% | 0 | 0 | 0 | Total Maintenance | 0 | |
|
30
|
0 | 0% | 0 | 0 | 0 | Other Expenses | |||
|
31
|
Total annual real estate taxes for the property.
|
0 | 0% | 0 | 0 | 0 | Real Estate Taxes | 0 | |
|
32
|
The total amount of property insurance for this property.
|
0 | 0% | 0 | 0 | 0 | Property Insurance | 0 | |
|
33
|
The annual contributions to your replacement reserve to cover the replacement of major building systems when needed. This is typically in the range of $250 to $300 per unit per year. | 0 | 0% | 0 | 0 | 0 | Replacement Reserve | 0 | |
|
34
|
Any other annual expenses you expect to pay annually on this property.
|
0 | 0% | 0 | 0 | 0 | Other (please specify) | 0 | |
|
35
|
Any other expenses you expect to pay for this property.
|
0 | 0% | 0 | 0 | 0 | Total Other | 0 | |
|
36
|
The total annual operating expenses for this property
|
0 | 0% | 0 | 0 | 0 | TOTAL ANNUAL EXPENSES | 0 | |
|
37
|
The total annual income for this property minus the total annual expenses for the property.
|
0 | 0% | 0 | 0 | 0 | NET OPERATING INCOME | 0 | |
|
38
|
The per unit per annum expenses - the total amount you pay in operating expenses each year divided by the number of units at the property.
|
0 | 0% | 0 | 0 | 0 | P.U.P.A. Expenses * | #DIV/0! | |
|
39
|
0 | 0% | 0 | 0 | 0 | * P.U.P.A = Per Unit Per Annum Expenses | |||
|
40
|
0 | 0% | 0 | 0 | 0 | Utilities included in rent? Y/N | Y | ||
|
41
|
0 | 0% | 0 | 0 | 0 | If yes, what utilities included in rent? | |||
|
42
|
0 | 0% | 0 | 0 | 0 | Gas Electricity Water Sewer Trash | |||
|
43
|
Total units | 0 | Total Rent Income | 0 | |||||
|
44
|
Total rental sq ft | 0 | |||||||
|
45
|
Parking Income | 0 | |||||||
|
46
|
Laundry Income | 0 | |||||||
|
47
|
Vending, Application, Late Fees | 0 | |||||||
|
48
|
No place in CHFA sheet for Vacancy rate entry | Total Income | 0 | ||||||
|
49
|
Vac. Rate | 7.00% | Less Vacancy | 0 | |||||
|
50
|
Effective Gross Income | 0 | |||||||
|
51
|
|||||||||
|
52
|
DEBT SERVICE | ||||||||
|
53
|
1st Mortgage | 0 | |||||||
|
54
|
2nd Mortgage | 0 | |||||||
|
55
|
3rd Mortgage | 0 | |||||||
|
56
|
TOTAL DEBT SERVICE | 0 | |||||||
|
57
|
BEP | #DIV/0! | Poss D/S @ 1.15 DCR | 0 | |||||
|
58
|
BEP = Break Even Point | Project Debt Coverage Ratio | #DIV/0! | ||||||
|
59
|
Poss D/S @ 1.15 DCR = Possible Debt Service at a 1.15 Debt Coverage Ratio | ||||||||
|
60
|
|||||||||
|
61
|
Free form entry ====>
|
||||||||
|
62
|
The number of bedrooms and bathrooms in a given floorplan
|
The percent of Area Median Income the units in this floorplan are affordable to. (if there are multiple, separate these out into different effective floorplans)
|
The number of units corresponding to this floorplan
|
The number of square feet per unit in this floor plan.
|
The monthly rent you will collect from this unit, inclusive of any unit or household-based vouchers you expect. If you pay for any utilities at the property level, include the amount you will collect for utilities from your tenants.
|
The total rent that will be collected annually for this floorplan (assuming no vacancy)
|
|||
The Permanent Financing Sources worksheet captures the sources of funding you will use to pay for your development as well as associated details such as the type and interest rate of a loan. This includes all debt, equity, grants and other sources of funding.
These sources are then compared to the total costs reflected in the Development Costs worksheet to determine whether there is a gap between the costs of the development and the funding available to pay for those costs.
Note that this does not include construction loans or other temporary funding sources you may employ for your development. These are instead accounted for in the Development Costs worksheet.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
1
|
HOUSING DEVELOPMENT ANALYSIS SPREADSHEET | |||||||||||
|
2
|
Project Name: | |||||||||||
|
3
|
Date: | Page #3 | ||||||||||
|
4
|
Applicant: | Permanent Financing Sources | ||||||||||
|
5
|
Spreadsheet Version: | APPLICANTS COMPLETE | ||||||||||
|
6
|
||||||||||||
|
7
|
||||||||||||
|
8
|
||||||||||||
|
9
|
TOTAL DEVELOPMENT COSTS:
|
$0 | ||||||||||
|
10
|
SOURCES OF FUNDS
|
Types of Loans: | C = Conventional | |||||||||
|
11
|
The permanent loans you will have for your development.
|
HARD DEBT
|
FIRST MORTGAGE | T = Tax-Exempt | ||||||||
|
12
|
The name of the lender who will originate your loan.
|
Lender | F = Federal Financing | |||||||||
|
13
|
Whether your loan is Conventional ("C"), Tax-Exempt ("T"), or Federal Financing ("F")
|
Type of Loan | 0 | c | ||||||||
|
14
|
The amount you are borrowing and agreeing to pay back (before interest is accounted for).
|
Principal | 0 | Financing Sources: | Total | % of Total | ||||||
|
15
|
The annual interest rate for the loan.
|
Interest Rate | 0.000% | Conventional | 0 | #DIV/0! | ||||||
|
16
|
The number of years over which your loan will be paid back, assuming no property sale or refinancing.
|
Term (years) | 0 | Tax Exempt | 0 | #DIV/0! | ||||||
|
17
|
The calculated annual payment based on the information above. Note that the formula used assumes an amortized loan. If one of your loans is an interest only loan, you will need to modify this formula.
|
Annual Payment | 0 | #DIV/0! | DCR | Federal Financing | 0 | #DIV/0! | ||||
|
18
|
Tax Credits | 0 | #DIV/0! | |||||||||
|
19
|
The permanent loans you will have for your development.
|
SECOND MORTGAGE | Government Grants | 0 | #DIV/0! | |||||||
|
20
|
The name of the lender who will originate your loan.
|
Lender | Other Grants | 0 | #DIV/0! | |||||||
|
21
|
Whether your loan is Conventional ("C"), Tax-Exempt ("T"), or Federal Financing ("F")
|
Type of Loan | c | Owner Equity | 0 | #DIV/0! | ||||||
|
22
|
The amount you are borrowing and agreeing to pay back (before interest is accounted for).
|
Principal | 0 | GAP | 0 | #DIV/0! | ||||||
|
23
|
The annual interest rate for the loan.
|
Interest Rate | 0.000% | TOTAL SOURCES | 0 | #DIV/0! | ||||||
|
24
|
The number of years over which your loan will be paid back, assuming no property sale or refinancing.
|
Term (years) | 0 | |||||||||
|
25
|
This section is used to help you determine the maximum loan that is possible for your property based on the maximum loan to value ratio a lender or loan product will allow. Two methods are provided. The first uses the net operating income and an assumed cap rate to estimate the resulting development's value. The second relies on an existing appraised value.
|
The calculated annual payment based on the information above. Note that the formula used assumes an amortized loan. If one of your loans is an interest only loan, you will need to modify this formula.
|
Annual Payment | 0 | #DIV/0! | DCR |
Quick Calculation of Mortgage Principal
|
|||||
|
26
|
The Net Operating Income of your development once it is stabilized - the property's total income less vacancy loss and operating expenses. This value is pulled from the Operating Pro Forma worksheet.
|
NOI | $0 | |||||||||
|
27
|
The capitalization rate or cap rate is the ratio of the property's net operating income to the total value of the property (e.g., the expected sale price if it were to be sold). It is expressed as a percentage. Different housing and real estate markets will have different cap rates. If you have completed your market study, it should include this information. If you haven't, you can look at commercial real estate listings in the area to see if cap rates are reported or to calculate it yourself by comparing the property's income to the list or recent sales price.
|
The permanent loans you will have for your development.
|
THIRD MORTGAGE | Cap Rate | ||||||||
|
28
|
Given the cap rate you supplied, this is the estimated value of your completed development. This is important for a lender to understand so that they can understand the risk associated with their loan.
|
The name of the lender who will originate your loan.
|
Lender | Value at Cap Rate | #DIV/0! | |||||||
|
29
|
The loan to value ratio. In this case this number represents the maximum LVR a lender or loan product will be willing to support.
|
Whether your loan is Conventional ("C"), Tax-Exempt ("T"), or Federal Financing ("F")
|
Type of Loan | LVR | ||||||||
|
30
|
The maximum loan amount possible using the NOI and cap rate valuation method
|
The amount you are borrowing and agreeing to pay back (before interest is accounted for).
|
Principal | 0 |
Maximum Loan Amount
|
#DIV/0! | ||||||
|
31
|
If you have an appraisal of the development that reflects the completed development, this is the best method to use.
|
The annual interest rate for the loan.
|
Interest Rate | 0.000% | Appraised Value | |||||||
|
32
|
The loan to value ratio. In this case this number represents the maximum LVR a lender or loan product will be willing to support.
|
The number of years over which your loan will be paid back, assuming no property sale or refinancing.
|
Term (years) | LVR | ||||||||
|
33
|
The maximum loan amount possible using an appraised value method
|
The calculated annual payment based on the information above. Note that the formula used assumes an amortized loan. If one of your loans is an interest only loan, you will need to modify this formula.
|
Annual Payment | 0 | #DIV/0! | DCR |
Maximum Loan Amount
|
$0 | ||||
|
34
|
||||||||||||
|
35
|
Funding that comes from Housing Tax Credit or Historic Tax Credit programs.
|
TC EQUITY
|
TAX CREDIT EQUITY | |||||||||
|
36
|
9% LIHTC Proceeds | $0 | $0.00 | |||||||||
|
37
|
4% LIHTC Proceeds | $0 | $0.00 | |||||||||
|
38
|
Federal Hist TC | $0 | $0.00 | |||||||||
|
39
|
||||||||||||
|
40
|
Government funding sources that you do not expect to have to pay back.
|
GRANTS & SOFT DEBT
|
GOVERNMENT GRANTS AND SOFT DEBT | |||||||||
|
41
|
Source | |||||||||||
|
42
|
DOH Loan | Amount | 0 | #DIV/0! |
per unit
|
|||||||
|
43
|
Amount | |||||||||||
|
44
|
Amount | |||||||||||
|
45
|
||||||||||||
|
46
|
All other funding sources that you do not expect to have to pay back.
|
OTHER GRANTS (NON-GOVERNMENTAL) | ||||||||||
|
47
|
Source | |||||||||||
|
48
|
Amount | |||||||||||
|
49
|
Amount | |||||||||||
|
50
|
Amount | |||||||||||
|
51
|
Amount | |||||||||||
|
52
|
||||||||||||
|
53
|
Amounts your organization or other organizations are investing in the property in the form of equity.
|
OWNER
|
OWNER EQUITY | |||||||||
|
54
|
Source | |||||||||||
|
55
|
If you are deferring your developer fee (i.e., collecting the fee from project cash flows over time instead of upfront), this will effectively count as a funding source. This is because your developer fee will still be reflected in the development costs worksheet as a cost.
|
Deferred Dev. Fee | Amount | 0 | #DIV/0! | 0 | Kept | 0 | ||||
|
56
|
Amount | |||||||||||
|
57
|
Amount | |||||||||||
|
58
|
Amount | |||||||||||
|
59
|
||||||||||||
|
60
|
The total amount of all funding sources listed.
|
TOTAL SOURCES | 0 | |||||||||
|
61
|
The difference (gap or surplus) between the total development costs and the total sources. If you have a gap, you will need to find additional funding to cover the gap.
|
GAP (SURPLUS) | 0 | |||||||||
|
62
|
||||||||||||
|
63
|
BEP | #DIV/0! |
Poss D/S @ 1.15 DCR
|
0 | ||||||||
|
64
|
DCR | #DIV/0! | Cap Rate | |||||||||
|
65
|
||||||||||||
|
66
|
BEP = Break Even Point | Poss D/S @ 1.15 DCR = Possible Debt Service at a 1.15 Debt Coverage Ratio | ||||||||||
|
67
|
DCR = Debt Coverage Ratio | Cap Rate = Capitalization Rate | ||||||||||
The Permanent Sources and Uses Budget worksheet is used to summarize your project’s financing and how that compares to your TDCs. In other words, do you have sufficient funding to cover the costs of building your project?
The information in this worksheet utilizes information you have entered in other worksheets but requires additional detail on the funding sources and the specific development costs they will be used to cover.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
|
1
|
HOUSING DEVELOPMENT ANALYSIS SPREADSHEET | ||||||
|
2
|
Project Name: | ||||||
|
3
|
Date: | Page #4 | |||||
|
4
|
Applicant: |
Permanent Sources & Uses Budget
|
|||||
|
5
|
Spreadsheet Version:
|
APPLICANTS COMPLETE
|
|||||
|
6
|
|||||||
|
7
|
|||||||
|
8
|
|||||||
|
9
|
Project Activities | Total Costs (from DevCosts Tab) | State Funds Requested | Other Funds | Source | Status (Pending or Committed) | Activity Subtotals |
|
10
|
Acquisition Costs | $0 | $0 | ||||
|
11
|
|||||||
|
12
|
|||||||
|
13
|
|||||||
|
14
|
|||||||
|
15
|
Site Improvements | $0 | $0 | ||||
|
16
|
|||||||
|
17
|
Construction | $0 | $0 | ||||
|
18
|
|||||||
|
19
|
|||||||
|
20
|
|||||||
|
21
|
|||||||
|
22
|
|||||||
|
23
|
Professional Fees | $0 | $0 | ||||
|
24
|
|||||||
|
25
|
Construction Finance | $0 | $0 | ||||
|
26
|
|||||||
|
27
|
Permanent Finance and Syndication |
$0 | $0 | ||||
|
28
|
|||||||
|
29
|
|||||||
|
30
|
Soft Costs | $0 | $0 | ||||
|
31
|
|||||||
|
32
|
|||||||
|
33
|
Developer Fee / Profit | $0 | $0 | ||||
|
34
|
|||||||
|
35
|
Reserves | $0 | $0 | ||||
|
36
|
|||||||
|
37
|
Total Project Costs | $0 | $0 | $0 | $0 | ||
|
38
|
$0 | ||||||
|
39
|
|||||||
|
40
|
Gap | $0 | |||||
|
41
|
|||||||
|
42
|
The primary cost categories (these match the categories used in the Development Costs Worksheet)
|
The total costs for the category based on the information you entered in the Development Costs worksheet.
|
The dollar amount of state funds you are requesting.
|
The amounts of all other funding sources you intend to use for this activity.
|
The name of the organization and/or program providing each funding amount.
|
Whether this funding has already been secured or this is pending approval.
|
The total sources associated with this activity
|
The 15-Year Operating Pro Forma worksheet uses information you already specified from the Operating Pro Forma and Permanent Financing Sources Worksheets to provide an estimate of what your development's income, expenses, debt service and cash flow will be over a 15-year period. This can also help you estimate returns for investors and understand the financial impacts over time of owning the development for your organization.
A key consideration in this worksheet is how you expect rents and expenses to change over time. For example, if you expect expenses to increase with inflation, but rents to stay fixed, what will that mean for your cash flow over time?
Note that this worksheet is focused on rental developments, so developments that include for-sale units should use the Loan Payoff Schedule - Homeownership worksheet instead to better understand how costs and income will play out over time.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
1
|
HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
|
|||||||||||||
|
2
|
Project Name: | |||||||||||||
|
3
|
Date: | Page #5 | ||||||||||||
|
4
|
Applicant: | 15-Year Operating Proforma | ||||||||||||
|
5
|
Spreadsheet Version:
|
APPLICANTS COMPLETE | ||||||||||||
|
6
|
(RENTAL PROJECTS ONLY) | |||||||||||||
|
7
|
||||||||||||||
|
8
|
YEAR 1 | YEAR 2 | YEAR 3 | YEAR 4 | YEAR 5 | YEAR 6 | YEAR 7 | YEAR 8 | YEAR 9 | YEAR 10 | ||||
|
9
|
||||||||||||||
|
10
|
The total income generated from collected rents in a given year assuming you have 100% occupancy. This is increased according to a specified escalation factor to reflect expected rent increases over time. |
Rent Income - increasing by
|
2.00% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
|
11
|
An assumed average vacancy rate that results in an estimated decrease in rent income each year due to rents not being collected on vacant units.
|
Less Vacancy | 7.0% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
|
12
|
The total of all other income for the property (for example laundry and parking income). This is increased according to a specified escalation factor to reflect expected increases in the fees associated with these services over time
|
Other Income - increasing by
|
2.00% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
|
13
|
The sum of rent income and other income minus the rent lost due to vacancy.
|
Eff. Gross Income | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
14
|
The total annual operating expenses for the property in a given year. This starts from the operating expenses in the operating budget worksheet and escalates them to reflect increases costs over time.
|
Total Annual Expenses - inc. by
|
3.00% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
|
15
|
The effective gross income minus the total annual expenses for the property in a given year.
|
NET OPERATING INCOME
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
16
|
The total debt service you pay on all permanent financing.
|
Total Debt Service | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
17
|
If you have a bridge loan, any debt service you pay on this loan. A bridge loan is a short-term loan that is used to finance a property until permanent financing sources can be employed.
|
Bridge Loan Debt Service
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
18
|
Your net operating income minus the debt service and bridge loan debt service.
|
Cash flow Available | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
19
|
Your net operating income as a fraction of your debt service. Lenders typically have a minimum threshold they will want to see.
|
Debt Coverage Ratio
|
#DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | ||
|
20
|
||||||||||||||
|
21
|
A list of payments you will need to make from your cash flow.
|
Projected Payments from Cashflow
|
||||||||||||
|
22
|
If your development involved a deferred developer fee, this will come out of your annual cash flow.
|
Deferred Developer Fees
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
23
|
Any asset management fees you must pay.
|
Asset Management Fees
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
24
|
If you have a loan that will need to be paid out of your cash flow, record it here. Cash flow loans are typically loan payments made to government subsidy loans (like DOH loans or City/County loans) and are also referred to as “soft debt.” | Cash Flow Loan #1 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
25
|
If you have a second loan that will need to be paid out of your cash flow, record it here. Cash flow loans are typically loan payments made to government subsidy loans (like DOH loans or City/County loans) and are also referred to as “soft debt.” | Cash Flow Loan #2 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
26
|
Any other obligations you have to use a portion of your cash flow.
|
Other | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
27
|
||||||||||||||
|
45
|
The total cash flow you have remaining after all obligations are met.
|
Available Cash after Payments
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
|
46
|
||||||||||||||
|
47
|
||||||||||||||
|
48
|
YEAR 11 | YEAR 12 | YEAR 13 | YEAR 14 | YEAR 15 | Deferred Developer Fee Totals | ||||||||
|
49
|
Yrs 1-10 | Yrs 11-15 | Total | |||||||||||
|
50
|
The total income generated from collected rents in a given year assuming you have 100% occupancy. This is increased according to a specified escalation factor to reflect expected rent increases over time.
|
Rent Income - increasing by
|
2.00% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |||
|
51
|
An assumed average vacancy rate that results in an estimated decrease in rent income each year due to rents not being collected on vacant units.
|
Less Vacancy | 7% | $0 | $0 | $0 | $0 | $0 | ||||||
|
52
|
The total of all other income for the property (for example laundry and parking income). This is increased according to a specified escalation factor to reflect expected increases in the fees associated with these services over time
|
Other Income - increasing by
|
2.00% | $0 | $0 | $0 | $0 | $0 | ||||||
|
53
|
The sum of rent income and other income minus the rent lost due to vacancy.
|
Eff. Gross Income | $0 | $0 | $0 | $0 | $0 | Cash Flow Loan #1 | ||||||
|
54
|
The total annual operating expenses for the property in a given year. This starts from the operating expenses in the operating budget worksheet and escalates them to reflect increases costs over time.
|
Total Annual Expenses - inc. by
|
3.00% | $0 | $0 | $0 | $0 | $0 | Yrs 1-10 | Yrs 11-15 | Total | #DIV/0! | ||
|
55
|
The effective gross income minus the total annual expenses for the property in a given year.
|
NET OPERATING INCOME
|
$0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | #DIV/0! | |||
|
56
|
The total debt service you pay on all permanent financing.
|
Total Debt Service | $0 | $0 | $0 | $0 | $0 | |||||||
|
57
|
If you have a bridge loan, any debt service you pay on this loan. A bridge loan is a short-term loan that is used to finance a property until permanent financing sources can be employed.
|
Bridge Loan Debt Service
|
$0 | $0 | $0 | $0 | $0 | |||||||
|
58
|
Your net operating income minus the debt service and bridge loan debt service.
|
Cash flow Available | $0 | $0 | $0 | $0 | $0 | Cash Flow Loan #2 | $0 | |||||
|
59
|
Your net operating income as a fraction of your debt service. Lenders typically have a minimum threshold they will want to see.
|
Debt Coverage Ratio
|
#DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | Yrs 1-10 | Yrs 11-15 | Total | #DIV/0! | |||
|
60
|
$0 | $0 | $0 | #DIV/0! | ||||||||||
|
61
|
A list of payments you will need to make from your cash flow.
|
Projected Payments from Cashflow
|
||||||||||||
|
62
|
If your development involved a deferred developer fee, this will come out of your annual cash flow.
|
Deferred Developer Fees
|
$0 | $0 | $0 | $0 | $0 | |||||||
|
63
|
Any asset management fees you must pay.
|
Asset Management Fees
|
$0 | $0 | $0 | $0 | $0 | |||||||
|
64
|
If you have a loan that will need to be paid out of your cash flow, record it here. Cash flow loans are typically loan payments made to government subsidy loans (like DOH loans or City/County loans) and are also referred to as “soft debt.” | Cash Flow Loan #1 | $0 | $0 | $0 | $0 | $0 | |||||||
|
65
|
If you have a second loan that will need to be paid out of your cash flow, record it here. Cash flow loans are typically loan payments made to government subsidy loans (like DOH loans or City/County loans) and are also referred to as “soft debt.” | Cash Flow Loan #2 | $0 | $0 | $0 | $0 | $0 | |||||||
|
66
|
Any other obligations you have to use a portion of your cash flow.
|
Other | $0 | $0 | $0 | $0 | $0 | |||||||
|
67
|
The total cash flow you have remaining after all obligations are met.
|
Available Cash after Payments
|
$0 | $0 | $0 | $0 | $0 | |||||||