doh_proforma by dandanhuanghuang

VIEWS: 3 PAGES: 23

									ATTACHMENT E
ATTACHMENT E DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                                                                                                Spreadsheet directions are to the right --->
Date:                             21-Jan-12
Developer:                                                     PAGE #1
Spreadsheet Version:                                           Operating Proforma

STABILIZED FIRST YEAR INCOME                                                      EXPENSES
            Incomes #of units          Sq. Ft. Monthly Rent Total Annual Rent     Administrative Expenses
1Br/1Ba                                                                     0                    Management Fee                       #DIV/0!
1Br/1Ba                                                                     0           On-site Personnel Payroll
2Br/1Ba                                                                     0               Health Ins. & Benefits
2Br/1Ba                                                                     0                  Legal & Accounting
2Br/1Ba                                                                     0                             Advertising
2Br/1.5Ba                                                                   0                        Office Supplies
2Br/1.5Ba                                                                   0                             Telephone
2Br/1.5Ba                                                                   0                                  Audit
2Br/2Ba                                                                     0                                 Other
2Br/2Ba                                                                     0     Total Administrative Expenses                   0 #DIV/0!
3Br/2Ba                                                                     0     Operating Expenses
3Br/2Ba                                                                     0                 Utilities (owner paid)
3Br/2Ba                                                                     0                        Trash Removal
4Br/2Ba                                                                     0            Fire & Liability Insurance
4Br/2Ba                                                                     0                                 Other
4Br/2Ba                                                                     0     Total Operating Expenses                        0
          Total units  0                  Total Rent Income                 0     Maintenance
          Total sq ft  0                                                                               Maintenance
                                             Parking Income                  0                               Repairs
                                             Laundry Income                  0      Grounds (inc. snow removal)
                                               Other Income                  0                                Other
                                                Total Income                 0    Total Maintenance                               0
          Vac. Rate        0.05                Less Vacancy                  0    Real Estate Taxes                               0
                                    Effective Gross Income                   0    Operating Reserve                               0 unit avg.= ###
                                                                                  Replacement Reserve                               unit avg.= ###
                               DEBT SERVICE                                       TOTAL ANNUAL EXPENSES                           0
                                               1st Mortgage                  0    NET OPERATING INCOME                            0
                                              2nd Mortgage                   0    P.U.P.A. Expenses *                   #DIV/0!
                                              3rd Mortgage                   0
                               TOTAL DEBT SERVICE                            0
          BEP          #DIV/0!         Poss D/S @ 1.1 DCR                     0
                               Project Debt Coverage Ratio         #DIV/0!
BEP = Break Even Point
Poss D/S @ 1.1 DCR = Possible Debt Service at a 1.1 Debt Coverage Ratio
* P.U.P.A = Per Unit Per Annum Expenses
ATTACHMENT E
ATTACHMENT E
DIVISION OF HOUSING                                 <-- Spreadsheet is to the left
DEVELOPMENT ANALYSIS SPREADSHEET VERSION 1.1
GENERAL INSTRUCTIONS
This spreadsheet is designed to create a 10 year operating proforma for multi-unit rental housing. It can be
used to analyze the financing of a rental development project, and to determine the project's financial
feasibility.

The spreadsheet is divided up into five separate pages. Each page has linked information. The
information entered on pages 1 - 4 is read into page 5 where there is a 10 year proforma.

The five pages are as follows:
                  Page 1 - Operating Proforma (OpPro)
                  Page 2 - Development Budget (DevCosts)
                  Page 3 - Permanent Financing Sources (Financing)
                  Page 4 - Depreciation and Tax Credits (TaxCredits)
                  Page 5 - 10 Year Proforma (10YrProform)

On each page, the spreadsheet is to the left, instructions are to the right.

This spreadsheet was written in Excel 2000. Some of the instructions are directly
related to using this program. If you are using this spreadsheet in another program, some features like
shading, cell color and location of pages may be different.

To move around in each spreadsheet, use the scroll bars on the right of the screen. To move between
pages, click on the title bar at the bottom of the screen for the page you want to work on.

You are able to enter information into the cells which are shaded yellow. All other
cells are protected and cannot be changed.




We suggest that as soon as you enter the spreadsheet, you save it with a new name. This way, you will
always have a blank template to work with. To do this, go to FILE, SAVE AS, and name your file.
Remember, you should save your work periodically as you work through the spreadsheet. After naming
your file, you need only go to FILE, SAVE.


Remember, only enter data into the yellow cells. (If you are using another program, the instructions will
guide you through which cells you should be working with.)

DIVISION OF HOUSING *HOUSING DEVELOPMENT ANALYSIS SPREADSHEET VERSION 1.1
Operating Proforma - Page 1 - Instructions
The Operating Proforma page (page 1) is designed to show the project's operating income and expense
ATTACHMENT E
data.

On this page, you should first enter the Project Name in Cell C3, Developer Name in Cell C5, and your
version number (you may want to run a few different scenarios) in Cell C6. These entries will be shown at
 the top of each of the five pages.

Enter the project's number of units, square footage, and monthly rents in Cells B9 - D17.
Add any other income in Cells E20 - E22. The vacancy factor should be added in B24. This needs to be
expressed as a decimal. For example, 7% would be .07. Although they are not shaded, you can change the
bedroom size titles in Column A if you need to.

The mortgage amounts in Cells E28 through E31 are read into this schedule from the Financing page
(page 3). You do not need to enter them here.

The Break Even Point is automatically calculated once you enter all of the Operating Proforma information.

Next, enter your project's expense data in Cells G9 through G34. You need only enter data for the expenses
that pertain to your specific project. The program will automatically calculate the PUPA (Per Unit Per Annum
expenses) for you.

Cells H9 , I33 and I34 calculate the management fee as a percent of total income, average operating
reserve and average replacement reserve.

When you are ready to move to the Development Costs page, click on the button labeled DevCosts.



         45,610
DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                                 0                                        Spreadsheet directions are to the right --->
Date:                         21-Jan-12
Developer:                                    0                          PAGE #2
Spreadsheet Version:                          0                          Development Costs

Total Square Feet in Units                    0
Non Living Square Footage
Total Project Square Feet                     0
Number of Units                               0

                                      Total Cost             Cost/Unit        Cost/Sq Ft
ACQUISITION COSTS
Land *                                                  #DIV/0!              #DIV/0!
Existing Structures *                                   #DIV/0!              #DIV/0!
Appraisals & Market Study                               #DIV/0!              #DIV/0!
Soils Tests                                             #DIV/0!              #DIV/0!
Surveys                                                 #DIV/0!              #DIV/0!
Other -                                                 #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
CONSTRUCTION COSTS
Building Permit Fees *                                  #DIV/0!              #DIV/0!
Tap Fees *                                              #DIV/0!              #DIV/0!
Off Site Infrastructure *                               #DIV/0!              #DIV/0!
On Site Infrastructure *                                #DIV/0!              #DIV/0!
Construction *                                          #DIV/0!              #DIV/0!
Landscaping *                                           #DIV/0!              #DIV/0!
Contingency *                                           #DIV/0!              #DIV/0!
Other* -                                                #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
DESIGN FEES
Architect Fees                                          #DIV/0!              #DIV/0!
Engineering Fees                                        #DIV/0!              #DIV/0!
Other -                                                 #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
INTERIM COSTS
Construction Insurance                                  #DIV/0!              #DIV/0!
Construction Loan Orig. Fee                             #DIV/0!              #DIV/0!
Construction Interest                                   #DIV/0!              #DIV/0!
Consultants                                             #DIV/0!              #DIV/0!
Taxes During Construction                               #DIV/0!              #DIV/0!
Other -                                                 #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
PERMANENT FINANCING FEES/DEPOSITS
Loan Fees & Expenses                                    #DIV/0!              #DIV/0!
Attorney Fees                                           #DIV/0!              #DIV/0!
LIHTC Fees                                              #DIV/0!              #DIV/0!
Developer's Fee                                         #DIV/0!              #DIV/0!
Operating & Debt Service Reserve                        #DIV/0!              #DIV/0!
Other -                                                 #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
TENANT RELOCATION
Temporary Relocation                                    #DIV/0!              #DIV/0!
Permanent Relocation                                    #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!
PROJECT MANAGEMENT
Marketing                                               #DIV/0!              #DIV/0!
Project Management                                      #DIV/0!              #DIV/0!
Consultants                                             #DIV/0!              #DIV/0!
Other -                                                 #DIV/0!              #DIV/0!
                                              0         #DIV/0!              #DIV/0!

TOTAL DEVELOPMENT EXPENSES                    0         #DIV/0!              #DIV/0!

Hard Cost Per Unit                  #DIV/0!        #DIV/0!               * Hard Costs
Soft Cost Per Unit                  #DIV/0!        #DIV/0!
Hard Cost Per Square Foot           #DIV/0!
Soft Cost Per Square Foot           #DIV/0!
                      DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
e to the right --->   Development Costs - Page 2 - Instructions

                      The second page of the spreadsheet asks for the cost data for project development. This page will
                      calculate your total development budget, as well as cost per square foot and per unit. This includes all hard
                      and soft costs. For each subcategory of expense, there is an extra line to add additional cost categories.
                      To identify the line item added, you may type over the "other" label in these cells and add your own cost
                      category.

                      You will next see a schedule which shows Total Square Feet in Units and Number of Units. These values
                      will automatically be read into this page from the Operating Proforma page (page 1).
                      Add any non-residential square footage in Cell C9. The program will calculate the total square feet in the
                      project in Cell C10.

                      Enter the project development cost data in the yellow cells in column C. For each cost category grouping,
                      the program will calculate a subtotal of costs. The Total Develoment Expense is calculated in Cell C66.

                      The program will calculate the cost per unit and cost per square foot for each line item and for the
                      development as a whole in Columns D and E.

                      The last schedule on this page calculates the hard and soft cost per square foot and per unit.

                      When you are ready to move to the Financing page, click the button labeled Financing.
 elopment. This page will
 d per unit. This includes all hard
add additional cost categories.
e cells and add your own cost


Number of Units. These values

 ate the total square feet in the


or each cost category grouping,
nse is calculated in Cell C66.

ch line item and for the


e foot and per unit.
DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                                          0                                Spreadsheet directions are to the right ----->
Date:                                     21-Jan-12
Developer:                                             0          Page #3
Spreadsheet Version:                                   0          Permanent Financing Sources

TOTAL DEVELOPMENT COSTS:                              $0
SOURCES OF FUNDS                                                  Type of Loan     Types of Loans:        C = Conventional
FIRST MORTGAGE                                                                                            T = Tax-Exempt
             Lender                                                                                       F = Federal Financing
             Principal
             Interest Rate                                                         Financing Sources:             Total   % of Total
             Term                                           YRS                    Conventional                      0    #DIV/0!
             Annual Payment                             0                          Tax Exempt                        0    #DIV/0!
                                                                                   Federal Financing                 0    #DIV/0!
SECOND MORTGAGE                                                                    Tax Credits                       0    #DIV/0!
            Lender                                                                 Government Grants                 0    #DIV/0!
            Principal                                                              Other Grants                      0    #DIV/0!
            Interest Rate                                                          Owner Equity                      0    #DIV/0!
            Term                                            YRS                    GAP                               0    #DIV/0!
            Annual Payment                              0                          TOTAL SOURCES                     0    #DIV/0!

THIRD MORTGAGE                                                                     Quick Calculation of Mortgage Principle
                 Lender                                                            NOI                                        0
                 Principal                                                         Cap Rate                                0.00
                 Interest Rate                                                     Value at Cap Rate                  #DIV/0!
                 Term                                     YRS                      LVR
                 Annual Payment                         0 *                        Max Loan Amount                    #DIV/0!
TAX CREDITS
                 4% Low Income Proceeds                 0
                 9% Low Income Proceeds                 0
                 Historic Housing                       0
                 Historic Commercial                    0
GOVMT GRANTS
Source
CDOH             Amount
                 Amount
                 Amount
OTHER GRANTS
Source
                 Amount
                 Amount
OWNER EQUITY
Source
Deferred Dev fee Amount
                 Amount

TOTAL SOURCES                                           0
GAP (SURPLUS)                                           0

BEP                         #DIV/0!       Poss D/S @ 1.1 DCR                 0
DCR                         #DIV/0!       Cap Rate

BEP = Break Even Point                    Poss D/S @ 1.1 DCR = Possible Debt Service at a 1.1 Debt Coverage Ratio
DCR = Debt Coverage Ratio                 Cap Rate = Capitalization Rate
                     DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET VERSION 1.1
o the right ----->   Permanent Financing Sources - Page 3 - Instructions
                     The Permanent Financing Sources page (page 3) of the spreadsheet askes for all permanent sources of
                     funds for the development of this project. The financing sources on this page should add up to your total
                     development costs (shown in Cell C8).

                     On this page, you will enter all permanent financing, grant, tax credit and equity sources. You may also
                     analyze where your sources are coming from. For example, is this project mostly paid for through public
                     sources? Or is it primarily privately funded?

                     First, enter data on all permanent loans (mortgages)into the cells in column C.
                     As on the Operating Proforma page, you will need to express the interest rate as a decimal. (7% is
                     expressed as .07).

                     If you want to determine the amount of permanent financing that is federal, tax exempt, or conventional,
                      enter the code for each type of loan into Cells E12, E19, and E26. The codes are listed in Cells I7
                     through I9.

                     The "Quick Calculation of Mortage Principle" worksheet allows you to do a quick, basic estimate of the
                     project's permanent debt capacity. All values are read into this worksheet except the loan to value ratio
                     (LVR), which you must enter in Cell J32. The program then gives you an estimated maximum mortgage
                     amount, which can be used when entering permanent loan date.

                     The next item on Page 3 is tax credits. On the Tax Credit page (page 4) there are worksheets to calculate
                     the historic and low income tax credits that the project will generate, if this pertains to your project. Once you
                     have calculated these credits on Page 4, (directions for using these worksheets are on Page 4), the equity
                     generated will automatically be read into Financing page. You can go to the Tax Credit page now to
                     calculate these credits, or wait until you have all other financing sources entered.

                     Next, enter any government and other grants the project expects to use for development in cells C37
                     through C42. Donated land, materials, waived fees, etc. could be included as other grants
                     (source: donation or in-kind), or could be considered owner equity. Enter the source in Column A.

                     Owner equity is entered in cells C45 through C46. This allows for a cash-on-cash return on investment
                     calculation in the 10 year proforma.


                     Once you have entered all of your sources of funds, check the total row to make sure that your total sources
                     equals your total development costs. Any difference will be shown as a GAP in Cell C50.

                     The Break Even Point, Debt Coverage Ratio, Cap Rate, and Possible Debt Service at a 1.1 DCR for the
                     first mortgage are shown at the bottom of the page. As you play with the sources of funds, you can see how
                     they affect each of these items.

                     You must enter your local capitalization rate in Cell E54. This should be expressed as a decimal. You canb get your local
                     rate from a local lender. The capitalization rate is a commonly used tool to calculate the fair market value of
                     a project to the developer.

                     The permanent financing page also includes a "Total of Sources by Type" worksheet, which allows you to
                     analyze how much of your funding comes from each funding type. This worksheet is located in Cells G13
                     through J25. This worksheet is automatically calculated from your entries on this page.

                     When you are ready to move on to the Tax Credit and Depreciation page, click on the button labeled
                     TaxCredits.
u canb get your local
DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                            0                                                                                                  Spreadsheet directions are to the right ----->
Date:                           21-Jan-12
Developer:                               0    Page #4
Spreadsheet Version:                     0    Tax Credit Calculation

DEPRECIATION                    Residential   ESTIMATED LIHTC            9% CREDIT               Deferred Fee Phase In (LIHTC PROJECTS)
Bldg Acq Cost                                 Construction Costs                                                   Partnership
Design Fees                                   Interim Finance Cost                                      Developer Management Organization
Developer Fees                                Prop Tax (Const.)                                  Year   Fees       Fees          Costs
Construction Costs                            Arch, Eng Fees                                     1
Prop. Tax During Construction                 Dev Fees                                           2
Const Loan Finance Cost                       Appraisal Fees                                     3
Historic Tax Credits                     0    - Historic Tax Credit              0               4
DEP. BASIS                               0    TOTAL BASIS                        0               5
Annual Depreciation                      0    High Cost (enter .3)                               6
                                              Credit Multiplier (.xxx)                           7
DEPRECIATION                    Commercial    ESTIMATED CREDIT                   0               8
Bldg Acq Cost                                 Price Per $                                        9
Design Fees                                   EQUITY GENERATED                   0               10
Dev. Fees                                                                                        11
Const. Cost                                   ESTIMATED LIHTC            4% CREDIT               12
Prop. Tax During Construction                 Construction Costs                                 13
Const Loan Finance Cost                       Interim Finance Cost                               14
Historic Tax Credits                     0    Prop Tax (Const.)                                  15
DEP. BASIS                               0    Arch, Eng Fees                                     16
Annual Depreciation                      0    Dev Fees                                           17
                                              Appraisal Fees                                     18
HISTORIC TAX CREDITS            Residential   - Historic Tax Credit              0               19
Construction Costs                            TOTAL BASIS                        0               20
Interim Financing                             High Cost (enter .3)                               TOTAL           0             0        0
Prop Tax (Const.)                             Credit Multiplier (.xxx)
Arch, Eng Fees                                ESTIMATED CREDIT
Dev Fees                                      Price Per $
Appraisal Fees                                EQUITY GENERATED                   0
TOTAL BASIS                              0
20% Credit                               0    PRESENT VALUE of TAX CREDIT EQUITY
Price Per $                                   Discount Factor (%):
EQUITY GENERATED                         0    YEAR                 Present Value Future Value
                                              Preoccupancy                                   0
HISTORIC TAX CREDITS            Commercial    1                                              0
Construction Costs                            2                                              0
Interim Financing                             3                                              0
Prop Tax (Const.)                             4                                              0
Arch, Eng Fees                                5                                              0
Dev Fees                                      6                                              0
Appraisal Fees                                7                                              0
TOTAL BASIS                              0    8                                              0
20% Credit                               0    9                                              0
Price Per $                                   10                                             0
EQUITY GENERATED                         0    TOTAL                            0             0
::
DIVISION OF HOUSING * HOUSING DEVELOMENT ANALYSIS SPREADSHEET
Depreciation and Tax Credits - Page 4 - Instructions

Depreciation
The first two schedules on the Depreciation and Tax Credit page (page 4) calculate the depreciation of
both the residential and commercial components of a project. If the project owner will not be taxed, it is not
necessary to complete these schedules (ie. a housing authority or non-profit). To calculate the depeciation
(27.5 years for residential, 39 years for commercial), enter the appropriate amounts in Cells B7 through
B12 for residential, B19 through 24 for commercial. The spreadsheet will calculate the annual depreciation
and read it into the 10 year proforma on page 5.

Historic Tax Credits
The next two schedules on Page 4 calculate the amount of historic tax credits the project is eligible for. If
the project will receive Historic Tax Credits, use these schedules to calculate the amount of equity
these credits will produce for the project. There is one worksheet for residential construction costs
and one for commercial construction costs.

To calculate the amount of equity these credits will generate, enter the appropriate information in Cells C32
through C37 for housing, C44 through C49 for commercial. Enter the price per dollar you expect the credits
will be sold for in Cells C40 and C52. This amount should be expressed as a decimal. For example, 65
cents would be .65.

The schedule will calculate the amount of equity received from the sale of these credits, and automatically
enter it into the sources of funds found on the Financing page (page 3).

Low Income Housing Tax Credits (LIHTC)
In column E on Page 4, the next three schedules deal with low income tax credits. If the project will not be
receiving credits, skip these three schedules.

The first schedule calculates LIHTC at the 9% level, the second at the 4% level. The third calculates the
present value of tax credit equity paid in by investors over time. You should either use the 9% OR the 4%
worksheet, not both. You must know ahead of time which is appropriate for the project.

To use either the 9% or 4% worksheets, enter the appropriate project costs in Cells F7 through F12, or F22
through F27. The program will read in any historic Tax Credits (HTC) (from C39 and C51) and calculate the
total tax credit basis. Next, enter .3 into Cells F15 or F30 if the project is being developed in a high cost area.
If it is not, leave this cell blank. You can enter the most current credit multiplyer in Cells F16 or F31 if you
know them, or keep default .09 and .04 already in these cells. The program will then calculate the
estimated LIHTC that the project is eligible for. Enter an estimated price per credit in Cell F18 or Cell F33
(as a decimal), and the program will calculate the equity generated by these credits. This equity number is
read automatically into the sources schedule on Page 3.

*for a list of eligible improvement costs for LIHTC projects, click on Page 6. (To move through the tabs at the
bottom of the page, click the small arrows on the bottom left of the page)

DIVISION OF HOUSING * HOUSING DEVELOMENT ANALYSIS SPREADSHEET
Depreciation and Tax Credits - Page 4 - Instructions Cont.

Present Value of Tax Credit Equity

The Present Value of Tax Credit Equity schedule is meant to help determine the actual present day
value of the tax credit investor's equity payments. Some investors may pay less for credits but pay upfront,
while others may pay more but spread their payments over time. You can play with different scenarios in
this worksheet. To do this, enter a Discount Factor in Cell F38. You can get a current discount rate from
 your local lender. Next, enter the proposed pay in of credit over ten years in Cells F40 through F50. The
program will calculate the actual present value of this equity in Cell G51. Remember when you determine
the best pay in scenario for tax credit equity the project will have to pay bridge loan interest on any equity
not paid in at the time of lease up.

Deferred Fee Phase In for LIHTC Projects
If there are any developer fees, partnership management fees, or organizational costs paid out of cash
flow over time, you must hand enter these numbers into this worksheet. The program then reads these
numbers into the 10 year proforma on Page 5. It also uses the developer fees in the internal rate of return
calculations.
DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                               0                                                            Spreadsheet directions are to the right ----->
Date:                               21-Jan-12
Developer:                                  0              Page #5
Spreadsheet Version:                        0              Ten Year Operation Proforma

                                       YEAR 1    YEAR 2       YEAR 3    YEAR 4    YEAR 5    YEAR 6    YEAR 7      YEAR 8        YEAR 9       YEAR 10

Rent Income                                $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Less Vacancy                               $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Other Income                               $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Eff. Gross Income                          $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Total Annual Expenses                      $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
NET OPERATING INCOME                       $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Total Debt Service                         $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Bridge Loan Debt Service                   $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Cash flow Available                        $0        $0           $0        $0        $0        $0        $0          $0            $0             $0
Debt Coverage Ratio                    #DIV/0!   #DIV/0!      #DIV/0!   #DIV/0!   #DIV/0!   #DIV/0!   #DIV/0!     #DIV/0!       #DIV/0!        #DIV/0!
Cash-On-Cash ROI                           0%        0%           0%        0%        0%        0%        0%          0%            0%             0%

Developer Fees                            $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Partnership Management Fees               $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Tax Credit Org. Costs                     $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Cash Flow After Tx Cr Costs               $0        $0           $0        $0        $0        $0        $0            $0            $0            $0

TAX ANALYSIS
Taxable Cash Flow                         $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Depreciation Expense                      $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Principal Payments                        $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Unexpended Reserves                       $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
EARNINGS BEFORE TAX                       $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
Tax Rate                      0%          0%        0%           0%        0%        0%        0%        0%            0%            0%            0%
Tax Liability or (Operating Loss)         $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
CASH FLOW AFTER TAX                       $0        $0           $0        $0        $0        $0        $0            $0            $0            $0

TOTAL BENEFIT ANALYSIS - TAX CREDIT INVESTORS
Cash Flow After Tx    99%         $0        $0                   $0        $0        $0        $0        $0            $0            $0            $0
Historic Tax Credits              $0
LIHTC                             $0        $0                   $0        $0        $0        $0        $0            $0            $0            $0
Net Sales Proceeds                $0        $0                   $0        $0        $0        $0        $0            $0            $0            $0

TOTAL BENEFITS AFTER TAX                  $0        $0           $0        $0        $0        $0        $0            $0            $0            $0
                  DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
he right ----->

                  10 Year Operating Proforma - Page 5 - Instructions
                  Page five produces a 10 year operating proforma. Most information to produce this proforma is read in
                  from the previous four pages.

                  The first box of infomation projects all income and exprenses over the ten year period to produce a NET
                  OPERATING INCOME, and CASH ON CASH RETURN ON INVESTMENT. The only information which
                  must be entered in this box is any bridge loan debt service that the project will pay if tax credits will be paid
                  in over time. If the project will incur such costs, enter the annual debt service in Cells C17 through L17.

                  The program automatically uses an inflation factor of 4% for Rent Income, 4.25% for Total Annual Expenses.

                  The Cash on Cash Return calculated in Cells C20 through L20 is based upon the owner equity entered on
                  Page 3. If no owner equity is entered, the program bases the Cash on Cash Return on a default value of 1.

                  The second box on Page 5 deals with taxes and tax benefit. If this project is being developed by a private
                  entity, enter their tax rate in Cell B33. The program will then calculate the CASH FLOW AFTER TAX.

                  The third box on Page 5 calculates the total benefit to the tax credit investor. If this is not a tax credit project,
                  skip this section. If it is a tax credit project, enter the percent interest in the tax credit partnership the
                  investors will have in cell B38 if it is less than 99%. If they will own 99% of the partnership, the default is
                  already set and you do not need to enter anything. If it is expected that this project will be sold within the
                  next ten years, enter the projected net sales proceeds on line 41, in the year that the sale is anticipated. The
                  program will then calculate ten years of Total Benefits After Tax to the investors.
      IMPROVEMENT COSTS FOR LOW INCOME HOUSING TAX CREDIT PROJECTS

INCLUDE                                                 EXCLUDE

     * Construction Costs                           *   Permanent Financing
     * Permits and Fees                             *   Reserves
     * Construction financing expenses (interest,   *   Marketing
       fees, appraisal, inspections)                *   Tax Credit Application Fee
     * Property Taxes & Insurance                   *   Syndication Costs (legal, audit,
     * Architectural & Engineering                      consultant, etc.)
     * Performance Bond                             *   Acquisition
     * Furnishings                                  *   Off-site improvements
     * Environmental Assessment                         -costs for nonresidential
     * Developer Fee                                    -costs for market-rate residential
     * Contingency (if spent)                       *   Organizational expense
     * Development Consulting                       *   Any expense paid for with "bad" money
                                                        (bad money would be any federal HOME
                                                        dollars not loaned to the project)
 T PROJECTS




 cation Fee
sts (legal, audit,




 t-rate residential

 id for with "bad" money
uld be any federal HOME
ed to the project)
{SelectBlock OpPro:A1..I40}
{Print.Block "OpPro:A1..I40"}{Print.DoPrint}{SelectBlock DevCosts:A1..G72}{Print.Orientation Portrait}{Pr

{SelectBlock OpPro:A1..A1}
{HLine 7}                                    {SelectBlock OpPro:K1..K1}      {SelectBlock TaxCredits:M46
{SelectBlock OpPro:K1..S45}                  {HLine -7}                      {HLine -12}
{Print.Block "OpPro:K1..S45"}                {SelectBlock OpPro:A1..I40}     {SelectBlock TaxCredits:A1..
{Print.DoPrint}                              {Print.Block "OpPro:A1..I40"}   {Print.Block "TaxCredits:A1..
{SelectBlock OpPro:J1..J1}                   {Print.DoPrint}                 {Print.DoPrint}
{VLine 25}
{SelectBlock OpPro:K46..S72}
{Print.Block "OpPro:K46..S72"}
{Print.DoPrint}                                                             {
                                             {SelectBlock Financing:M1..M1}SelectBlock 10yrProform:O1
{SelectBlock DevCosts:A1..A1}                {HLine -11}                    {HLine -12}
{HLine 4}                                    {SelectBlock Financing:A1..K58}{SelectBlock 10yrProform:A1
{SelectBlock DevCosts:H1..N24}                                              {Print.Block "10yrProform:A1
                                             {Print.Block "Financing:A1..K58"}
{Print.Block "DevCosts:H1..N24"}             {Print.DoPrint}                {Print.DoPrint}
{Print.DoPrint}
{SelectBlock Financing:A1..A1}               {SelectBlock DevCosts:A1..G72}
{HLine 11}                                   {Print.Orientation Portrait}
{SelectBlock Financing:M1..Z55}              {Print.Block "DevCosts:A1..G72"}
{Print.Block "Financing:M1..Z55"}            {Print.DoPrint}
{Print.DoPrint}                              {Print.Orientation Landscape}
{SelectBlock TaxCredits:A1..A1}
{HLine 7}
{SelectBlock TaxCredits:M1..Z45}
{Print.Block "TaxCredits:M1..Z45"}
{Print.DoPrint}
{SelectBlock TaxCredits:M46..Z65}
{Print.Block "TaxCredits:M46..Z65"}
{Print.DoPrint}
{SelectBlock 6:A1..L21}
{Print.Block "6:A1..L21"}
{Print.DoPrint}
{SelectBlock 10yrProform:A1..A1}
{HLine 12}
{SelectBlock 10yrProform:O1..W26}
{Print.Block "10yrProform:O1..W26"}
{Print.DoPrint}
Orientation Portrait}{Print.Block "DevCosts:A1..G72"}{Print.DoPrint}{Print.Orientation Landscape}{SelectBlock Financ


 Block TaxCredits:M46..M46}           {SelectBlock OpPro:K1..K1}
                                      {SelectBlock DevCosts:A1..A1}
 Block TaxCredits:A1..L56}            {SelectBlock Financing:A1..A1}
Block "TaxCredits:A1..L56"}           {SelectBlock TaxCredits:A1..A1}
                                      {SelectBlock 10yrProform:A1..a1}
                                      {SelectBlock OpPro:K1..K1}


 Block 10yrProform:O1..O1}

 Block 10yrProform:A1..M45}
Block "10yrProform:A1..M45"}
cape}{SelectBlock Financing:A1..J58}{Print.Block "Financing:A1..J58"}{Print.DoPrin
INTERNAL RATE OF RETURN TO DEVELOPER
Temp Cash Equity                 $0
Months in Project                  0    0
               8                 $0
Perm Cash Equity                 $0
Developer Fee Up Front           $0
Developer Fee Year 1             $0
CFAT Year 1                      $0

TOTAL EQUITY                       $0
TOTAL CASH YEAR ONE                $0
IRR Year One                #N/A

10 YEAR INTERNAL RATE OF RETURN
Year          Cash Flow    PV
              Equity =             $0
1                       $0         $0
2                       $0         $0
3                       $0         $0
4                       $0         $0
5                       $0         $0
6                       $0         $0
7                       $0         $0
8                       $0         $0
9                       $0         $0
10                      $0         $0
TOTAL                   $0         $0
10 Year IRR =      #N/A
COLORADO DIVISION OF HOUSING * HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
Project Name:                                  0
Date:                                  21-Jan-12
Developer:                                     0                                  Page #9
Spreadsheet Version:                           0                                  Analysis of Sale Prices

                                                               Annual Incomes - Denver 2001
            # People:         1             1.5            2           2.5            3              3.5        4           4.5          5          6
                        (efficiency)      (1BR)                                     (2BR)                                 (3BR)                   (4BR)
              Median         $45,100        $48,300      $51,500      $54,750       $58,000      $61,200      $64,400     $67,000      $69,600     $74,700
                80%          $36,050        $38,640      $41,200      $43,800       $46,350      $48,960      $51,500     $53,600      $55,650     $59,750
                70%          $31,570        $33,810      $36,050      $38,325       $40,600      $42,840      $45,080     $46,900      $48,720     $52,290
                60%          $27,060        $28,980      $30,900      $32,850       $34,800      $36,720      $38,640     $40,200      $41,760     $44,820
                50%          $22,550        $24,150      $25,750      $27,375       $29,000      $30,600      $32,200     $33,500      $34,800     $37,350
                40%          $18,040        $19,320      $20,600      $21,900       $23,200      $24,480      $25,760     $26,800      $27,840     $29,880

                                                 Monthly Housing Costs (PITI=29% of gross income)
            # People:       1              1.5            2            2.5           3           3.5            4         4.5           5           6
              Median        $1,090          $1,167       $1,245        $1,323       $1,402       $1,479        $1,556     $1,619       $1,682       $1,805
                 80%          $871             $934        $996        $1,059       $1,120       $1,183        $1,245     $1,295       $1,345       $1,444
                 70%          $654             $700        $747          $794         $841         $887          $934       $972       $1,009       $1,083
                 60%          $545             $584        $622          $662         $701         $740          $778       $810         $841         $903
                 50%          $436             $467        $498          $529         $561         $592          $623       $648         $673         $722
                 40%      #REF!           #REF!         #REF!         #REF!        #REF!        #REF!         #REF!      #REF!        #REF!       #REF!
                 30%      #REF!           #REF!         #REF!         #REF!        #REF!        #REF!         #REF!      #REF!        #REF!       #REF!



       Assumptions:                           7.00% Interest                 30 Term (yrs)


AFFORDABLE SALE PRICES @ 80% AMI
            # People:        1             1.5             2           2.5            3           3.5           4          4.5           5          6
Gross Payment                   871             934           996        1,059         1,120        1,183        1,245       1,295        1,345       1,444
Taxes                           -48             -55           -56           -64          -72           -74         -78          -85         -91         -97
Insurance                       -48             -55           -56           -64          -72           -74         -78          -85         -91         -97
PMI                             -48             -55           -56           -64          -72           -74         -78          -85         -91         -97
Condo/HOA fee                  -100            -100          -100          -100         -100          -100        -100         -100        -100        -100
Net Payment (PITI)              627             669           728           767          804           861         911          940         972       1,053
Mortgage Amount           $106,949        $114,041      $124,078     $130,700      $137,116     $146,848     $155,268    $160,341     $165,719    $179,545
Dpmt (3%)                   $3,208          $3,421        $3,722       $3,921        $4,113       $4,405       $4,658      $4,810       $4,972      $5,386
Total Sale Price          $110,157        $117,462      $127,801     $134,621      $141,229     $151,253     $159,926    $165,151     $170,691    $184,932


AFFORDABLE SALE PRICES @ 60% AMI
            # People:        1             1.5             2           2.5            3              3.5        4          4.5           5          6
Gross Payment                    545             584          622          662           701          740          778         810          841         903
Taxes                            -35             -38          -41          -47           -53          -55          -58         -63          -67         -72
Insurance                        -35             -38          -41          -47           -53          -55          -58         -63          -67         -72
PMI                              -35             -38          -41          -47           -53          -55          -58         -63          -67         -72
Condo/HOA fee                   -100            -100         -100         -100          -100         -100         -100        -100         -100        -100
Net Payment (PITI)               340             370          399          421           442          475          504         521          540         587
Mortgage Amount             $57,968         $63,027      $68,085      $71,712       $75,339      $80,909      $85,968     $88,767      $92,078    $100,028
Dpmt (3%)                    $1,739          $1,891       $2,043       $2,151        $2,260       $2,427       $2,579      $2,663       $2,762      $3,001
Total Sale Price            $59,707         $64,917      $70,128      $73,864       $77,599      $83,337      $88,547     $91,430      $94,841    $103,029


Notes
1. This spreadsheet is for the Denver PMSA, as of 4/2001. Check AMI for your county.
2. For yearly updates, plug in 50% incomes for 1-4 people. Most numbers will adjust automatically.
2a. Then check your numbers for HUD adjustments. For Denver 2000, adjustments are bolded.
3. In general, updateable information is in yellow boxes.
4. Interest rates should be adjusted, as appropriate (cell C24).
5. Included taxes, insurance, PMI and HOA fees are estimates, and should be adjusted if more accurate info is avail.

								
To top