Project Summary We are advising one of our clients in the potential…

Project Summary
We are advising one of our clients in the potential disposition of a multifamily asset located in Alexandria, VA. As
part of this assignment, we are tasked with providing a Broker Opinion of Value (BOV) on the asset. The asset
comprises 240‐units and was originally built in 2010. The following additional information has been provided (use
these assumptions for Year 1):
 100 one‐bedroom units (700 SF per unit, average monthly rents of $1,500 per unit), and 140 two‐
bedroom units (1,100 SF avg size, average monthly rents of $2,200 per unit)
 Total Other Income of $1,500 per unit/year
 Total expenses are equal to $8,000/unit per month – assume that landlord covers all expenses.
 Annual Replacement Reserves are equal to $225/unit year.
 Vacancy of 5.0% annually
Task
Create a cash flow model in Excel that:
1) calculates the 7 year unleveraged and leveraged IRR of the investment
2) states the going‐in cap rate and annual cash‐on‐cash yields
3) states the NOI and profit margin for each year of the investment
4) is flexible enough to incorporate sensitivity analyses related to all of the variables listed above and below.
Your financial model should be based on the operational data provided above and the following parameters:
 Purchase price of $70,000,000
 Top line rents grow at 5.00% in Year 1, and 3.50% annually thereafter.
 Other Income grows at 2.50% per year over the investment period
 Operating expenses grow at 2.50% per year over the investment period
 Vacancy of 5.00% annually through the investment period
 Collection Loss/Bad Debt factor of 0.25% of Gross Scheduled Income annually
 Loan Terms: 60.0% LTV, 5.50% fixed rate, 10 year term, 30 year amortization, 5 years I/O (this entails
creating a loan amortization schedule to properly calculate the leveraged IRR)
 Property is sold at an 6.00% cap rate at the end of year 7