Real Estate Financial Modeling Tools

Free Job Interview Test Set-ups For Ground-Up Mixed-Use Apartment Building Development Modeling

$0.00

SKU: SKU-735 Categories: ,

Description

These free set-ups simulate the type of assignment you might get in a job interview hands-on test that requires you to build a development Excel model from scratch under a time constraint.

Instructions: Using the assumptions given, build out a 7-year annual projection model for the ground-up development, operation, refinancing (in the case of setup #1 only) and sale of a mixed-use apartment building property.

A complete solution will include the following sections:

  1. Sources and Uses of funds
  2. Cash Flow
  3. Sensitivity Analysis
  4. Equity Joint Venture Cash Flow Waterfall

NOTE: To allow for the use of data tables in the analysis, please do use circular references in your sizing of the sources of funds. In other words, the senior loan will be sized as a given % of the eligible project costs and it will also fund 100% of its own interest reserve amount.

When you are done with the property-level Excel projections, create the following two-way data tables for sensitivity analysis that show the impact to the project-level IRR given simultaneous changes to these sets of variables:

Base Building Hard Costs & Contingency per unit vs. Average Monthly Rent

Hard Costs & Contingency –

  • Base: $200,000
  • +2%
  • +4%
  • +6%
  • +8%

Average Monthly Rent, today’s value (apartments)

  • Base: $4.00 PSF
  • -$0.05
  • -$0.10
  • +$0.05
  • +$0.10

 

Hard Costs & Contingency per unit vs. Asset Sale Capitalization Rate

Hard Costs & Contingency –

  • Base: $200,000
  • +2%
  • +4%
  • +6%
  • +8%

Asset Sale Capitalization Rate –

  • Base: 6.00%
  • +0.25%
  • +0.50%
  • -0.25%
  • -0.50%

 

Questions to answer:

  1. What is the going-in cap rate for this transaction in Elapsed Year 5?
  2. What is the Elapsed Year 5 cash-on-cash return?
  3. Assuming a project annual discount rate of 15%, does the project create value according to the NPV calculation on an Unlevered basis? On a Levered basis?
  4. What is the Debt Service Coverage ratio of the Permanent Loan in Elapsed Year 5?
  5. What is the total profit to the Sponsor?

.

Want to get the two unlocked Excel solution sets and two illustrative Investment Committee-style 1-page writeups?

Buy the Solution Sets

You may also like…

$25 OFF the top Real Estate Investments book for a LIMITED TIME with code: holilinn BUY THE BOOK
left to take 50% off with coupon code: yearend50