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:
- Sources and Uses of funds
- Cash Flow
- Sensitivity Analysis
- 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:
- What is the going-in cap rate for this transaction in Elapsed Year 5?
- What is the Elapsed Year 5 cash-on-cash return?
- 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?
- What is the Debt Service Coverage ratio of the Permanent Loan in Elapsed Year 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