Real Estate Financial Modeling Tools

Professional Version: Mixed-Use Office Building/Industrial Building Ground-Up Development Pro-Forma Template

$1,499.00

-

An Excel-based analysis tool for the ground-up development, operation and eventual disposition of a single- or multi-tenant commercial office building with or without ground-floor retail and with or without income-producing parking, or for the ground-up development, operation and eventual disposition of a single- or multi-tenant industrial building. The model is monthly in nature, and it is a 100% unlocked Excel file with fully transparent formulas that can be further tailored to suit the particulars of your transactions.

User-Friendly Features

  • Compatible with both PC and Mac
  • Includes instructions for use, with detailed annotations tied to select cells
  • Two-page assumptions input and reporting screen optimized for fast inputting and dashboard-style viewing
  • Persistent calculation checks and data validation protections to prevent faulty inputs
  • Hyperlink navigation enables fast, intuitive access to all tabs
  • Institutional-quality print-ready reporting tabs that are brandable to your company
  • S-Curve distribution of Soft Costs by individual line item
  • No circular references impacting the calculation of the construction loan size and funded interest
  • Includes copy/paste tab for “next buyer” analysis to be housed and presented in REFM’s Valuate® web-based software

Functional Highlights

The model supports the following:

  • Up to five office/industrial tenants with escalations by % and/or step, and free rent
  • Up to three retail tenants with percentage rent, escalations by % and/or step, and free rent
  • TIs paid according to bell-shaped curve allocations, LCs paid in multiple installments
  • Valuation and disposition of the retail component independently of the office component
  • Sponsor land equity, a land loan, and up to two layers of construction debt financing (Senior Construction debt with accruing Funded Interest Reserve, and Mezzanine debt with current cash payments), and an amortizing permanent takeout loan
  • Up to three joint venture equity partnership entities and up to two 5-tier lookback IRR-based waterfalls (i.e., a double-promote structure)
  • Gantt chart for project planning purposes.

Equity and Debt Financing Default Settings

The model is set up as follows:

  • Equity invests first in full. Within the equity, the Equity Sponsor invests in full, then Partner (if any), then Third Party Investor (if any).
  • Any operating and financing deficits are funded by Equity on a pro-rata basis
  • Any Mezzanine Loan funds prior to the Senior Construction Loan, has current cash interest payments, and is repaid from Operating Cash Flow, and if needed and applicable, from residual refinancing or sale proceeds after full repayment of the Senior Loan
  • Senior Construction Loan funds last and has Funded Fees and a Funded Interest Reserve (i.e., capitalized fees and interest) with an option to switch to monthly cash interest payments at a user-input milestone
  • Senior Construction Loan repays any outstanding Land Loan at the senior loan’s initial draw
  • Senior Construction Loan is repaid from refinancing proceeds from the Permanent Loan, or if there is no refinancing, from the net sales proceeds of the office building asset and retail asset dispositions
  • Permanent Loan is sized based on the lesser of three tests: Loan to Value, Debt Service Coverage Ratio, and Debt Yield. Given the Maximum Loan Size is sufficient, distribution of excess loan proceeds to equity can be modeled.

Modification to the above is possible either by you or by REFM on a consulting basis.

Joint Venture Equity Partnership Cash Flow Waterfall Default Settings

  • The JV equity partnership structure reflects monthly compounding, cumulative IRR hurdle-based waterfalls in which the Tier 1 Preferred Return is distributed first pro-rata simultaneously (i.e., pari passu), then capital is returned pari passu
  • Promote %s are amounts above and beyond the pro-rata invested dollars share of each party
  • Waterfall #1’s hurdles are based off of the performance of the Third Party Investor’s invested dollars
  • Waterfall #2’s hurdles are based off of the performance of the Developer Partner’s invested dollars.

Modification to the above is possible either by you or by REFM on a consulting basis.

Model Tabs

1    Back of the Envelope Model
2    Assumptions Input
3    Capital Structure Exhibit
4    Stacking Plan
5    Office Tenant 1 Inputs
6    Office Tenant 2 Inputs
7    Office Tenant 3 Inputs
8    Office Tenant 4 Inputs
9    Office Tenant 5 Inputs
10  Retail Tenant 1 Inputs
11  Retail Tenant 2 Inputs
12  Retail Tenant 3 Inputs
13  Master Leasing Tab Exhibit
14  Sources & Uses of Funds Monthly Exhibit
15  Cash Flow Monthly Exhibit
16  Public Parking Income Exhibit
17  Sources & Uses Annual Exhibit
18  Source & Uses Quarterly Exhibit
19  Cash Flow Annual Exhibit
20  Cash Flow Quarterly Exhibit
21  Transaction Summary Exhibit
22  Partnership Structure Exhibit
23  Waterfall #1 Exhibit
24  Waterfall #2 Exhibit
25  JV Returns Summary Exhibit – 3 Players
26  JV Returns Summary Exhibit – 2 Players
27  Land Acquisition Costs Schedule
28  Soft Costs Budget Schedule
29  Hard Costs Budget Schedule
30  Gantt Chart
31  Office TI & LC Amortization Schedule
32  Retail TI & LC Amortization Schedule
33  Permanent Loan Amortization Schedule
34  Construction Bell-shaped Curve Lookup Table

Model Timeline

15 years (expandable)

Reports

Print-ready, brandable reports are included in the model. See the Sample Reports link above.

License Type And Multiple-User Pricing

Licenses are sold on a per-user basis
Multi-user pricing quotes are available here

Included With Purchase

  • Blank template (inputs are all set to zero) and a version of the template with a sample deal loaded in
  • Standard version of the template for crisp presentation of less complex deals
  • 1 year of online ticket-based support

SPECIAL BONUS ($179 value)

Equity Joint Venture Partnership and Waterfall Modeling Bootcamp Video Tutorial included with purchase

This video tutorial teaches the conceptual foundations and detailed inner workings of the double-promote waterfall structure contained in the template.

Includes the following items, which never expire:

 Online access to an easily navigated 80-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets

 PDF of the 60-slide presentation

 Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, with the following tabs:

  1. Preferred Return Non-Compounded Annual
  2. Preferred Return Non-Compounded Annual Solution
  3. Preferred Return Compounded Monthly
  4. Preferred Return Compounded Monthly Solution
  5. 3-Tier Annual Waterfall with Look-Back
  6. Double-Promote Profit Sharing
  7. Double-Promote Monthly Waterfall # 1 with Look-Back
  8. Exercise – Rebuild Tier #3
  9. Double-Promote Monthly Waterfall #2 with Look-Back
  10. Double Promote Returns Summary Exhibit
  11. Alternate Compounding Periods
  12. Sample Partnership Structure 1
  13. Sample Partnership Structure 2
  14. Sample Partnership Structure 3
  15. Claw-Back.
Don’t need so much horsepower?
Support Level

Standard Support, Priority Support

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