Real Estate Multi Family Development Excel Model

Real Estate Financial Model to evaluate a development project, with Equity Waterfall and Advanced Scenarios Modeling

by Jair Almeida
Business Controller and FreelancerFollow 55

5 reviews3,620 views|4 comments|Bookmark Add to cart - $40.00

Description
This template is a fully functional, institutional quality, and dynamic real estate Development Financial Model. It’s a powerful yet very user-friendly model that will provide the user with a detailed Excel spreadsheet in which the viability of an investment in a multi-family real estate property can be accessed with accuracy.



Model Options:
•Dynamic Monthly & Annual Cash Flow: to calculate the key investments metrics needed to determine the feasibility of the project and the overall value of a potential selling, the cashflow is calculated on a monthly basis;


• Because it is a developing project model, the lease-up forecast considers that there is no or limited revenue during the construction phase, and the construction loan interests are capitalized.


•Dynamic construction budget: the construction expenses are modeled following an s-curve (normal distribution), meaning that construction expenses can be projected in a way that you have few expenses in the beginning, and then they quickly ramp up to your full project costs (depending on the standard deviation introduced);


Capital Stack: Development debt financing is drawn and repaid as follows: 


•Debt funds are drawn after equity has fully funded its share of eligible costs. 


•The Mezzanine Loan funds after all equity Loan contributions have been made and before the construction loan. It is assumed that any Mezzanine Loan cash interest is paid monthly with the positive operating cash flow. However, if the operational cash flow is insufficient, the interest cash is capitalized together with the PIK interest. The Mezz loan balance is repaid with permanent debt issuance.



•The Construction Loan funds after Mezz debt and equity contributions have been exhausted. The Construction Loan interest is either capitalized or paid with operating positive cash flow after paying the Mezz loan cash interest. 


•In the event of conversion of the construction loan into permanent debt, the Construction and Mezzanine Loan balances are repaid in full by the permanent loan. 


•If the permanent loan is insufficient to pay off all outstanding debt, you are notified with a visual alert message on the checks Sheet. 


•If no refinance exists, the permanent loan is repaid from net sale proceeds. Equity must be drawn down to pay the shortfall if net sales proceeds are insufficient to repay the debt balance.


•Equity Waterfall Model: the template provides a 3 tier Equity waterfall model, so the user will be able to determine how much capital the limited partner and General partner will get and their respective rates of return;


•Scenarios: The scenarios modeling sheets provide three possible scenarios based on the Rent, Vacancy, Construction Costs, exit Cap rate variance, etc. The base scenario is defined by default with the inputs set on the Investment Summary and the Assumptions sheets. 
•Macros: The model has two built-in macros, one for scenario generation and another to copy the Carry Cost (capitalized Interest, Loans Fees, operating shortfall during construction period) to reduce the risk of circularity 


Instructions 
• The user only needs to input information into the cells formatted in the dark blue font in the Investment Summary and Assumptions sheets. If the contents of a cell are colored black, it means that it is a formula. 


•The template is provided with information from a hypothetical property for demonstration purposes that must be erased for a real property valuation; 


•The investment summary sheet provides an overview of the property based on several key metrics and assumptions.


•In the Assumptions sheet, the user can define the remaining drivers; 


•The model is designed to capitalize the NOI of the 12 months after that of the disposition divided by the defined exit cap rate to arrive at the sell value;


• The maximum horizon for this model is ten (10) years; 


• For Revenue, the user must provide the current rents, the type, quantity, and square foot of the different units; other types of incomes per unit, and an annual growth rate per year, applied both to the rents and additional revenues; 


• For Construction Budget, the user needs to set the budget for different categories in the assumptions sheet.

This Best Practice includes
1 Excel file