Multifamily pro forma Excel Project Each student is required to create their own Excel file. Objective - Create a pro forma for a Proposed Multifamily Development Project The objective is to create a pro forma (cash flow projection) for a proposed multifamily develo following set of project assumptions. Assumptions . Unit mix 50 one-bedroom units; average size: 950 square feet; rent: $1.30 per s.f. 95 two-bedroom units; average size: 1,280 square feet: rent: $1.12 pers.f. 70 three-bedroom units; average size: 1.450 square feet; rent: $1.00 per s.f. Other income: $85,000 Projected vacancy: 7% of PGI Expenses Property management: 2% of adjusted gross rent Controllable costs: $2,800/unit/year Real estate taxes (during construction): $650,000 Insurance: $350/unit/year Appropriate cap rate: 7.5% Loan: Constant-monthly payment, fully-amortizing Interest rate: 5.5% Amortization: 20 years Maximum LTV: 75% Minimum DCR: 1.3 Land costs: $25,242.83 per unit Land carry: 6% for 4 months Approval fees: $375 per unit Construction hard costs: $52 pers.f. gross area Soft costs Architecture & engineering: $2,000 per unit Legal: $180,000, total Appraisal & title: 0.40% of hard costs + $15,000 Marketing: $1,200 per unit Taxes during construction: $650,000 Insurance during construction: $350 per unit Loan origination costs: 1.5% of permanent loan amount . . Land carry: 6% for 4 months Approval fees: $375 per unit Construction hard costs: $52 pers.f. gross area Soft costs Architecture & engineering: $2,000 per unit Legal: $180,000, total Appraisal & title: 0.40% of hard costs + $15,000 o Marketing: $1,200 per unit Taxes during construction: $650,000 Insurance during construction: $350 per unit Loan origination costs: 1.5% of permanent loan amount Developer overhead: 4% of land + land carry + approval fees + hard costs + soft costs Contingency: 10% of construction hard costs Construction interest: 7.5% Construction period: 12 months Average draw: 55% Lease-up period: 12 months Average occupancy during lease-up: 57% Overall cap rate at sale: 7.5% Depreciation: Straight-line to zero over 27.5 years Inflation Gross rent: 4%, annual Operating expenses: 3%, annual Stabilized vacancy rate: 7% Tax rate for income: 28% Cap rate for sale: 7.5% Commission: 5% of sale price Recapture tax: 25% of accumulated depreciation Capital gains tax: 20% of capital gains (excludes accumulated depreciation) Appropriate discount rate for unleveraged cash flows: 12% (used in NPV calculation) Appropriate discount rate for leveraged cash flows: 15% (used in NPV calculation) Create six spreadsheets analogous to those in the textbook (chapter 4) and using similar formattin . Stage 1a: Rental Summary (Figure 4-3a, p.169) Stage 1b: Pro Forma NOI (Figure 4-3b, p.170) Stage 1c: Maximum Loan Amount (Figure 4-3c, p.170) Stage 1d: Development Costs (Figure 4-3d, p.171) . Stage 1e: Simple Ratios (Figure 4-3e, p.173) Stage 2: Stage 2 Analysis (Figure 4-4a, p.174-176) . . 1 Multifamily pro forma Excel Project Each student is required to create their own Excel file. Objective - Create a pro forma for a Proposed Multifamily Development Project The objective is to create a pro forma (cash flow projection) for a proposed multifamily develo following set of project assumptions. Assumptions . Unit mix 50 one-bedroom units; average size: 950 square feet; rent: $1.30 per s.f. 95 two-bedroom units; average size: 1,280 square feet: rent: $1.12 pers.f. 70 three-bedroom units; average size: 1.450 square feet; rent: $1.00 per s.f. Other income: $85,000 Projected vacancy: 7% of PGI Expenses Property management: 2% of adjusted gross rent Controllable costs: $2,800/unit/year Real estate taxes (during construction): $650,000 Insurance: $350/unit/year Appropriate cap rate: 7.5% Loan: Constant-monthly payment, fully-amortizing Interest rate: 5.5% Amortization: 20 years Maximum LTV: 75% Minimum DCR: 1.3 Land costs: $25,242.83 per unit Land carry: 6% for 4 months Approval fees: $375 per unit Construction hard costs: $52 pers.f. gross area Soft costs Architecture & engineering: $2,000 per unit Legal: $180,000, total Appraisal & title: 0.40% of hard costs + $15,000 Marketing: $1,200 per unit Taxes during construction: $650,000 Insurance during construction: $350 per unit Loan origination costs: 1.5% of permanent loan amount . . Land carry: 6% for 4 months Approval fees: $375 per unit Construction hard costs: $52 pers.f. gross area Soft costs Architecture & engineering: $2,000 per unit Legal: $180,000, total Appraisal & title: 0.40% of hard costs + $15,000 o Marketing: $1,200 per unit Taxes during construction: $650,000 Insurance during construction: $350 per unit Loan origination costs: 1.5% of permanent loan amount Developer overhead: 4% of land + land carry + approval fees + hard costs + soft costs Contingency: 10% of construction hard costs Construction interest: 7.5% Construction period: 12 months Average draw: 55% Lease-up period: 12 months Average occupancy during lease-up: 57% Overall cap rate at sale: 7.5% Depreciation: Straight-line to zero over 27.5 years Inflation Gross rent: 4%, annual Operating expenses: 3%, annual Stabilized vacancy rate: 7% Tax rate for income: 28% Cap rate for sale: 7.5% Commission: 5% of sale price Recapture tax: 25% of accumulated depreciation Capital gains tax: 20% of capital gains (excludes accumulated depreciation) Appropriate discount rate for unleveraged cash flows: 12% (used in NPV calculation) Appropriate discount rate for leveraged cash flows: 15% (used in NPV calculation) Create six spreadsheets analogous to those in the textbook (chapter 4) and using similar formattin . Stage 1a: Rental Summary (Figure 4-3a, p.169) Stage 1b: Pro Forma NOI (Figure 4-3b, p.170) Stage 1c: Maximum Loan Amount (Figure 4-3c, p.170) Stage 1d: Development Costs (Figure 4-3d, p.171) . Stage 1e: Simple Ratios (Figure 4-3e, p.173) Stage 2: Stage 2 Analysis (Figure 4-4a, p.174-176) . . 1