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)
