Port Jefferson Apts Finance/Economics Case Study

port_jefferson_apts_excel_financial_analysis.xlsx

Home >Business & Finance homework help >Port Jefferson Apts Finance/Economics Case Study

List of Tables

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 Rent Summary

figure 4-3b Pro Forma NOI

figure 4-3c Maximum Debt Calculation

figure 4-3d Development Costs

figure 4-3e

Stage 1 Analysis

figure 4-4 Stage 2 Analysis

figure 4-6 Stage 3 Analysis (Part 1) Cash Flows During Development Period, Including Initial Lease-Up Activities

figure 4-7 Final Development Cost Summary

figure 4-8 Stage 3 Analysis (Part 2) Annual Before and After-Tax Cash Flows during Development and Operating Period

figure 4-9 Stage 5 Analysis – Investor Return

Notes

1. Project Overview

PROJECT OVERVIEW
Mortgage Amount	28,826,831	70%		Managing Member's Equity:	617,810	5%
Equity Amount	12,356,208	30%		Member's Equity	11,738,397	95%
Total Project Costs	41,183,039	100%		Total Equity	12,356,208	100%
Land Price	3,900,000
Hard Costs (incl. contingency + cost of bridge)	28,954,915
Soft Costs (incl. contingency)	5,049,981
Financing Costs	432,402
Developer's Fee	1,233,462
Total Development Cost	39,570,761
Construction Loan Interest	1,109,833
Operating Reserve	502,445
Total Project Cost	41,183,039
STAGE I ANALYSIS:
Overall Return (Overall Cap Rate)	6.54%
Cash Throw-Off	730,936
Cash-On-Cash Return	5.92%
Development Profit	7,817,733
STAGE II ANALYSIS: SALE IN YEAR 5
Unleveraged Returns
Unleveraged IRR	12.20%
Net Present Value	-6,979,229
Leveraged Before-Tax Return Analysis
Before-Tax IRR	23.37%
Net Present Value	3,406,803
Leveraged After-Tax Return Analysis
After-Tax IRR	19.66%
Simple Return Measures
Year	1	2	3	4	5
NOI/Project Cost	6.54%	6.74%	6.94%	7.15%	7.37%
Before Tax Cash Flow/Equity	5.92%	6.57%	7.24%	7.94%	8.65%
Tax Shelter/Equity	0.00%	0.00%	0.00%	0.22%	0.00%

2. Rental Rate

RENTAL RATE
Property	Avg. Rent/SF($)	Weight
The Barnum House	1.90	2%
Fairfield Falls	2.12	22%
Fairfield Landmark	2.34	24%
Fairfield	2.65	22%
Avalon Pines	1.65	10%
Avalon Charles Pond	1.56	10%
Avalon Commons	1.95	10%
Unweighted Rental Rate	2.02	100%
Weighted Rental Rate	2.17
Weighted Rental Rate with 20% Premium	2.60

3. Rent Summary

RENT SUMMARY
Item	No. of Units	Rent/s.f.	Rentable Area/Unit s.f.	Total rentable s.f.	Total Gross s.f.	Rent/Month/Unit $	Total Annual Rent for Unit Type $
Avg. Unit	112	2.60	1,086	121,632	126,291	2,824	3,794,918
Total Rental Income	112	2.60	1,086	121,632	126,291	2,824	3,794,918
Other Income (i.e. parking, concessions)	112	0.09	1,086	121,632	126,291	95	127,680
Total Income	112	2.69	1,086	121,632	126,291	2,919	3,922,598
*Blue value indicates assumption.

4. Pro Forma NOI

PRO FORMA NOI - First Stabilized Year
	Factor	Per Unit/Year	Annual Revenue/Cost
INCOME:
Gross Potential Rental Income + Other Income			3,922,598
Less: Vacancy	5.0%		196,130
Adjusted Gross Rent			3,726,468
Total Revenue			3,726,468

EXPENSES:
Total Property Management Fees (% of Adjusted Gross Rent)	3.0%	998	111,794
Total Payroll & Payroll Taxes		1,900	212,800
Total Utilities		464	51,968
Total Administrative Exp.		600	67,200
Total Marketing Expenses		1,350	151,200
Total Contracted Services		250	28,000
Total Rental Expense		371	41,552
Total Maintenance Expense		1,350	151,200
Total Taxes, Insurance & Fees		1,926	215,712
Total Operating Expenses		9,209	1,031,426
Net Operating Income			2,695,042
*Blue value indicates assumption.

5. Debt Calculation

MAXIMUM DEBT CALCULATION
Pro Forma NOI and Value
Pro Forma NOI	2,695,042
Capitalization Rate	5.5%
Value (NOI/Cap Rate)	49,000,771
Loan Terms
Interest Rate	5.5%
Amortization (years)	30
Option (i): Using Loan to Value (LTV)
Maximum LTV	70.0%
Maximum Loan Based on LTV	34,300,540
OR
Option (ii): Using Loan-to-Cost (LTC)
Maximum LTV	70.0%
Total Project Costs	41,181,187	41,183,039
Maximum Loan Based on LTC	28,826,831
OR
Option (iii): Using Debt Service Coverage Ratio (DSCR)
Monthly NOI	224,587
DSCR	1.25
Maximum Monthly Payment (NOI/DSCR/12)	179,669
Maximum Loan Based on DSCR	31,643,708
Maximum Loan (Lesser of LTV, LTC or DSCR)
Maximum Principal	28,826,831
Monthly Payment	163,676
Annual Debt Service	1,964,107
*Blue value indicates assumption.

6. Development cost

DEVELOPMENT COSTS
Number of Units				112
Total Square Footage				126,291
Land Acquisition & Project Costs:				Total Cost
Land Cost		34,821	per unit	3,900,000
Land Carry		Rate	No. of Months
		12%	0
Total Land Acquisition Cost				3,900,000
Construction Cost		217.60	per s.f. gross area	27,480,500
Bridge Cost		5.15		650,000
Hard Cost Contingency		3%	of hard cost (excl. bridge)	824,415
Total Hard Cost				28,954,915
Soft Costs:
Item		Cost Per Unit*	Total Cost (for all units)		Assumptions
	Accounting Fees	536	60,000		Estimate
	Architectural/MEP - Predevelopment	1,379	154,500		Includes 6k reimbursement
	Legal Fees	3,973	445,000		Includes 195k for Closing Legal
	Bank Consultant	536	60,000
	Architectural/MEP - development	7,594	850,500		Includes 9k reimburs
	Environmental Survey	1,116	125,000		DEIS - Phase 1 and 2, Asbestos Survey
	Equipment and Amenities	2,232	250,000
	Insurance (GL,BR & Umbrella)	4,464	500,000		Estimate from Peoples Insurance
	Permits, Fees and Offsites	4,107	460,000		Building Permit, Site Plan App, Proffer Obligations, SCDHS, DEC,
	Traffic Study	366	41,000
	RE Taxes During Construction	625	70,000		Currently $35k/year
	Recordation Fees	2,321	260,000		1.45% of Loan Amount
	Security at Site	446	50,000		During construction
	Civil Engineering	2,714	304,000		Includes SWPP Inspections, includes Landscape Architect $50k
	Survey-TOPO	116	13,000
	Marketing/Advertising	2,232	250,000
	Utility Connections	1,339	150,000
	Other Soft Costs	1,875	210,000		Independent Inspections Public and Private, ADA, Thermal Moisture, LEED, Reimbursables
	CM Precon Fee	714	80,000		Reimbursable upon Site Plan approval
	Operating Reserves Year 1	4,344	486,494		5 Months of Stabilized OpEx
	Geotech	491	55,000
	Title Insurance	254	28,400		10bps or Mortgage
Total Soft Costs		43,776	4,902,894.30
Financing Costs:
	Guarantee Fee	1%		288,268
	Loan Origination Fee	0.5%	of permanent loan amount	144,134
Total Financing Costs				432,402
Total Development Cost:
Total Soft + Financing Costs				5,335,297
Developer's Fee		3%	of total budget	1,233,462
Soft Cost Contingency		3%	% of soft costs	147,087
Total Development Cost (excl. Interest and Operating Reserve for Lease-up)				39,570,761
Estimate of Construction Interest
	Permanent Loan			28,826,831
	Construction Interest			5.50%
	Construction Period (months)			14
	Average Draw			60%
Estimated Construction Loan Interest				1,109,833
Total Project Cost before Operating Reserve				40,680,594
Estimate of Operating Reserve
	Gross potential Rent (monthly)			326,883
	Lease-up Period (months)			8
	Average Occupancy during Lease-up			47.5%
	Estimated rent during lease-up			1,242,156
	Estimated op. expenses during lease-up			687,617
NOI during lease-up				554,539
	Construction interest during lease-up			1,056,984
	Operating reserve during lease-up			502,445
Total Project Costs				41,183,039
*Blue value indicates assumption.

Construction Interest

Construction Loan

28,826,831

Beginning Month 18

Interest Rate

5.50%

Amortization (months)

360

Term (months)

120

Equity Contribution

12,356,208

Monthly Payment

163,676

- Predevelopment to Site Plan Approval -

- Predevelopment to Building Permit -

- Construction -

- Lease Up -

Loan Analysis (Monthly)

Month Beginning

5/1/13

6/1/13

7/1/13

8/1/13

9/1/13

10/1/13

11/1/13

12/1/13

1/1/14

2/1/14

3/1/14

4/1/14

5/1/14

6/1/14

7/1/14

8/1/14

9/1/14

10/1/14

11/1/14

12/1/14

1/1/15

2/1/15

3/1/15

4/1/15

5/1/15

6/1/15

7/1/15

8/1/15

9/1/15

10/1/15

11/1/15

12/1/15

1/1/16

2/1/16

3/1/16

4/1/16

5/1/16

6/1/16

7/1/16

8/1/16

9/1/16

10/1/16

11/1/16

12/1/16

1/1/17

2/1/17

3/1/17

4/1/17

5/1/17

6/1/17

7/1/17

8/1/17

9/1/17

10/1/17

11/1/17

12/1/17

1/1/18

2/1/18

3/1/18

4/1/18

5/1/18

6/1/18

7/1/18

8/1/18

9/1/18

10/1/18

11/1/18

12/1/18

1/1/19

2/1/19

3/1/19

4/1/19

5/1/19

6/1/19

7/1/19

8/1/19

9/1/19

10/1/19

11/1/19

12/1/19

1/1/20

2/1/20

3/1/20

4/1/20

5/1/20

6/1/20

7/1/20

8/1/20

9/1/20

10/1/20

11/1/20

12/1/20

1/1/21

2/1/21

3/1/21

4/1/21

5/1/21

6/1/21

7/1/21

8/1/21

9/1/21

10/1/21

11/1/21

12/1/21

1/1/22

2/1/22

3/1/22

4/1/22

5/1/22

6/1/22

7/1/22

8/1/22

9/1/22

10/1/22

11/1/22

12/1/22

1/1/23

2/1/23

3/1/23

4/1/23

5/1/23

6/1/23

7/1/23

8/1/23

9/1/23

10/1/23

11/1/23

12/1/23

1/1/24

2/1/24

3/1/24

4/1/24

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Month 13

Month 14

Month 15

Month 16

Month 17

Month 18

Month 19

Month 20

Month 21

Month 22

Month 23

Month 24

Month 25

Month 26

Month 27

Month 28

Month 29

Month 30

Month 31

Month 32

Month 33

Month 34

Month 35

Month 36

Month 37

Month 38

Month 39

Month 40

Month 41

Month 42

Month 43

Month 44

Month 45

Month 46

Month 47

Month 48

Month 49

Month 50

Month 51

Month 52

Month 53

Month 54

Month 55

Month 56

Month 57

Month 58

Month 59

Month 60

Month 61

Month 62

Month 63

Month 64

Month 65

Month 66

Month 67

Month 68

Month 69

Month 70

Month 71

Month 72

Month 73

Month 74

Month 75

Month 76

Month 77

Month 78

Month 79

Month 80

Month 81

Month 82

Month 83

Month 84

Month 85

Month 86

Month 87

Month 88

Month 89

Month 90

Month 91

Month 92

Month 93

Month 94

Month 95

Month 96

Month 97

Month 98

Month 99

Month 100

Month 101

Month 102

Month 103

Month 104

Month 105

Month 106

Month 107

Month 108

Month 109

Month 110

Month 111

Month 112

Month 113

Month 114

Month 115

Month 116

Month 117

Month 118

Month 119

Month 120

Month 121

Month 122

Month 123

Month 124

Month 125

Month 126

Month 127

Month 128

Month 129

Month 130

Month 131

Month 132

Equity Contribution

Beginning equity balance

ERROR:#REF!

Initial equity contribution

ERROR:#REF!

130,400

130,750

ERROR:#REF!

Ending equity balance

ERROR:#REF!

Total Remaining Cost After Equity Contribution

ERROR:#REF!

130,400

130,750

ERROR:#REF!

Beginning Mortgage Balance

ERROR:#REF!

-126,646

-253,123

-379,429

-505,564

-631,527

-757,317

-882,933

-1,008,375

-1,133,642

-1,258,733

-1,383,646

-1,508,383

-1,632,940

-1,757,319

-1,881,517

-2,005,534

-2,129,370

-2,253,023

-2,376,492

-2,499,777

-2,622,878

-2,745,792

-2,868,519

-2,991,059

-3,113,410

-3,235,571

-3,357,543

-3,479,323

-3,600,911

-3,722,307

-3,843,508

-3,964,515

-4,085,327

-4,205,942

-4,326,359

-4,446,578

-4,566,598

-4,686,418

-4,806,037

-4,925,454

-5,044,669

-5,163,679

-5,282,485

-5,401,085

-5,519,478

-5,637,664

-5,755,642

-5,873,410

-5,990,967

-6,108,313

-6,225,447

-6,342,368

-6,459,074

-6,575,565

-6,691,840

-6,807,897

-6,923,736

-7,039,356

-7,154,756

-7,269,934

-7,384,890

-7,499,623

-7,614,132

-7,728,415

-7,842,472

-7,956,301

-8,069,902

-8,183,274

-8,296,414

-8,409,324

-8,522,000

-8,634,443

-8,746,651

-8,858,623

-8,970,358

-9,081,855

-9,193,113

-9,304,131

-9,414,907

-9,525,441

-9,635,731

-9,745,777

-9,855,577

-9,965,130

-10,074,434

-10,183,490

-10,292,295

-10,400,849

-10,509,150

-10,617,198

-10,724,990

-10,832,526

-10,939,805

-11,046,826

-11,153,586

-11,260,086

Mortgage Contribution

ERROR:#REF!

130,400

ERROR:#REF!

37,029

37,199

37,369

37,541

37,713

37,886

38,059

38,234

38,409

38,585

38,762

38,939

39,118

39,297

39,477

39,658

39,840

40,023

40,206

40,390

40,575

40,761

40,948

41,136

41,324

41,514

41,704

41,895

42,087

42,280

42,474

42,669

42,864

43,061

43,258

43,456

43,655

43,856

44,057

44,259

44,461

44,665

44,870

45,076

45,282

45,490

45,698

45,908

46,118

46,329

46,542

46,755

46,969

47,185

47,401

47,618

47,836

48,056

48,276

48,497

48,719

48,943

49,167

49,392

49,619

49,846

50,075

50,304

50,535

50,766

50,999

51,233

51,468

51,703

51,940

52,179

52,418

52,658

52,899

53,142

53,385

53,630

53,876

54,123

54,371

54,620

54,870

55,122

55,374

55,628

55,883

56,139

56,397

56,655

56,915

57,176

57,438

Payoff Balance

ERROR:#REF!

126,646

126,477

126,306

126,135

125,963

125,790

125,616

125,442

125,267

125,091

124,914

124,736

124,558

124,378

124,198

124,017

123,836

123,653

123,470

123,285

123,100

122,914

122,727

122,540

122,351

122,162

121,971

121,780

121,588

121,395

121,202

121,007

120,811

120,615

120,418

120,219

120,020

119,820

119,619

119,417

119,214

119,010

118,806

118,600

118,393

118,186

117,977

117,768

117,558

117,346

117,134

116,921

116,706

116,491

116,275

116,057

115,839

115,620

115,400

115,178

114,956

114,733

114,509

114,283

114,057

113,829

113,601

113,371

113,141

112,909

112,677

112,443

112,208

111,972

111,735

111,497

111,258

111,018

110,776

110,534

110,290

110,046

109,800

109,553

109,305

109,056

108,805

108,554

108,301

108,047

107,792

107,536

107,279

107,020

106,761

106,500

106,238

Payoff Revenue

62,650

ERROR:#REF!

Ending Mortgage Balance

ERROR:#REF!

Ending loan balance

ERROR:#REF!

-126,646

-253,123

-379,429

-505,564

-631,527

-757,317

-882,933

-1,008,375

-1,133,642

-1,258,733

-1,383,646

-1,508,383

-1,632,940

-1,757,319

-1,881,517

-2,005,534

-2,129,370

-2,253,023

-2,376,492

-2,499,777

-2,622,878

-2,745,792

-2,868,519

-2,991,059

-3,113,410

-3,235,571

-3,357,543

-3,479,323

-3,600,911

-3,722,307

-3,843,508

-3,964,515

-4,085,327

-4,205,942

-4,326,359

-4,446,578

-4,566,598

-4,686,418

-4,806,037

-4,925,454

-5,044,669

-5,163,679

-5,282,485

-5,401,085

-5,519,478

-5,637,664

-5,755,642

-5,873,410

-5,990,967

-6,108,313

-6,225,447

-6,342,368

-6,459,074

-6,575,565

-6,691,840

-6,807,897

-6,923,736

-7,039,356

-7,154,756

-7,269,934

-7,384,890

-7,499,623

-7,614,132

-7,728,415

-7,842,472

-7,956,301

-8,069,902

-8,183,274

-8,296,414

-8,409,324

-8,522,000

-8,634,443

-8,746,651

-8,858,623

-8,970,358

-9,081,855

-9,193,113

-9,304,131

-9,414,907

-9,525,441

-9,635,731

-9,745,777

-9,855,577

-9,965,130

-10,074,434

-10,183,490

-10,292,295

-10,400,849

-10,509,150

-10,617,198

-10,724,990

-10,832,526

-10,939,805

-11,046,826

-11,153,586

-11,260,086

-11,366,324

Loan Analysis (Annual)

Year Beginning

5/1/13

5/1/14

5/1/15

5/1/16

5/1/17

5/1/18

5/1/19

5/1/20

5/1/21

5/1/22

5/1/23

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Beginning loan balance

28,826,831

ERROR:#REF!

Principal Payment

ERROR:#REF!

457,813

483,637

510,918

539,738

570,184

602,346

636,323

672,217

Interest Payment

ERROR:#REF!

Ending loan balance

ERROR:#REF!

Interest during construction period:

ERROR:#REF!

7. Stage I Analysis

STAGE I ANALYSIS: SIMPLE RATIOS
Net Operating Income (1st stabilized yr)	2,695,042
Total Project Cost	41,183,039
Overall Return (Overall Cap Rate) (1st stabilized yr)	6.5%
Net Operating Income	2,695,042
Annual Debt Service	1,964,107
Cash Throw-Off (Before-Tax Cash Flow)	730,936
Total Project Cost	41,183,039
Permanent Mortgage	28,826,831
Equity	12,356,208
Cash-On-Cash Return (1st stabilized yr)	5.9%
NOI	2,695,042
Overall Cap-Rate at Sale	5.5%
Capitalized Value	49,000,771
Total Project Cost	41,183,039
Development Profit	7,817,733
*Blue value indicates assumption.

8. Stage II Analysis- 5 Yr

STAGE II ANALYSIS - SALE IN YEAR 5
Project Costs
Total Project Cost		41,183,039
Total Proj. Cost Before Op. Reserve		40,680,594
Land Cost		3,900,000
Financing Assumptions
Equity		12,356,208
Mortgage Principal		28,826,831
Annual Interest Rate		5.50%
Amortization (years)		30
Annual Debt Service		1,964,107
Depreciation Assumptions
Building Basis		37,283,039
Life (in years)		27.5
Acceleration Factor		1
Straight line (calculated)		1,355,747
Year			1	2	3	4	5	6	7	8	9	10
MORTGAGE CALCULATION		Year	1	2	3	4	5	6	7	8	9	10
Beginning Balance			28,826,831	28,438,508	28,028,280	27,594,912	27,137,099	26,653,462	26,142,544	25,602,805	25,032,622	24,430,275
Amortization of Principal			388,323	410,228	433,368	457,813	483,637	510,918	539,738	570,184	602,346	636,323
Interest Payment			1,575,784	1,553,879	1,530,739	1,506,294	1,480,470	1,453,189	1,424,369	1,393,923	1,361,760	1,327,783
Ending Balance			28,438,508	28,028,280	27,594,912	27,137,099	26,653,462	26,142,544	25,602,805	25,032,622	24,430,275	23,793,952
DEPRECIATION CALCULATION		Year	1	2	3	4	5	6	7	8	9	10
Beginning Balance			37,283,039	35,927,292	34,571,545	33,215,798	31,860,051	30,504,304	29,148,557	27,792,811	26,437,064	25,081,317
Less: Annual Depreciation			1,355,747	1,355,747	1,355,747	1,355,747	1,355,747	1,355,747	1,355,747	1,355,747	1,355,747	1,355,747
Ending Balance			35,927,292	34,571,545	33,215,798	31,860,051	30,504,304	29,148,557	27,792,811	26,437,064	25,081,317	23,725,570
Cumulative Depreciation Taken			1,355,747	2,711,494	4,067,241	5,422,987	6,778,734	8,134,481	9,490,228	10,845,975	12,201,722	13,557,469
Cumulative Straight Line			1,355,747	2,711,494	4,067,241	5,422,987	6,778,734	8,134,481	9,490,228	10,845,975	12,201,722	13,557,469
Recapture			0	0	0	0	0	0	0	0	0	0
Remaining Book Value			39,827,292	38,471,545	37,115,798	35,760,051	34,404,304	33,048,557	31,692,811	30,337,064	28,981,317	27,625,570
ANNUAL CASH FLOWS		Year	1	2	3	4	5	6	7	8	9	10
			1	2	3	4	5	6	7	8	9	10
Gross Rent	Inflation Rate:	3.0%	3,922,598	4,040,276	4,161,485	4,286,329	4,414,919	4,547,367	4,683,788	4,824,301	4,969,030	5,118,101
Vacancy	Vacancy Rate:	5.0%	-196,130	-202,014	-208,074	-214,316	-220,746	-227,368	-234,189	-241,215	-248,452	-255,905
Adjusted Gross Income			3,726,468	3,838,263	3,953,410	4,072,013	4,194,173	4,319,998	4,449,598	4,583,086	4,720,579	4,862,196
Operating Expenses	Inflation Rate:	3.0%	1,031,426	1,062,369	1,094,240	1,127,067	1,160,879	1,195,705	1,231,577	1,268,524	1,306,580	1,345,777
Other Expenses			0	0	0	0	0	0	0	0	0	0
Total Expenses			1,031,426	1,062,369	1,094,240	1,127,067	1,160,879	1,195,705	1,231,577	1,268,524	1,306,580	1,345,777
Net Operating Income			2,695,042	2,775,894	2,859,171	2,944,946	3,033,294	3,124,293	3,218,022	3,314,562	3,413,999	3,516,419
Annual Debt Service			-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107	-1,964,107
Before-Tax Operating Cash Flow			730,936	811,787	895,064	980,839	1,069,187	1,160,186	1,253,915	1,350,455	1,449,892	1,552,312
Tax Calculation:
Net Operating Income			2,695,042	2,775,894	2,859,171	2,944,946	3,033,294	3,124,293	3,218,022	3,314,562	3,413,999	3,516,419
Interest			-1,575,784	-1,553,879	-1,530,739	-1,506,294	-1,480,470	-1,453,189	-1,424,369	-1,393,923	-1,361,760	-1,327,783
Depreciation			-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747	-1,355,747
Taxable Income (Loss)			-236,488	-133,732	-27,316	82,905	197,078	315,357	437,906	564,892	696,492	832,889
Passive Loss Offset			0	0	0	-27,316	0	0	0	0	0	0
Taxable Income			0	0	0	55,589	197,078	315,357	437,906	564,892	696,492	832,889
Passive Loss Carryforward			-236,488	-133,732	-27,316	0	0	0	0	0	0	0
Taxes	Tax Rate:	28.0%	0	0	0	15,565	55,182	88,300	122,614	158,170	195,018	233,209
After Tax Cash Flow:
Before-Tax Operating Cash Flow			730,936	811,787	895,064	980,839	1,069,187	1,160,186	1,253,915	1,350,455	1,449,892	1,552,312
Taxes			0	0	0	-15,565	-55,182	-88,300	-122,614	-158,170	-195,018	-233,209
After-Tax Operating Cash Flow			730,936	811,787	895,064	965,274	1,014,005	1,071,886	1,131,301	1,192,286	1,254,875	1,319,103
ANALYSIS OF SALE IN YEAR 5
SALE CALCULATION					Sale Year		5
Before Tax Cash Flow from Sale:
Sale Price	Cap Rate (NOIsale year +1):	5.5%					56,805,324
Closing Costs		3.0%					-1,704,160
Adjusted Sales Price							55,101,164
Remaining Mortgage Balance							-26,653,462
Before-Tax Cash Flow from Sale							28,447,702
Taxes:
Adjusted Sales Price							55,101,164
Remaining Book Value							-34,404,304
Total Taxable Gain							20,696,860
Passive Loss Carryforward							0
Total Gain							20,696,860
Total Depreciation Taken							6,778,734
Recapture Tax @ 25%	Recapture Rate:	25.0%					-1,694,684
Capital Gain							13,918,126
Tax on Capital Gain	Tax Rate on Capital Gain:	20.0%					2,783,625
After-Tax Cash Flow from Sale:
Before-Tax Cash Flow from Sale							28,447,702
Total Tax							-4,478,309
After-Tax Cash Flow from Sale							23,969,394
RETURN MEASURES		Year	1	2	3	4	5	6	7
UNLEVERAGED IRR
Project Cost	Investment	-41,183,039
Net Operating Income			2,695,042	2,775,894	2,859,171	2,944,946	3,033,294
Adjusted Sales Price							55,101,164
Total Before-Tax Cash Flow	Investment	-41,183,039	2,695,042	2,775,894	2,859,171	2,944,946	58,134,458
Unleveraged IRR			=	12.20%
Net Present Value	Discount Rate @	17.0%	=	-6,979,229
BEFORE-TAX IRR
Equity	Investment	-12,356,208
Before-Tax Operating Cash Flow			730,936	811,787	895,064	980,839	1,069,187
Before-Tax Cash Flow from Sale							28,447,702
Total Before-Tax Cash Flow	Investment	-12,356,208	730,936	811,787	895,064	980,839	29,516,889
Before-Tax IRR			=	23.37%
Net Present Value	Discount Rate @	17.0%	=	3,406,803
AFTER-TAX IRR
Equity	Investment	-12,356,208
After-Tax Operating Cash Flow			730,936	811,787	895,064	965,274	1,014,005
After-Tax Cash Flow from Sale							23,969,394
Total After-Tax Cash Flow	Investment	-12,356,208	730,936	811,787	895,064	965,274	24,983,399
After-Tax IRR			=	19.66%
SIMPLE RETURN MEASURES
NOI/Project Cost			6.54%	6.74%	6.94%	7.15%	7.37%
Before Tax Cash Flow/Equity			5.92%	6.57%	7.24%	7.94%	8.65%
Tax Shelter/Equity			0.00%	0.00%	0.00%	0.22%	0.00%
*Blue value indicates assumption.

Stage III Dev. Cost Summary

DEVELOPMENT COST SUMMARY

			Year
		TOTAL	0	1	2
USES
Total Dev Costs		ERROR:#REF!	ERROR:#REF!	ERROR:#REF!	ERROR:#REF!
Capitalized Constr. Loan int.	4 quarters	ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Total Capital Costs		ERROR:#REF!	ERROR:#REF!	ERROR:#REF!	ERROR:#REF!
Cash Flow from Operations:
NOI		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Constr. Loan int during ops.		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Permanent debt service		0	0	0	0
Cash Flow from Operations After Interest		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Total Uses		ERROR:#REF!	ERROR:#REF!	ERROR:#REF!	ERROR:#REF!
SOURCES
Constr. Loan Ending Balance		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Perm. Loan ending balance		0		0	0
Equity ending balance		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!

Construction Loan Sources		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Permanent Loan Sources		0	0	0	0
Equity Sources		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Additional Equity Required		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Positive Cash Flow After Interest		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Total Sources		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Check (2 methods)
Equity for Capital Investment		ERROR:#REF!	0	ERROR:#REF!	ERROR:#REF!
Equity for Capital Investment		ERROR:#REF!	ERROR:#REF!	ERROR:#REF!	ERROR:#REF!
Summary
Capital Costs:
Total Development Cost Excluding Interest					ERROR:#REF!
Interest Accrued During Construction					ERROR:#REF!
Total Capital Costs					ERROR:#REF!
Depreciable Basis:
Total Capital Costs					ERROR:#REF!
Land Cost					ERROR:#REF!
Depreciable Basis					ERROR:#REF!
Operating Reserve:
Operating Loss During Lease-up					ERROR:#REF!
Interest Accrued During Operating Period					ERROR:#REF!
Interest Paid During Operating Period					ERROR:#REF!
Total Operating Reserve Funded by Construction Loan					ERROR:#REF!
Total Project Cost:					ERROR:#REF!
Positive Cash Flow After Interest					ERROR:#REF!
Total Project Cost After First Year Operations					ERROR:#REF!
Cash Proceeds from Permanent Loan Takeout:
Permanent Mortgage Amount					28,826,831
Construction Loan Ending Balance					ERROR:#REF!
Cash proceeds from Permanent Loan Takeout					ERROR:#REF!
Stage I vs. Stage III Comparison			Stage I		Stage III
Construction Interest			1,109,833		ERROR:#REF!
Operating Reserve			502,445		ERROR:#REF!

Stage III Combined Dev+Op Per

COMBINED ANNUAL BEFORE- & AFTER-TAX CASH FLOWS DURING DEVELOPMENT AND OPERATING PERIOD

– Development Period –

– Investment Period –

Mortgage Calculation

Initial Investment

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Beginning Balance

28,826,831

28,438,508

28,028,280

27,594,912

27,137,099

26,653,462

26,142,544

25,602,805

25,032,622

24,430,275

Ending Balance

28,826,831

28,438,508

28,028,280

27,594,912

27,137,099

26,653,462

26,142,544

25,602,805

25,032,622

24,430,275

23,793,952

Amortization of Principal

388,323

410,228

433,368

457,813

483,637

510,918

539,738

570,184

602,346

636,323

Interest

1,575,784

1,553,879

1,530,739

1,506,294

1,480,470

1,453,189

1,424,369

1,393,923

1,361,760

1,327,783

DEPRECIATION CALCULATION

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Beginning Balance

ERROR:#REF!

Less: Annual Depreciation

ERROR:#REF!

Ending Balance

ERROR:#REF!

Cumulative Depreciation Taken

ERROR:#REF!

Cumulative Straight Line

1,355,747

2,711,494

4,067,241

5,422,987

6,778,734

8,134,481

9,490,228

10,845,975

12,201,722

13,557,469

14,913,215

Recapture

ERROR:#REF!

Remaining Book Value

ERROR:#REF!

ANNUAL CASH FLOWS

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Gross Rent

3,922,598

4,040,276

4,414,919

4,547,367

4,683,788

4,824,301

4,969,030

5,118,101

5,271,644

5,429,794

5,592,687

Vacancy Rate

Vacancy ($)

ERROR:#REF!

202,014

220,746

227,368

234,189

241,215

248,452

255,905

263,582

271,490

279,634

Adjusted Gross Income

ERROR:#REF!

3,838,263

4,194,173

4,319,998

4,449,598

4,583,086

4,720,579

4,862,196

5,008,062

5,158,304

5,313,053

Total Cash In

ERROR:#REF!

3,838,263

4,194,173

4,319,998

4,449,598

4,583,086

4,720,579

4,862,196

5,008,062

5,158,304

5,313,053

Operating Expenses

1,031,426

1,082,997

1,137,147

1,194,005

1,253,705

1,316,390

1,382,210

1,451,320

1,523,886

1,600,080

1,680,084

Total Expenses

1,031,426

1,082,997

1,137,147

1,194,005

1,253,705

1,316,390

1,382,210

1,451,320

1,523,886

1,600,080

1,680,084

Net Operating Income

ERROR:#REF!

2,755,265

3,057,026

3,125,994

3,195,893

3,266,696

3,338,369

3,410,876

3,484,176

3,558,224

3,632,969

- Construction Loan Interest

ERROR:#REF!

- Annual Debt Service

-1,964,107

+ Operating Reserve Funded by Const. Loan

ERROR:#REF!

Before Tax Cash Flow

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

1,446,769

1,520,069

1,594,117

1,668,862

TAX CALCULATION

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Net Operating Income

ERROR:#REF!

2,755,265

3,057,026

3,125,994

3,195,893

3,266,696

3,338,369

3,410,876

3,484,176

3,558,224

3,632,969

- Interest

ERROR:#REF!

-1,575,784

-1,553,879

-1,530,739

-1,506,294

-1,480,470

-1,453,189

-1,424,369

-1,393,923

-1,361,760

-1,327,783

-Depreciation

ERROR:#REF!

Taxable Income (Loss)

ERROR:#REF!

Less: Passive Loss Offset

ERROR:#REF!

Taxable Income

ERROR:#REF!

Passive Loss Carryforward

ERROR:#REF!

Taxes

28%

ERROR:#REF!

AFTER-TAX CASH FLOW FROM OPERATIONS

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Before Tax Cash Flow

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

1,446,769

1,520,069

1,594,117

1,668,862

Less: Taxes

ERROR:#REF!

After Tax Cash Flow

ERROR:#REF!

SALE CALCULATION

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Year 11

Year 12

Sales Price EOY 9 (based on Year 10 NOI)

5.5%

63,348,654

Less: Commission

3.0%

1,900,460

Adjusted Sales Price

61,448,195

Less: Remaining Balance on Mortgage

25,602,805

Cash From Sale Before Tax

35,845,389

Taxes:

Adjusted Sales Price

61,448,195

Less: Remaining Book Value

ERROR:#REF!

Total Taxable Gain

ERROR:#REF!

Less: Passive Loss Carryforward

ERROR:#REF!

Total Gain after passive loss

ERROR:#REF!

Total Depreciation Taken

ERROR:#REF!

Recapture Tax

25%

ERROR:#REF!

Capital Gain

ERROR:#REF!

Tax on Capital Gain

20%

ERROR:#REF!

Total Tax From Sale

ERROR:#REF!

Cash From Sale Before Tax

35,845,389

Less: Tax

ERROR:#REF!

Cash From Sale After Tax

ERROR:#REF!

RETURN ANALYSIS

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Equity (-)

ERROR:#REF!

Cash Proceeds from Perm. Takeout

ERROR:#REF!

Before Tax Cash Flows from Operations

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

1,446,769

Cash Flow From Sale Before Tax

35,845,389

Total Before Tax Cash Flow

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

37,292,159

Before Tax IRR

ERROR:#VALUE!

Equity(-)

ERROR:#REF!

Cash Proceeds from Perm. Takeout

ERROR:#REF!

After Tax Cash Flows from Operations

ERROR:#REF!

Cash Flow From Sale After Tax

ERROR:#REF!

Total After Tax Cash Flow

ERROR:#REF!

After Tax IRR

ERROR:#VALUE!

Stage V Investors Analysis

STAGE V ANALYSIS: INVESTOR RETURN

the "whole pie" - putting together deal into standard JV syndication format; this tab deals with the priority distribution of cash flows

Initial Equity

ERROR:#REF!

Cumulative Preferred Return

Investor Priority Payback of Equity

50%

Investor Share of Remaining Cash Flow

70%

– Development Period –

– Investment Period –

Initial Investment

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Before Tax Cash Flow

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

37,292,159

Preferred Return:

Beginning Equity Account Balance

ERROR:#REF!

cumulative preferred return = any unpaid preferential return in the current year is accrued and paid in the future when cash flow is available/sufficient

Preferred Return Earned

ERROR:#REF!

non-cumulative preferred return = any year in which there is not enough cash to pay the full preferred return is simply forgotten (developers prefer this option)

Preferred Return Paid Currently

ERROR:#REF!

Unpaid Return Account:

Beginning Balance

ERROR:#REF!

Deferred Preferred Return

ERROR:#REF!

Deferred Preferred Return Paid

ERROR:#REF!

Ending Balance

ERROR:#REF!

Equity Account Balance:

Beginning Equity Account Balance

ERROR:#REF!

Equity Payback

ERROR:#REF!

Ending Balance

ERROR:#REF!

Equity Payments Re-Cap:

Preferred Return Paid Currently

ERROR:#REF!

Deferred Preferred Return Paid

ERROR:#REF!

Equity Payback

ERROR:#REF!

Total Payments on Equity

ERROR:#REF!

Remaining Cash Flow:

Before Tax Cash Flow

ERROR:#REF!

791,158

1,092,919

1,161,887

1,231,787

1,302,589

1,374,262

37,292,159

Total Payments on Equity

ERROR:#REF!

Remaining Cash Flow

ERROR:#REF!

Investor Share of Remaining Cash Flow

ERROR:#REF!

Investor Cash Flow Recap:

Investment

ERROR:#REF!

Total Payments on Equity

ERROR:#REF!

Investor Share of Remaining Cash Flow

ERROR:#REF!

Before-Tax Investor Cash Flow

ERROR:#REF!

Investor Before-Tax IRR

ERROR:#VALUE!

ideally, you want this # to be 20%, but in today's much lower IR environment, you can get deals done with IRR as low as 12-13% depending on risk. But if it's a new deal, you want to shoot for at least 15-20% because of increased risk of new development

Net Present Value

Discount Rate @

ERROR:#REF!

Developer Cash Flows:

Before-Tax Cash Flow to Developer

ERROR:#REF!

Net Present Value

Discount Rate @

ERROR:#REF!

notes

Notes
1.	Land carry refers to interest paid to the land seller as part of the land purchase contract.
2.	This calculation is a preliminary estimate of interest during construction. A more accurate estimate will be made as part of the Stage 3 analysis, and even more accurate estimate in Stage 4 (not shown).
3.	Operating Reserve during lease-up represents the subsidy that will be required to cover operating costs and debt service before the project reaches break-even occupancy.
4.	Based on market studies, the project is projected to lease at a rate of 16.6 units per month. The project will then take 12 months to be fully leased. (200 ÷ 16.6 =12)
5.	Net Present Value equals the present value of future cash flows, less the initial investment. The unleveraged NPV represents the development profit.
6.	The permanent mortgage balance was determined based on value and cash flow. During the development period, only interest would be paid on the construction loan. Amortization would begin upon the funding of the permanent loan, after stabilization.
7.	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 on-stream at different in 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 early Stage 2 analyses since other assumptions are at best good approximations.
8.	The operating reserve includes funds needed to cover operating costs and debt service during the lease-up period.
9.	Current tax laws treat real estate as passive income and limit the amount of loss one can take. Passive losses can only be taken against other passive income (with minor adjustments for small investors). In this example, passive loss limitation rules do not apply since taxable income is positive. Stage 3, Table 15, shows the treatment where Taxable Income is negative. See Brueggeman and Fisher (2002) for more information.
10	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 30 and Line 31 in Table 14 should be equal for each year.

9. Sensitivity Analysis

SENSITIVITY ANALYSIS: BASED ON TERMINAL CAP RATE CHANGES
Note: Values are hard-coded and do not automatically update. Based on assumptions noted in the "Harbor View Teaching Note".
TERMINAL CAP RATE OF 5.0%
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-3,036,558	-3,760,125	-4,466,003	-5,154,703	-5,826,721	-6,482,536
Levered Before-Tax NPV	6,669,150	6,289,874	5,920,029	5,559,339	5,207,538	4,864,369
Levered After-Tax NPV	3,977,412	3,655,369	3,341,302	3,034,979	2,736,175	2,444,673
TERMINAL CAP RATE OF 5.5%
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-5,659,996	-6,327,748	-6,979,229	-7,614,909	-8,235,244	-8,840,673
Levered Before-Tax NPV	4,045,712	3,722,252	3,406,803	3,099,133	2,799,015	2,506,232
Levered After-Tax NPV	1,878,662	1,601,271	1,330,721	1,066,814	809,357	558,163
TERMINAL CAP RATE OF 6.0%
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-7,846,194	-8,467,433	-9,073,583	-9,665,080	-10,242,346	-10,805,787
Levered Before-Tax NPV	1,859,514	1,582,566	1,312,449	1,048,961	791,913	541,118
Levered After-Tax NPV	129,703	-110,477	-344,762	-573,323	-796,325	-1,013,928
TERMINAL CAP RATE OF 6.5%
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-9,696,055	-10,277,936	-10,845,729	-11,399,841	-11,940,663	-12,468,576
Levered Before-Tax NPV	9,653	-227,937	-459,698	-685,799	-906,404	-1,121,671
Levered After-Tax NPV	-1,350,185	-1,558,880	-1,762,479	-1,961,132	-2,154,979	-2,344,159
Note: These values are based on a five-year analysis (sale in year 5). Assumes all else equal at a rental rate of $2.60/SF.
SENSITIVITY ANALYSIS: BASED ON RENTAL RATE CHANGES
RENTAL RATE OF $2.20 PER SQUARE FOOT
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-12,609,546	-13,143,997	-13,665,425	-14,174,206	-14,670,706	-15,155,275
Levered Before-Tax NPV	-4,082,272	-4,314,234	-4,540,429	-4,761,027	-4,976,188	-5,186,072
Levered After-Tax NPV	-5,177,470	-5,386,130	-5,589,617	-5,788,080	-5,981,666	-6,170,516
RENTAL RATE OF $2.40 PER SQUARE FOOT
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-9,261,765	-9,862,867	-10,449,321	-11,021,552	-11,579,969	-12,124,968
Levered Before-Tax NPV	328,933	68,044	-186,360	-434,468	-676,462	-912,521
Levered After-Tax NPV	-1,275,412	-1,502,168	-1,723,306	-1,938,989	-2,149,377	-2,354,623
RENTAL RATE OF $2.60 PER SQUARE FOOT
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-5,659,996	-6,327,748	-6,979,229	-7,614,909	-8,235,244	-8,840,673
Levered Before-Tax NPV	4,045,712	3,722,252	3,406,803	3,099,133	2,799,015	2,506,232
Levered After-Tax NPV	1,878,662	1,601,271	1,330,721	1,066,814	809,357	558,163
RENTAL RATE OF $2.80 PER SQUARE FOOT
Hurdle Rate	16.0%	16.5%	17.0%	17.5%	18.0%	18.5%
Unlevered Before-Tax NPV	-2,027,427	-2,761,829	-3,478,336	-4,177,466	-4,859,718	-5,525,577
Levered Before-Tax NPV	7,678,281	7,288,171	6,907,695	6,536,576	6,174,540	5,821,327
Levered After-Tax NPV	4,804,222	4,474,081	4,152,065	3,837,937	3,531,469	3,232,441
Note: These values are based on a five-year analysis (sale in year 5). Assumes all else equal at a terminal cap rate of 5.5%.

10. Sources & Uses

SOURCES & USES
Sources:
	Mortgage Amount (70%)		28,826,831
	Equity Amount (30%)		12,356,208
		Managing Member's Equity (5%)	617,810
		Member's Equity (95%)	11,738,397
Total Project Funding			41,183,039
Uses:
	Land Price		3,900,000
	Hard Costs (incl. contingency + bridge cost)		28,954,915
	Soft Costs (incl. contingency)		5,049,981
	Financing Costs		432,402
	Developer's Fee		1,233,462
Total Development Cost			39,570,761
	Construction Loan Interest		1,109,833
	Operating Reserve		502,445
Total Project Cost			41,183,039