Instructions: after thoroughly reading (and rereading) the following

Instructions: After thoroughly reading (and rereading) the following documentation, construct a new 10-year, discounted cash flow, Excel pro forma valuation model starting from an empty workbook. Once the model has been constructed, tested, and is considered complete, access the Blackboard “Pro Forma” assignment and answer project specific questions. You will upload your Excel model as part of the online assignment. Note, no additional write up is required with this submission (i.e., only the Excel file will be submitted).

Project Characteristics:

Purchase Price: $4,000,000

Renovation: $500,000


Legal: 1.0% of Purchase Price


Finance Cost: 1.5% of Purchase Price


Product Type: Three story building; five (5) Apartment Units

Annual Maintenance: 2.00% Net Revenue

Annual Management: 3.00% Net Revenue

Rent Roll          Rate               Starting (period)                     Vacancy (wk/yr)

Unit 1              $4,000             Jan 1,2016 (1)                         2

Unit 2              $4,000             Jan 1,2016 (1)                         2

Unit 3              $5,500             April 1, 2016 (4)                      3

Unit 4              $5,500             April 1,2016 (4)                       3

Unit 5 (PH) )         $12,000       September 1, 2016 (9)               0

Rental Increase: 2.00% year over year

Expense Increase: 2.25% year over year

Janitorial/Y ear: $5,000

Doorman/Year: $40,000

Electricity/Y ear: $1,500

Water/Year: $1,000

Annual Tax Bill (Expected): $15,000

Insurance/Year: $5,000

Primary Loan

Term: 30 years

Start: 1/1/16 (period 1)

LTV:  80% max

Interest rate: 5.50%

Structure: CPM

Renovation Loan

Term : 5 years

Start : 1/1/16 (period 1)

LTV : 100% of renovation costs –lump sum

Interest rate : 2.00%

Structure: CPM

Capitalization Rate: 5.00%

Sales Expense: 6.00%

Discount Rate: 15.00%

Pro forma start: 1/1/16 (period 1)