Real Estate Financial Modeling Tools

Solution Sets For Job Interview Test For Ground-Up Mixed-Use Apartment Building Development Modeling

$199.00

-

Academic / U.S. Military Price – $129.00

To get discounted pricing, click Credentials Verification in the header and follow the instructions. Once you are approved, the discounted pricing will show.

SKU: SKU-736 Categories: ,
  • Two fully-unlocked Excel file containing a dynamic 180-line tall annual models (one with a refi, and one without) with the following characteristics
    • individual line item annotations explaining the rationale and mechanics of the formulas
    • logic-based text alerts that confirm correctness of calculations
    • logic-based text alerts that alert user to mismatches and errors that would lead to faulty investment conclusions
  • Two illustrative Investment Committee-style 1-page write-ups recommending pursuit of the hypothetical development project.

This product relates to the two set-ups described below.

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 (only in the case of set-up #1) 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. Assuming a valuation cap rate of 6.00% on forward Adjusted NOI, what is the Loan-to-Value % of the Permanent Loan when it is funded?
  6. What is the total profit to the Sponsor?

 

Want to try your hand at the interview tests before buying the Solution Sets?

Get the Free Test Setups
Registration is open for April Excel Bootcamp Webinars Register Now
left to take 50% off with coupon code: yearend50