Disclaimer: These spreadsheets are intended for educational use only.

Users should consult with professional

advisors and should not rely on the information contained herein for investment decisions. Any computations
should be independently verified.

List of Tables
figure 4-3a
Stage 1a: Rent & Sales Summary

figure 4-3b
Stage 1b: Pro Forma NOI

figure 4-3c
Stage 1c: Maximum Debt Calculation

figure 4-3d
Stage 1d: Development Costs

figure 4-3e
Stage 1e: Summary Analysis & Simple Ratios

figure 4-4a
Stage 2a: Analysis

figure 4-4b
Stage 2 Analysis--For-Sale Condominium Cash Flow

figure 4-5
Stage 3a--Analysis, Cash Flows During Development Period, Including Initial Lease-Up Activities

figure 4-6
Stage 3b: Development Cost Summary

figure 4-7
Stage 3c: Analysis--Combined Annual Before- and After-Tax Cash Flows during Development and Operating Period

figure 4-9
Stage 5 Analysis - Investor Return
 figure 4-3a
Stage 1a - Rental and Sales Revenue Summary

Area/Unit Rent/Month/ Total Annual

Apartment Unit Types No. of Units Rent/Ft2 (ft2) Total Ft2 Unit Rent

1 Bedroom, 1 Bath 10 $1.13 800 8,000 $900 $108,000

1 Bedroom, 1.5 Bath 27 $1.10 956 25,800 $1,053 $341,100
2 Bedroom, 1 Bath 8 $1.03 1,138 9,100 $1,169 $112,200
2 Bedroom, 2 Bath 6 $1.21 1,450 8,700 $1,750 $126,000
2 Bedroom, 2 Bath, Den 2 $1.02 2,450 4,900 $2,495 $59,880

Total Apartment Rental Revenue 53 $ 1.10 1,066 56,500 $1,175 $747,180

Total Retail Rental Revenue (see below) 4 $ 1.31 1,981 7,925 $2,592 $124,437
Other Rental Revenuea $18,300
Other Miscellaneous Revenueb $2,400
Total Rental Revenue 57 $ 1.15 1,130 64,425 $3,767 $892,317

Area/Unit Rent/Month/ Total Annual

Retail Tenants No. of Units Rent/Ft2 (ft2) Total Ft2 Unit Rent

Restaurant 3 $1.25 2,333 6,998 $2,916 $104,970

Dry Cleaners 1 $1.75 927 927 $1,622 $19,467

Total Rental Revenue 4 $ 1.31 1,981 7,925 $2,592 $124,437

Area/Unit Total Annual

Condominium Homes No. of Units Sales/Ft2 (ft2) Total Ft2 Sales/Unit Sales

Condominium Homes 3 $162.50 2,000 6,000 $325,000 $975,000

Total Sales Revenue 3 $ 162.50 2,000 6,000 $325,000 $975,000

Other Rental Revenue includes additional revenue derived from leasing space at the property. Examples of Other Rental
a

Revenue include leases for parking, rooftop telecommunication devices, storage space, and billboards.

b
Other Miscellaneous Revenue includes additional revenue as a result of conducting daily business activities. Examples of
Other Miscellaneous Revenue include late fees and penalties, forfeiture of deposits, and lost key fees. Specific to this case,
miscellaneous revenue from participation in the tax increment financing program accounts for the majority of the line item
amount.
 figure 4-3b
Stage 1b - Pro Forma NOI

Annual
Factor Revenue/Cost
Revenue
Gross Potential Revenuea $892,317
Less: Vacancy 5.00% ($44,616)
Less: Bad Debt 0.50% ($4,462)
Effective Gross Revenue $843,240

Expensesb
Property Management 3.00% of Effective Gross Revenue $25,297
Controllable Costsc $ 1,950 per unit $103,350
Real Estate Taxes 1.36% of estimated total project cost $135,000
Insurance $400 per unit $21,200
Utilities $500 per unit $26,500
Replacement Reserve $150 per unit $7,950
Total Expenses $319,297

Net Operating Income $523,942

= link from another sheet

a
Gross Potential Revenue is provided by the prior Rental and Sales Revenue Summary
exhibit. Vacancy and Bad Debt are customary charges against gross revenue.

bCustomary expense items have been shown. In the pro forma, per-unit expense items are
applied against 53 units. For expenses based on project cost, the total project cost used to
estimate expenses is $9,900,000, which accounts for the apartment and retail portions only
before application of any subsidies.

Controllable costs typically include salary, administrative, marketing, and maintenance expenses.
c
 figure 4-3c
Stage 1c - Maximum Debt Calculation
Pro Forma Net Operating Income (NOI) and Value
Pro Forma NOIa $523,942
Capitalization Rate 6.00%
Value of Income Property Only (NOI / Cap Rate) $8,732,373

Loan Terms
Interest Rateb 5.75%
Amortization (years) 30

Debt Based on Loan to Value (LTV)

Maximum LTV Percentageb 75.00%
Maximum Loan Based on LTV for Income Property $6,549,280

Using Debt Coverage Ratio (DCR)

Monthly NOI 43,662
Maximum DCR 1.20
Maximum Monthly Payment (NOI/DCR/12) $36,385
Maximum Loan Based on DCR for Income Property $6,234,849

Maximum Loan (Lesser of LTV or DCR Result)

Maximum Loan for Income Propertyc $6,234,849
Add: Loan for For-Sale Propertyd $682,500
Total Initial Project Debt $6,917,349

= link from another sheet

a
The pro forma NOI figure is provided by the prior eponymous exhibit and does not include any
revenues from the condominiums.

b
The assumed interest rate and loan-to-value reflect the availability of financing that was current
at the time of the project.

c
In the typical valuation of pure income properties, the maximum debt calculation ends with
selecting the lesser of two loan values, based on LTV or DCR, as is shown in line 23.

d
It is assumed that an additional loan in the amount equal to 70 percent of the condominium sales
revenue is provided by the construction loan lender.
 figure 4-3d
Stage 1d - Development Costs

Total Apartments For-Sale

Gross f Usable and Retail Condominiums
Number of Units 60 60 57 3
Square Footage (Input Gross) 75,696 70,425 64,425 6,000
Percentage of Total Square Footage 100% 91% 9%

Development Costsa
Land $16.53 per gross ft2 $ 1,251,500 $ 1,144,876 $ 106,624
Land Carryb 3.65% rate 12 months 27,418 25,082 2,336
Approval Fees $1.17 per gross ft2 88,800 81,235 7,565
Environmental Remediation $1.46 per gross ft2 110,574 101,153 9,421
Construction Hard Cost $98.77 per gross ft2 7,476,741 6,839,745 636,996
Soft Costs:
Architecture & Engineering 5.06% of hard cost 378,000 345,796 32,204
Legal & Other Fees $228,910 estimate 228,910 209,408 19,502
Appraisal & Title $10,883 estimate 10,883 9,956 927
Marketing $1,225 per total units 73,500 67,238 6,262
Taxes during Construction $50,000 estimate 50,000 45,740 4,260
Insurance during Construction $19,800 estimate 19,800 18,113 1,687
Total Soft Costs 761,093 696,250 64,843

Contingency 2.82% of hard cost 210,825 192,864 17,962

Total Development Cost before Interest and Operating Reserve $ 9,926,951 $ 9,081,205 $ 845,747

Estimate of Construction Interestc

Construction Loan $6,917,349
Construction Interest 12.00%
Construction Period (months) 12
Average Draw 65.00%
Estimated Construction Loan Interest 539,553 493,585 45,968
Total Project Cost before Operating Reserve $ 10,466,505 $ 9,574,790 $ 891,715

Estimate of Operating Reserved

Gross Potential Rent (monthly) $74,360
Lease-Up Period (months until stabilization) 6.0
Average Occupancy during Lease-Up 65%
Estimated Rent during Lease-Up 290,003
Estimated Op. Expenses during Lease-Up 159,649
NOI during Lease-Up 130,354
Construction Interest during Lease-Up 415,041
First-Year Operating Reserve Required 284,687 260,432 24,254
Total Project Costs $ 10,751,191 $ 9,835,222 $ 915,969

Less: Development Cost Subsidiese (2,476,984) (2,415,812) (61,171)

Total Project Costs after Subsidies $ 8,274,208 $ 7,419,410 $ 854,798

= link from another sheet

a
The following outline of development costs include customary expenses.

b
Land carry refers to interest paid to the land seller as part of the land purchase contract. The interest rate is applied to the negotiated purchase price less any upfront paid
amounts. Specific to this case, $500,000 was paid to the seller as part of the initial deposit.

c
This calculation is a preliminary estimate of interest during construction and reflects the availability of then-current market rate construction financing. A more accurate
estimate will be made as part of the Stage 3 analysis and an even more accurate estimate would be appropriate for a Stage 4 analysis (not shown).

d
Operating Reserve represents the amount that will be required to cover operating costs and debt service before the project reaches break-even occupancy. Customarily, the
Operating Reserve is based on the average occupancy during the term in which the property leases up to full occupancy. Specific to this case, 30 percent of the units were
preleased, hence the average occupancy is 65 percent ((30%+100%)/2). Additionally, based on the details of the case, the project was expected to be fully leased within six
months and in fact did achieve that goal.

e
Specific to this case, several local and federal subsidies were procured to finance the development of the project. Subsidies include the tax-increment financing (TIF)
subsidy, low-income housing tax credit (LIHTC) funds, a brownfield grant, a HUD Home grant, and a community development block grant (CDBG). The present value of
these subsidies have been deducted from the total project cost as a means to simplify the overall setup of the analysis since project financing is outside the scope of this
chapter. Also, due to the varying nature of the subsidies, the subsidies are prorationed based on the applicable portion of the project and not by square footage of the project
as previously done for other development costs.
 figure 4-3e
Stage 1e Summary Analysis & Simple Ratios

Net Operating Income (NOI) 523,942

Total Project Cost 10,751,191
Less: Development Subsidies (2,476,984)
Project Cost after Subsidies 8,274,208
Less: Development Cost of For-Sale Condominiums (854,798)
Total Adjusted Cost for Income Property Only 7,419,410

Overall Return, Overall Cap Rate (NOI/Total Adjusted Cost) 7.1%

Net Operating Income 523,942

Annual Debt Servicea 436,619
Cash Throw-Off (CTO or BTCF) 87,324

Total Adjusted Cost 7,419,410

Permanent Mortgageb 6,234,849
Equity 1,184,560

Cash-on-Cash Return (CTO/Equity) 7.4%

Development Profit for Apartments

Net Operating Income 523,942
Overall Cap Rate at Sale 6.0%
Capitalized Value (NOI/Cap Rate) 8,732,373
Less: Total Adjusted Cost (7,419,410)
Development Profit 1,312,963

= link from another sheet

a Annual Debt Service reflects the total mortgage principal amount of $6,234,849, which
excludes financing the For-Sale Condominiums.

b Mortgage excludes financing of the For-Sale Condominiums. Also, if applicable, the

Mortgage amount is capped so as to not exceed the Total Project Cost after Subsidies.
 figure 4-4a
Stage 2a Analysis
Project Costs Total Apartments Condominiums
Total Project Cost $10,751,191 9,835,222 915,969
Operating Reserves 284,687 260,432 24,254
Total Project Cost before Operating Reserve $10,466,505 9,574,790 891,715
Total Project Cost after Subsidies $8,274,208 7,419,410 854,798
Total Proj Cost after Subsidies before Op Reserve 7,989,521 7,158,978 830,544
Land Cost $1,251,500 1,144,876 106,624

Financing Assumptions
Equity $1,356,858 1,222,984 133,874
Mortgage Principala 6,917,349 6,234,849 682,500
Interest Rate 5.75% 12.00%
Amortization 30 N/A
Annual Debt Service $482,587 $436,619 $45,968

Depreciation Assumptions
Building Basisb $7,022,708 $6,274,534 $748,174
Life (in years) 27.5
Acceleration Factor 1.0
Straight Line (calculated) $255,371 $228,165 $27,206

Alternate Project Cost Assumptions for Vacancy Overridec

Total Proj Cost after Subsidies before Op Reserve 7,989,521 7,158,978 830,544
Equity 1,072,172
Building Basis 6,738,021 6,014,102 723,919
Straight Line $245,019 $218,695 $26,324

MORTGAGE CALCULATION FOR APARTMENTS & RETAIL Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8

Beginning Balance 6,234,849 6,154,642 6,069,700 5,979,743 5,884,475 5,783,582 5,676,733 5,563,575
Ending Balance 6,154,642 6,069,700 5,979,743 5,884,475 5,783,582 5,676,733 5,563,575 5,443,737
Amortization of Principal 80,207 84,942 89,957 95,268 100,893 106,849 113,158 119,839
Interest 356,412 351,677 346,662 341,351 335,726 329,769 323,461 316,780

DEPRECIATION CALCULATION FOR APARTMENTS & RETAIL Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8

Beginning Balance 6,274,534 6,046,369 5,818,204 5,590,039 5,361,874 5,133,709 4,905,545 4,677,380
Less: Annual Depreciation (228,165) (228,165) (228,165) (228,165) (228,165) (228,165) (228,165) (228,165)
Ending Balance 6,046,369 5,818,204 5,590,039 5,361,874 5,133,709 4,905,545 4,677,380 4,449,215
Cumulative Depreciation Taken 228,165 456,330 684,495 912,659 1,140,824 1,368,989 1,597,154 1,825,319
Cumulative Straight Line 228,165 456,330 684,495 912,659 1,140,824 1,368,989 1,597,154 1,825,319
Remaining Book Value 7,191,245 6,963,080 6,734,915 6,506,750 6,278,585 6,050,420 5,822,256 5,594,091

ANNUAL CASH FLOWS FOR APARTMENTS & RETAIL Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8

override vacancy rates c 0.0% 0.0%

Gross Potential Revenue 3.00% inflation 892,317 919,087 946,659 975,059 1,004,311 1,034,440 1,065,473 1,097,437
Less: Vacancy 5.00% vacancy (44,616) (45,954) (47,333) (48,753) (50,216) (51,722) (53,274) (54,872)
Less: Bad Debt 0.50% bad debt (4,462) (4,595) (4,733) (4,875) (5,022) (5,172) (5,327) (5,487)
Effective Gross Revenue 843,240 868,537 894,593 921,431 949,074 977,546 1,006,872 1,037,078
Total Operating Expenses 3.00% inflation 319,297 328,876 338,742 348,905 359,372 370,153 381,258 392,695

Net Operating Income 523,942 539,661 555,850 572,526 589,702 607,393 625,615 644,383

Annual Debt Service (436,619) (436,619) (436,619) (436,619) (436,619) (436,619) (436,619)

Before-Tax Operating Cash Flow 87,324 103,042 119,232 135,907 153,083 170,774 188,996

Taxes (see below) - - - - - (2,776) (23,375)

After-Tax Operating Cash Flow 87,324 103,042 119,232 135,907 153,083 167,999 165,621

INCOME TAX CALCUATION FOR APARTMENTS & RETAIL Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Net Operating Income 523,942 539,661 555,850 572,526 589,702 607,393 625,615
Add: Replacement/Capital Reserve 7,950 8,189 8,434 8,687 8,948 9,216 9,493
Deduct: Interest (356,412) (351,677) (346,662) (341,351) (335,726) (329,769) (323,461)
Deduct: Depreciation (228,165) (228,165) (228,165) (228,165) (228,165) (228,165) (228,165)
Taxable Income/(Loss) (52,684) (31,992) (10,542) 11,698 34,759 58,675 83,482
Passive Loss Offsetd - - - (11,698) (34,759) (48,762) -

Taxable Income - - - - - 9,913 83,482

Passive Loss Carryforwardd (52,684) (84,677) (95,218) (83,521) (48,762) - -

Annual Income Taxes 28.00% rate - - - - - 2,776 23,375

SALE CALCULATION OF APARTMENTS & RETAIL (Incl. Tax) Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Cash Flow from Sale

Sale Price (cap rate applied to following year NOI) 7.5% cap rate 8,591,774
Less: Commission 2.50% rate (214,794)
Adjusted Sales Price 8,376,980
Less: Remaining Mortgage Balance (5,563,575)
Before-Tax Cash Flow from Sale 2,813,404

Total Tax at Sale (recapture & capital gain, see below) (533,787)

After-Tax Cash Flow from Sale 2,279,618

Tax Calculation from Sale

Adjusted Sales Price 8,376,980
Remaining Book Value (5,822,256)
Total Taxable Gain 2,554,724
Passive Loss Carryforwardd -
Total Net Taxable Gain 2,554,724

Total Depreciation Taken 1,597,154

Recapture Tax @ 25% 25.00% rate 399,289

Capital Gain 957,570

Deduct: Capital Reservese (60,917)
Net Capital Gain 896,653
Tax on Capital Gain 15.00% rate 134,498
 Total Tax at Sale 533,787

RETURN MEASURES Investment Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Unleveraged IRR
Project Cost $ (8,274,208)
For-Sale Revenuesf 975,000
Net Operating Income 523,942 539,661 555,850 572,526 589,702 607,393 625,615
Adjusted Sales Price 8,376,980
Unleveraged Cash Flow $ (8,274,208) $ 1,498,942 $ 539,661 $ 555,850 $ 572,526 $ 589,702 $ 607,393 $ 9,002,594

Unleveraged IRR 9.15%

Net Present Value @ 8.0%g $475,479

Before-Tax IRR
Equity $ (1,356,858)
Before-Tax Cash Flow from Condominiumsf 246,532
Before-Tax Operating Cash Flow 87,324 103,042 119,232 135,907 153,083 170,774 188,996
Before-Tax Cash Flow from Sale 2,813,404
Total Before-Tax Cash Flow $ (1,356,858) $ 333,855 $ 103,042 $ 119,232 $ 135,907 $ 153,083 $ 170,774 $ 3,002,400

Before-Tax IRR 21.42%

Net Present Value @ 15.0% $446,121

After-Tax IRR
Equity $ (1,356,858)
After-Tax Cash Flow from Condominiumsf 204,461
After-Tax Operating Cash Flow 87,324 103,042 119,232 135,907 153,083 167,999 165,621
After-Tax Cash Flow from Sale 2,279,618
Total After-Tax Cash Flow $ (1,356,858) $ 291,785 $ 103,042 $ 119,232 $ 135,907 $ 153,083 $ 167,999 $ 2,445,239

After-Tax IRR 18.07%

Net Present Value @ 15.0% $198,880

Simple Return Measures Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7
NOI/Adjusted Project Cost Excluding For-Sale Condos 7.1% 7.3% 7.5% 7.7% 7.9% 8.2% 8.4%
Before-Tax Cash Flow / Equity 6.4% 7.6% 8.8% 10.0% 11.3% 12.6% 13.9%
Tax Shelter/Equity 0.0% 0.0% 0.0% 0.9% 2.6% 3.6% 0.0%

= link from another sheet

a
The Mortgage Principal is determined based on value and cash flow as shown in Figure 4-3c, but is capped so as to not exceed development costs. Note that the annual debt service for the For-Sale Condominiums reflects the
interest-only construction loan shown on Figure 4-3d, which is based on interest payments during the construction period only.

b
Customarily, the building basis is the difference between the total project cost and the land value. For this analysis, however, the building basis has also been adjusted to subtract the cost of the condominiums and the development
cost subsidies based on the allocated development cost from Figure 4-3d.

If the user wants to incorporate vacancy rates during lease-up directly into this spreadsheet, the user may do so by entering the vacancy rates into cells E51 and F51. If these cells are left as zero, then the Total Project Costs and
c

Equity are taken from lines 3-9. If they are not zero, then Total Project Cost, Building Basis, and Equity are taken from lines 24-28.
d
Current tax regulations treat real estate investments as a passive activity for non-real estate investors. As a result, tax losses in real estate are considered passive income losses and can only be taken against other passive
income (with minor adjustments for small investors). In the event that an investor does not have any passive income, the passive losses are carried forward until they can be used against future passive income. See William B.
Brueggeman and Jeffrey Fisher, Real Estate Finance and Investments, 13th ed. (New York: McGraw-Hill, 2010) for more information.

e
Specific to this model, a simplifying assumption has been made that the yearly replacement reserves (shown as being included in the net operating income in Figure 4-3b) are accumulated during the hold period and spent on
capital needs immediately before the sale of the Property. Consequently, the replacement reserve amount spent on capital needs is not depreciated. As a result, the amount is deducted from the estimated capital gains from sale of
the Property.

f
Cash flows related to the sales and profit of the For-Sale Condominiums are calculated separately and shown in Figure 4-4b.

g
Net Present Value equals the present value of future cash flows, less the initial investment. Note that Stage 2 analysis assumes all equity is invested at the beginning of the project. Also note that the unleveraged NPV represents
the development profit.
 figure 4-4b
Stage 2b Analysis
For-Sale Condominium Cash Flow

Revenue and Expense for Condominiums

Sales Revenue 975,000
Development Costs & Expenses a
(854,798)
Profit 120,202

Tax Determination for Condominiums

Profit 120,202
Tax Rate 35.0%
Tax Liability 42,071

Mortgage Calculation for Condominiumsb

Beginning Balance 0
Borrowings 682,500
Releases (682,500)
Trial Ending Balance 0
Average Balance 341,250
Interest Owed 45,968
Interest Paid (45,968)
Ending Balance 0

Cash Flow Determination for Condominiums

Revenues 975,000
Less: Condominium Releases to Lender (682,500)
Less: Interest Paidb (45,968)
Before-Tax Cash Flow 246,532
Less: Taxes (42,071)
After-Tax Cash Flow 204,461

a
Specific to this case and Stage 2 analysis, operational expenses pertaining to the marketing and sales of
the condominiums have been accounted for as a development cost.

b
Specific to this case and Stage 2 analysis, it is assumed that the condominiums are effectively presold
such that the construction loan is immediately repaid at the completion of the construction. The interest
owed is the amount of interest that accrued while the project was being constructed. Note that this
amount had previously been accounted for in determining the total development costs and initial equity
required. As a result, the interest owed amount is not shown to affect the ultimate cash flows for the
condominiums.
 figure 4-5
Stage 3a Analysis
Cash Flows during Development Period, Including Initial Lease-Up Activities
– Development – – Lease-Up – – First Stabilized Year –
Data Total Time Zero Year 1 Total Year 2 Total Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarter 5 Quarter 6 Quarter 7 Quarter 8 Quarter 9 Quarter 10 Quarter 11 Quarter 12 Year 3 Total
Development Costs
Land $1,251,500 1,251,500 1,251,500 0 0 0
Land Carry $27,418 27,418 $27,418 0 0 0
Approval Fees $88,800 88,800 $88,800 0 0 0
Environmental Remediation $110,574 110,574 $110,574 0 0 0
Construction Hard Cost $7,476,741 7,476,741 7,476,741 0 $1,869,185 $1,869,185 $1,869,185 $1,869,185 0
Soft Costs: 0

Architecture & Engineering 378,000 378,000 378,000 0 378,000 0

Legal 228,910 228,910 228,910 0 0 0
Appraisal & Title 10,883 10,883 10,883 0 0 0
Marketing 73,500 73,500 36,750 36,750 9,188 9,188 9,188 9,188 9,188 9,188 9,188 9,188 0
Taxes during Construction 50,000 50,000 25,000 25,000 6,250 6,250 6,250 6,250 6,250 6,250 6,250 6,250 0
Insurance during Construction 19,800 19,800 9,900 9,900 2,475 2,475 2,475 2,475 2,475 2,475 2,475 2,475 0
Contingency 210,825 210,825 105,413 105,413 26,353 26,353 26,353 26,353 26,353 26,353 26,353 26,353 0
Development Subsidies (2,476,984) (2,476,984) (2,476,984) - (619,246) (619,246) (619,246) (619,246)

Total Development Cost, Excluding Construction Loan

Interest & Operating Reserves $7,449,968 7,449,968 $1,718,085 $5,554,820 $177,063 $1,672,205 $1,294,205 $1,294,205 $1,294,205 $44,266 $44,266 $44,266 $44,266 $0 $0 $0 $0 $0

Condominium Sales Schedule

Units Sold 3 3 0 3
Cumulative Units Sold 3 3 3 3 3 3 3 3 3 3 3 3
Revenue per Unit 650,000 325,000 325,000 $325,000 $325,000 $325,000 $325,000 $325,000 $325,000 $325,000 $325,000 $325,000 $0
Condominium Sales Revenue 975,000 975,000 0 975,000 0 0 0 0 0 0 0 0 0
Expenses a
0 0 0 0 0 0 0 0 0 0 0 0 0
Condominium Net Revenues 975,000 975,000 0 975,000 0 0 0 0 0 0 0 0 0

Operating Income/ (Loss) during Lease-Up

Initial Occupancy upon Opening 30%
Months to Reach Stabilized Occupancy 6.00
Apartments Leased per Quarter 16 37 16 19 19 0 0 0 0 0 0 0
Cumulative Number of Apartments Leased 16 53 16 34 53 53 53 53 53 53 53 53
Vacancy Due to Lease-Up (% of Gross Potential)b 85% 18% 85.00% 52.50% 17.50% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0%
Stabilized Vacancy (% of Gross Potential) 5% 0% 5% 0.00% 0.00% 0.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5%
Overall Vacancy Rate 85% 20% 85.00% 52.50% 17.50% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5%
Gross Potential Revenuec 3% 223,079 892,317 892,317 223,079 223,079 223,079 223,079 229,772 229,772 229,772 229,772 919,087
Vacancy Lossd (178,463) (178,463) (117,117) (39,039) (11,154) (11,154) (11,489) (11,489) (11,489) (11,489) (45,954)
Bad Debtc 0.50% (4,462) (4,462) (1,115) (1,115) (1,115) (1,115) (1,149) (1,149) (1,149) (1,149) (4,595)
Effective Gross Revenue 709,392 709,392 104,847 182,925 210,810 210,810 217,134 217,134 217,134 217,134 868,537
Operating Expensesc 3% (319,297) (319,297) (79,824) (79,824) (79,824) (79,824) (82,219) (82,219) (82,219) (82,219) (328,876)
Net Operating Income 390,095 390,095 $25,023 $103,101 $130,986 $130,986 $134,915 $134,915 $134,915 $134,915 $539,661

Net Cash Flow before Debt during First Three Years (6,084,873) (1,718,085) (4,579,820) 213,032 (1,672,205) (1,294,205) (1,294,205) (319,205) (19,243) 58,835 86,720 86,720 134,915 134,915 134,915 134,915 $539,661

Equity Contribution Account

Total Development Costse 8,274,208
Maximum Loan Balancee 6,917,349
Equity Required 1,356,858 1,356,858 1,356,858 0 0 0 0 0 0 0 0 0 0
Equity Account Ending Balancef 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858 1,356,858

Construction Loan Account and Interest Calculation

Beginning Balance 366,645 5,588,891 366,645 2,074,932 3,450,799 4,867,941 5,588,891 5,776,465 5,891,587 5,982,279
Loan Draw & Releases
Construction Draw—Initial Requestg 6,093,109 361,227 5,554,820 177,063 1,672,205 1,294,205 1,294,205 1,294,205 44,266 44,266 44,266 44,266
Construction Releasesh 70% (682,500) 0 (682,500) 0 0 0 0 (682,500) 0 0 0 0
Operating Deficit 0 0 0 0 0 0 0 0 0 0 0 0
Trial Balance 6,026,545 361,227 5,479,646 6,026,545 2,038,850 3,369,137 4,745,004 5,479,646 5,633,157 5,820,730 5,935,853 6,026,545
Additional Equity Required 0 0 0 0 0 0 0 0 0 0 0 0
Construction Draw—Net Fundedg 6,093,109 361,227 5,554,820 177,063 1,672,205 1,294,205 1,294,205 1,294,205 44,266 44,266 44,266 44,266
Ending Balance before Interest 361,227 5,479,646 6,026,545 2,038,850 3,369,137 4,745,004 5,479,646 5,633,157 5,820,730 5,935,853 6,026,545
Average Loan Balance Before Interest 180,613 2,923,145 5,807,718 1,202,748 2,722,035 4,097,901 5,173,793 5,611,024 5,798,597 5,913,720 6,004,412
Total Construction Loan Interest 12.0% 1,101,145 5,418 395,894 699,833 36,082 81,661 122,937 155,214 168,331 173,958 177,412 180,132
Interest Accrued during Construction Period 401,313 5,418 395,894 0 36,082 81,661 122,937 155,214 0 0 0 0
Interest Accrued during Operating Period 699,833 0 0 699,833 0 0 0 0 168,331 173,958 177,412 180,132
Interest Paid from Operations (390,095) 0 0 (390,095) 0 0 0 0 (25,023) (103,101) (130,986) (130,986)
Interest Paid from Condominium Salesh (45,968) 0 (45,968) 0 0 0 0 (45,968) 0 0 0 0
Trial Ending Balance 6,075,692 366,645 5,588,891 6,075,692 2,074,932 3,450,799 4,867,941 5,588,891 5,776,465 5,891,587 5,982,279 6,075,692
Additional Equity Required 0 0 0 0 0 0 0 0 0 0 0 0
Interest Accrued—Net i
665,082 5,418 349,926 309,738 36,082 81,661 122,937 109,245 143,308 70,857 46,426 49,147
Ending Balance 6,075,692 366,645 5,588,891 6,075,692 2,074,932 3,450,799 4,867,941 5,588,891 5,776,465 5,891,587 5,982,279 6,075,692

Total Additional Equity Required 0 0 0 0 0 0 0 0 0 0 0 0

Net Cash Flow after Debt 246,532 $0 246,532 0 $0 $0 $0 $246,532 $0 $0 $0 $0

= link from another sheet

a
Expenses related to the selling of condominium units have already been included as part of the development costs.

b
The Vacancy calculation assumes that the units leased in the present quarter are economically realized in the middle of the quarter. Hence, the Vacancy for the quarter is an average of the vacancy from the prior quarter and present quarter.

c
The following estimates pertaining to the revenue and expenses of the apartment and office portions of the project are taken from the pro forma, Figure 4-3b. Gross potential revenue and operating expenses are inflated 3 percent in the second year.

d
Vacancy Loss is a product of the Overall Vacancy Rate, which was determined by the absorption schedule in the preceding section.

The Total Development Costs after Subsidies was previously provided in the Development Costs worksheet, Figure 4-3b, and includes interest and operating reserves. The Maximum Loan Balance was previously provided by the Maximum Debt
e

Calculation worksheet, Figure 4-3c, and is capped so as to not exceed the Development Costs.

Banks want to ensure that developers have sufficient equity up front so they require that all the equity be committed first, before draws from the construction loan are allowed. Construction draws cover any remaining shortfall in funding.
f

g
Construction Draws are provided by the lender as construction progresses. In the event that the draw request, together with the carried balance of the construction loan, exceeds the maximum draw limit, then additional equity is required to maintain
the construction loan balance at the maximum draw limit. The net construction draw amount is the amount borrowed after additional equity is contributed, if any. Also note that any operating deficits that need to be funded by the lender are requested
and included as part of the draw.
 figure 4-5
Stage 3a Analysis
Cash Flows during Development Period, Including Initial Lease-Up Activities
– Development – – Lease-Up – – First Stabilized Year –
g Data
Construction Draws are provided by the lender as construction progresses. InTotal
the event thatTime Zerorequest,
the draw Year 1 Totalwith the
together Year 2 Total
carried balance Quarter 1 Quarter
of the construction loan,2exceeds
Quarter 3
the maximum Quarter 4 then
draw limit, Quarter 5 equity
additional Quarter 6
is required Quarter 7
to maintain Quarter 8 Quarter 9 Quarter 10 Quarter 11 Quarter 12 Year 3 Total
the construction loan balance at the maximum draw limit. The net construction draw amount is the amount borrowed after additional equity is contributed, if any. Also note that any operating deficits that need to be funded by the lender are requested
and included as part of the draw.

h
Construction releases are provided by sales of condominium units. Specific to this case, it is assumed that releases are scheduled as 70 percent of the revenues. Additionally, the portion of the interest accrued during the development period is paid
as condominiums are sold. Refer to Figure 4-4b for revenue, expense, and tax calculations for the condominiums.

Accrued interest is added to the overall balance of the construction loan. In the event that the accrued interest, together with the carried balance of the construction loan, exceeds the maximum draw limit, then additional equity is required to maintain
i

the construction loan balance at the maximum draw limit. The net accrued interest amount is the amount accrued after additional equity is contributed, if any.
 figure 4-6
Stage 3b Analysis
Development Cost Summary
YEAR
USES Total 0 1 2

Total Development Costs $7,449,968 $1,718,085 $5,554,820 $177,063

Construction Loan: Capitalized Interest $401,313 5,418 395,894 0
Total Capital Costs $7,851,281 $1,723,503 $5,950,714 $177,063

Cash Flow from Operations

Net Sales Revenue of Condominiums 975,000 0 975,000 0
Net Operating Income of Apartments 390,095 0 0 390,095
Less: Construction Loan Interest during Operations (699,833) 0 0 (699,833)
Less: Construction Loan Releases (682,500) 0 (682,500) 0
Cash Flow from Operations after Interest (17,238) 0 292,500 (309,738)

Construction Loan Balance to Be Refinanced 0 0

TOTAL USES $7,868,518 $1,723,503 $5,658,214 $486,801

SOURCES

Construction Loan Funding

Construction Loan: Net Draws 6,093,109 361,227 5,554,820 177,063
Construction Loan: Net Accrued Interest 665,082 5,418 349,926 309,738
Net Construction Loan Funding 6,758,192 366,645 5,904,746 486,801

Equity Sources 1,356,858 1,356,858 - -

Additional Equity Required - - - -
Permanent Mortgage Refinancing - -
Less: Positive Cash Flow after Interest—Distributed (246,532) - (246,532) -
Less: Cash Proceeds from Construction Loan Takeout - -

TOTAL SOURCES 7,868,518 1,723,503 5,658,214 486,801

Check
Equity for cap. Inv (equity sources+cash flow from ops—positive cash
flow after int)a 1,093,089 1,356,858 45,968 (309,738)

Equity for Capital Investment (Total Capital Costs—Loan Sources) 1,093,089 1,356,858 45,968 (309,738)

See Figure 4-8 in text for the following:

Capital Costs
Total Development Cost Excluding Interest $ 7,449,968
Interest Accrued during Construction (Figure 5-4, Line 72) $ 401,313
Total Capital Costs $ 7,851,281

Depreciable Basis Annual Depreciation Estimate

Total Capital Costs $ 7,851,281 27.5 Life Years
Land Cost $ 1,251,500 1.0 Accel. Factor
Depreciable Basis (Capital Cost, minus Land) $ 6,599,781 $239,992 Annual Deprec.

Operating Reserve
Operating Loss during Lease-Up (Figure 5-4, Line 45) $ 0
Interest Accrued during Operating Period (Figure 5-4, Line 73) $ 699,833
Interest Paid during Operating Period (Figure 5-4, Line 74) $ (390,095)
Total Operating Reserve funded by Construction Loan $ 309,738

Total Net Project Costs

Total Project Cost (Capital Costs plus Operating Reserve) $ 8,161,018
Positive Cash Flow after Interest (246,532)
Interest Paid from Condominium Sale (45,968)
Total Project Cost after First-Year Operations (Year 2) $ 7,868,518
Construction Loan Takeout at Stabilization
Permanent Mortgage Amount for Income Propertyb $ 6,075,692
Construction Loan Ending Balance for Income Property $ 6,075,692
Cash Proceeds from Construction Loan Takeout $ -

= link from another sheet

a
Equity for Capital Investment provides a helpful check for Stage 3. One must be careful not to double-count this equity since it comes not only
from new equity but also from positive operating cash flows during lease-up. Line 37 and Line 38 should be equal for each year.

b
Specific to this underwriting, the Permanent Mortgage amount is assumed to be the lesser of the maximum loan amount for the income
property only (as calculated in Figure 4-3c) or the balance of the construction loan. However, under certain conditions, this assumption can be
conservative and cash proceeds can be generated at the time of refinancing.
 figure 4-7
Stage 3c Analysis
Combined Annual Before- and After-Tax Cash Flows during Development and Operating Period
– Development Period – – Investment Period –
Mortgage Calculation Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8
Beginning Balancea 6,075,692 6,075,692 5,997,532 5,914,759 5,827,098 5,734,262 5,635,944
Ending Balance 5,997,532 5,914,759 5,827,098 5,734,262 5,635,944 5,531,823
Amortization of Principal 78,159 82,774 87,661 92,836 98,317 104,122
Interest/Annual Payment 5.75% 425,473 347,314 342,699 337,812 332,637 327,156 321,351

DEPRECIATION CALCULATION
Beginning Balanceb 6,599,781 6,359,788 6,119,796 5,879,804 5,639,812 5,399,820 5,159,828
Less: Annual Depreciation 239,992 239,992 239,992 239,992 239,992 239,992 239,992
Ending Balance 6,359,788 6,119,796 5,879,804 5,639,812 5,399,820 5,159,828 4,919,836
Cumulative Depreciation Taken 239,992 479,984 719,976 959,968 1,199,960 1,439,952 1,679,944
Cumulative Straight Line 239,992 479,984 719,976 959,968 1,199,960 1,439,952 1,679,944
Recapture 239,992 479,984 719,976 959,968 1,199,960 1,439,952 1,679,944
Remaining Book Value 6,359,788 6,119,796 5,879,804 5,639,812 5,399,820 5,159,828 4,919,836

ANNUAL CASH FLOWS Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8
Gross Rent 3% 0 892,317 919,087 1,004,311 1,034,440 1,065,473 1,097,437 1,130,360
Vacancy Rate 5% 85.00% 20.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Vacancy ($) 0 (178,463) (45,954) (50,216) (51,722) (53,274) (54,872) (56,518)
Bad Debt 0.50% 0 (4,462) (4,595) (5,022) (5,172) (5,327) (5,487) (5,652)
Effective Gross Revenue 0 709,392 868,537 949,074 977,546 1,006,872 1,037,078 1,068,191

Operating Expenses 3.00% 0 (319,297) (328,876) (338,742) (348,905) (359,372) (370,153) (381,258)
Net Operating Income 0 390,095 539,661 610,331 628,641 647,500 666,925 686,933
- Construction Loan Interest During Operatingc 0 (390,095)
- Annual Debt Service (425,473) (425,473) (425,473) (425,473) (425,473) (425,473)
+ Operating Reserve Funded by Construction Loand 0

Before-Tax Cash Flow 0 114,188 184,858 203,168 222,027 241,452 261,460

Taxes (see below) 0 0 0 0 0 4,735 37,823

After-Tax Cash Flow 0 114,188 184,858 203,168 222,027 236,717 223,637

INCOME TAX CALCUATION FOR APARTMENTS & RETAIL Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8

Net Operating Income - 390,095 539,661 610,331 628,641 647,500 666,925 686,933
Add: Replacement/ Capital Reserve 7,950 8,189 8,434 8,687 8,948 9,216 9,493
Deduct: Interest - (390,095) (347,314) (342,699) (337,812) (332,637) (327,156) (321,351)
Deduct: Depreciation - (239,992) (239,992) (239,992) (239,992) (239,992) (239,992) (239,992)
Taxable Income/(Loss) - (232,042) (39,457) 36,074 59,524 83,819 108,994 135,083
Passive Loss Offset - - - (36,074) (59,524) (83,819) (92,081) -

Taxable Income - - - - - - 16,912 135,083

Passive Loss Carryforward - (232,042) (271,499) (235,425) (175,901) (92,081) - -

Annual Income Taxes 28.00% rate - - - - - - 4,735 37,823

SALE CALCULATION OF APARTMENTS & RETAIL (Incl. Tax) Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Cash Flow from Sale

Sale Price (cap rate applied to next year 7.5% cap rate 9,159,108
Less: Commission 2.50% rate (228,978)
Adjusted Sales Price 8,930,130
Less: Remaining Mortgage Balance (5,635,944)
Before-Tax Cash Flow from Sale 3,294,186

Total Tax at Sale (recapture & capital gain, see below) (701,827)

After-Tax Cash Flow from Sale 2,592,359

Tax Calculation at Sale

Adjusted Sales Price 8,930,130
Remaining Book Value (5,159,828)
Total Taxable Gain 3,770,302
Passive Loss Carryforward -
Total Net Taxable Gain 3,770,302

Total Depreciation Taken 1,439,952

Recapture Tax @ 25% 25.00% rate 359,988

Capital Gain 2,330,350

Deduct: Capital Reservese (51,424)
Net Capital Gain 2,278,926
Tax on Capital Gain 15.00% rate 341,839

Total Tax at Sale 701,827

RETURN MEASURES Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Unleveraged IRR
Project Cost $ (7,449,968)
For-Sale Revenuesf 975,000
Net Operating Income - 390,095 539,661 610,331 628,641 647,500 666,925
Adjusted Sales Price 8,930,130
Unleveraged Cash Flow $ (7,449,968) $ 975,000 $ 390,095 $ 539,661 $ 610,331 $ 628,641 $ 647,500 $ 9,597,056

Unleveraged IRR 10.68%

Net Present Value @ 8.0% g $1,099,933

Before-Tax IRR
 Initial & Additional Equity Required $ (1,356,858) $ 0 $ 0
Before-Tax Cash Flow from Condominiumsf 246,532
Before-Tax Operating Cash Flow - 0 114,188 184,858 203,168 222,027 241,452
Before-Tax Cash Flow from Refinancings -
Before-Tax Cash Flow from Sale 3,294,186
Total Before-Tax Cash Flow $ (1,356,858) $ 246,532 $ 0 $ 114,188 $ 184,858 $ 203,168 $ 222,027 $ 3,535,638

Before-Tax IRR 22.21%

Net Present Value @ 15.0% $564,467

After-Tax IRR
Initial & Additional Equity Required $ (1,356,858) $ 0 $ 0
After-Tax Cash Flow from Condominiumsf 204,461
After-Tax Operating Cash Flow - 0 114,188 184,858 203,168 222,027 236,717
After-Tax Cash Flow from Refinancings -
After-Tax Cash Flow from Sale 2,592,359
Total After-Tax Cash Flow $ (1,356,858) $ 204,461 $ 0 $ 114,188 $ 184,858 $ 203,168 $ 222,027 $ 2,829,076

After-Tax IRR 18.61%

Net Present Value @ 15.0% $262,261

a
The permanent mortgage balance was determined based on value and cash flow from the retail and apartment portion of the project. The permanent mortgage would replace the outstanding
construction loan upon stabilization of the project. Moreover, the portion of the loan amount pertaining to the condominiums would not be included since it would be paid off upon sale of the
condominium units and before stabilization of the project. Note that the construction loan is interest only, whereas the permanent mortgage is amortizing.

b
The depreciable basis is the total project cost, excluding land costs and operating losses during the lease-up period. The remaining book value includes the land cost. Personal property is
included in the depreciable basis here for simplicity. It can be tracked separately. Also, apartment buildings may be brought onstream at different successive months as construction is
completed. A separate depreciation spreadsheet may be added to account for these nuances. That level of accuracy, however, is inappropriate for Stage 3 analysis since other assumptions
are at best good approximations.

c
Construction Interest During Operating represents the amount of interest charged during the operating period that was paid from operating revenues. Note that since condominium sales and
profits have been consolidated and shown separately, the amount of interest paid from condominium sales are not shown. See Figure 4-4b for specific detail regarding the revenues,
expenses, and taxes pertaining to the condominiums.

d
The operating reserve includes funds needed to cover operating costs and debt service during the lease-up period.

e
Specific to this model, a simplifying assumption has been made that the yearly replacement reserves (shown as being included in the net operating income in Figure 4-3b) are accumulated
during the hold period and spent on capital needs immediately before the sale of the Property. Consequently, the replacement reserve amount spent on capital needs is not depreciated. As a
result, the amount is deducted from the estimated capital gains from sale of the Property.

Cash flows related to the sales and profit of the For-Sale Condominiums are calculated separately and shown in Figure 4-4b.
f

g
Net Present Value equals the present value of future cash flows, less the initial investment. Note that the analysis assumes all equity is invested at the beginning of the project. Also note that
the unleveraged NPV represents the development profit.
 figure 4-9
Stage 5 Analysis
Investor Return

Initial & Additional Equitya $ 1,356,858

Investor Equity Contributionb 95%
Cumulative Preferred Return 8%
Investor Proportionate Payback of Equityb 95%
Investor Share of Remaining Cash Flow 70%

– Development Period – – Operating Period –

Initial Investment Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Before-Tax Cash Flow $ (1,356,858) $ 246,532 $ 0 $ 114,188 $ 184,858 $ 203,168 $ 222,027 $ 3,535,638

Preferred Returnb
Beginning Equity Account Balance 1,356,858 1,218,875 1,218,875 1,218,875 1,212,360 1,106,180 972,648
Preferred Return Earned 108,549 97,510 97,510 97,510 96,989 88,494 77,812
Preferred Return Paid Currently 108,549 0 97,510 97,510 96,989 88,494 77,812

Unpaid Return Accountb

Beginning Balance 0 0 97,510 80,832 0 0 0
Deferred Preferred Return 0 97,510 0 0 0 0 0
Deferred Preferred Return Paid 0 0 16,678 80,832 0 0 0
Ending Balance 0 97,510 80,832 0 0 0 0

Equity Account Balanceb

Beginning Equity Account Balance 1,356,858 1,218,875 1,218,875 1,218,875 1,212,360 1,106,180 972,648
Equity Payback 137,983 0 0 6,516 106,179 133,533 972,648
Ending Balance 1,218,875 1,218,875 1,218,875 1,212,360 1,106,180 972,648 0

Equity Payments Recapb

Preferred Return Paid Currently 108,549 0 97,510 97,510 96,989 88,494 77,812
Deferred Preferred Return Paid 0 0 16,678 80,832 0 0 0
Equity Payback 137,983 0 0 6,516 106,179 133,533 972,648
Total Payments on Equity 246,532 0 114,188 184,858 203,168 222,027 1,050,459

Remaining Cash Flow

Before-Tax Cash Flow 246,532 0 114,188 184,858 203,168 222,027 3,535,638
Total Payments on Equity 246,532 0 114,188 184,858 203,168 222,027 1,050,459
Remaining Cash Flow 0 0 0 0 0 0 2,485,179

Investor Share of Equity & Remaining Cash Flow (based on terms above)
Investor Share of Equity Payments 234,205 0 108,478 175,615 193,010 210,926 997,936
Investor Share of Remaining Cash Flow 0 0 0 0 0 0 1,739,625
Total Share to Investor 234,205 0 108,478 175,615 193,010 210,926 2,737,562

Developer Share of Equity & Remaining Cash Flow

Developer Share of Equity Payments 12,327 0 5,709 9,243 10,158 11,101 52,523
Developer Share of Remaining Cash Flow 0 0 0 0 0 0 745,554
Total Share to Investor 12,327 0 5,709 9,243 10,158 11,101 798,077

Investor Cash Flows

Before-Tax Cash Flow to Investor $ (1,289,015) $ 234,205 $ 0 $ 108,478 $ 175,615 $ 193,010 $ 210,926 $ 2,737,562

Investor Before-Tax IRR 19.41%

Net Present Value, at 15.0% 302,676

Developer Cash Flows

Before-Tax Cash Flow to Developer $ (67,843) $ 12,327 $ 0 $ 5,709 $ 9,243 $ 10,158 $ 11,101 $ 798,077

Developer Before-Tax IRR 47.25%

Net Present Value, at 15.0% $ 261,791

a
In this simplified investor return analysis, it is assumed that all equity, or committed capital by the investors, is invested in the initial year into an escrow account.

In this analysis, it is assumed that the investor and the developer have equal priority to the preferred return and that the payback of their respective equity is
b

proportional to their contributed amount. Hence, the preferred return and equity balance calculations are performed for both the investor and the developer
and later individually proportioned according to their proportionate share.