Overview and Video Tour

The Multi-Year B.O.T.E. Excel tool is suited for rapid apartment building development feasibility and residual land valuation based not only on a stabilized yield on cost, but also on goal-seeking to a targeted before-tax IRR, net profit, multiple on equity or NPV metric. Includes an optional refinancing after stabilization.

Functions

• Compatible with Excel on PC, Mac, iPad and Android tablets
• Supports an 11-year timeline of development and operation
• Allows for back-solving to a residual land value based on:
  • stabilized cap rate
  • net profit
  • IRR
  • multiple on equity
  • average cash on cash
  • NPV
• Streamlined set of assumptions inputs
• Units-based and FAR-based analysis types
• Supports a NNN retail component, sold with the apartments at a single cap rate
• Sale valuation based off of forward or trailing 12 months values

Features

• Charts for:
  • development equity and debt capitalization
  • annual development costs
  • annual NOI yield on cost
  • annual NOI
  • annual cash on cash
  • equity and debt capitalization at sale
• IRR Sensitivity Analysis data tables based on:
  • base building hard cost vs. exit cap
  • senior construction loan LTC vs. exit cap
  • land cost vs. base building hard cost.

Debt & Equity Capabilities

• Sponsor Equity
• Senior Construction Loan
• Refinancing upon stabilization with a Permanent Loan that supports an upfront interest-only period and a cash out refi

Reports

The analysis prints neatly on a single 8.5 x 11 page in landscape orientation.

Support

Support is available through our online ticket system.

Included With Purchase

• Blank templates for Units- and FAR-based analyses
• Copies of the templates with a sample deal loaded in
• Credit of what you pay towards future purchase of the Tools Bundle

Overview and Video Tour

The Standard Pro Forma is a robust Excel-based analysis tool for modeling the ground-up development, operation and sale of an apartment/multi-family rental building with or without ground-floor retail and income-producing parking. It is suited for detailed monthly-based analysis and it generates before-tax IRR, net profit, cash on cash, equity multiple and NPV outputs, among others.

Functions

• Compatible with Excel on both PC and Mac
• Supports a 10-year timeline for development, plus an additional 6 years of operation
• Separate unit mixes, rents and absorption for market and affordable rate units
• Dynamic construction hard cost allocation based off of a bell-shaped curve
• Retail tenant improvements (TIs) and leasing commissions (LCs)
• Option to value and dispose of the retail component independently
• Equity funding of operating and financing deficits on a pro-rata basis

Features

• Includes instructions for use, with detailed annotations tied to select cells
• Two-page assumptions input and reporting screen dashboard
• Hyperlink navigation for 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 construction loan sizing and interest

Debt Capabilities

• Senior construction loan
  • funds on a residual basis to all equity
  • fixed rate or floating interest only, with default funded interest reserve, and user ability to switch to cash interest payments at specified milestone
  • floating rate floor and cap
  • repaid from net sales proceeds of apartment building and retail unit dispositions, or from refinancing proceeds from Permanent Loan
• Permanent loan
  • sized by the lesser of three tests (LTV, DSCR, Debt Yield)
  • option to do a cash-out refi with distribution of excess proceeds to equity
  • fixed or floating rate (with floor and cap), with option for interest-only period up front

Equity Capabilities

• Equity positions supported
  • sponsor/developer
  • third party investor
• Equity investment options
  • pari passu (pro-rata, simultaneously)
  • sponsor first, then third party investor
• Equity profit sharing
  • Pari Passu Preferred Return (includes return of capital) with monthly compounding
  • Cumulative IRR hurdle based on the performance of the third party investor’s invested dollars
  • Residual profit split with sponsor promote share defined as share of residual profit above and beyond the sponsor deal ownership position share

Reports

• Assumptions and Project Returns
• Capital Structure
• Unit Mix
• Partnership Structure
• Annual Sources and Uses
• Annual Cash Flow
• JV Returns Summary

Quality Controls

• Persistent calculation checks and associated visual alerts, and data validation protections to prevent faulty inputs or faulty conclusions
  • project budget must match allocation of budget over timeline
  • senior loan must be repaid in full
  • all units must be leased
  • permanent loan must be sufficient to pay off senior loan
  • waterfall profit splits must sum to match the deal-level profit

Support and User Guide

Support is available through our online ticket system.

A comprehensive, searchable, hyperlinked PDF User Guide is included with purchase. A sample is below.

Included With Purchase

• Blank template (inputs are all set to zero)
• Copy of the template with a sample deal loaded in
• Searchable, hyperlinked PDF User Guide
• Credit of what you pay towards future purchase of the Tools Bundle

Workbook Tabs

1    Home Page
2    Assumptions Input
3    Capital Structure Exhibit
4    Apartment Unit Mix Input
5    Monthly Sources and Uses of Funds Input
6    Monthly Cash Flow Exhibit
7    Partnership Structure Exhibit
8    Waterfall Profit Splitting Exhibit
9    Annual Sources & Uses Exhibit
10  Annual Cash Flow Exhibit
11  Joint Venture Partnership Returns Summary Exhibit
12  Permanent Loan Amortization Schedule
13  Construction Loan Interest Rates
14  Monthly Construction Bell Curve Lookup Table
15  Land Acquisition Costs Schedule Input
16  Hard Costs Budget Schedule Input – Phase 1
17  Soft Costs Budget Schedule Input – Phase 2
18  Floating Rate Index

Overview and Video Tour

The Professional Pro Forma is a highly-flexible Excel-based analysis tool for modeling the ground-up development, operation and sale of an apartment/multi-family rental building with or without ground-floor retail and income-producing parking. It is suited for presentation to a wide array of debt and equity capital sources, including institutional players.

Features

• Includes instructions for use, with detailed annotations tied to select cells
• Two-page assumptions input and reporting screen dashboard
• Hyperlink navigation for 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 construction loan sizing and interest
• Ability to size senior construction loan by LTC, or by back-solving based on equity investment
• Detailed real estate tax adjustment mechanism for valuing asset sale

Debt Capabilities

• Land loan
  • fixed interest rate with current or accruing payments
  • repayment from senior construction loan
• Senior construction loan
  • funds on a residual basis to all equity and any mezzanine loan
  • fixed rate or floating interest only, with default funded interest reserve, and user ability to switch to cash interest payments at specified milestone
  • floating rate floor and cap
  • repaid from net sales proceeds of apartment building and retail unit dispositions, or from refinancing proceeds from Permanent Loan
• Mezzanine loan
  • fixed or floating interest-only rate, with option for carried interest
  • floor and cap for floating rate
  • repayment from either refinancing or sale
• Permanent loan
  • sized by the lesser of three tests (LTV, DSCR, Debt Yield)
  • option to do a cash-out refi with distribution of excess proceeds to equity
  • fixed or floating rate (with floor and cap), with option for interest-only period up front

Equity Capabilities

• Equity positions supported
  • sponsor/developer
  • developer partner
  • third party investor
• Equity investment options
  • all players pari passu (pro-rata, simultaneously)
  • sponsor first, then partner, then third party investor
  • sponsor and partner pari passu, then third party investor
• Equity profit sharing
  • Preferred Return (includes return of capital)
    • Participation: Sponsor can be included or excluded from the Pref. If the sponsor is not included in the Pref, then sponsor’s return of capital occurs only after the third party investor receives: a) all of their capital back, and b) their Pref profit.
    • Accrual options:
      • only based on monthly compounding, cumulative IRR hurdle based on the performance of the third party investor’s invested dollars
      • based on both third party investor IRR hurdle and third party investor equity multiple hurdle, where the larger resulting dollar amount governs which basis determines the distribution
  • Residual profit split with sponsor promote share defined as share of residual profit above and beyond the sponsor deal ownership position share.

Reports

• Assumptions and Project Returns
• Project Gantt Chart
• Stacking Plan
• Capital Structure
• Unit Mix
• Partnership Structure
• Annual and Quarterly Sources and Uses
• Annual and Quarterly Cash Flow
• JV Returns Summary for both 2 and 3 entities

Quality Controls

• Persistent calculation checks and associated visual alerts, and data validation protections to prevent faulty inputs or faulty conclusions
  • project budget must match allocation of budget over timeline
  • all loans must be repaid in full
  • all units must be leased
  • permanent loan is sufficient to pay off all outstanding debt
  • waterfall profit splits must sum to match the deal-level profit

Support and User Guide

Support is available through our online ticket system.

A comprehensive, searchable, hyperlinked PDF User Guide is included with purchase. A sample is below.

Included With Purchase

• Blank template (inputs are all set to zero)
• Copy of the template with a sample deal loaded in
• Searchable, hyperlinked PDF User Guide
• Credit of what you pay towards future purchase of the Tools Bundle

Workbook Tabs

1    Executive Summary
2    Assumptions Input
3    Building Stacking Plan Input
4    Apartment Unit Mix Input
5    Monthly Sources and Uses of Funds
6    Monthly Cash Flow Exhibit
7    Master Retail Leasing Exhibit
8    Retail Tenant 1 Exhibit
9    Retail Tenant 2 Exhibit
10  Retail Tenant 3 Exhibit
11  Retail Tenant 4 Exhibit
12  Retail Tenant 5 Exhibit
13  Annual Sources & Uses Exhibit
14  Quarterly Sources & Uses Exhibit
15  Annual Cash Flow Exhibit
16  Quarterly Cash Flow Exhibit
17  Capital Structure Exhibit
18  Joint Venture Partnership Structure Exhibit
19  Monthly Waterfall #1 Exhibit
20  Monthly Waterfall #2 Exhibit
21  Joint Venture Partnership Returns Summary Exhibit – 3 Equity Players
22  Joint Venture Partnership Returns Summary Exhibit – 2 Equity Players
23  Land Acquisition Costs Schedule Input
24  Soft Costs Budget Schedule Input – Phase 1
25  Soft Costs Budget Schedule Input – Phase 2
26  Hard Costs Budget Schedule Input – Phase 1
27  Hard Costs Budget Schedule Input – Phase 2
28  Monthly Retail TI and LC Amortization Schedule Exhibit
29  Monthly Permanent Loan Amortization Schedule Exhibit
30  Mezzanine Loan Interest Rates
31   Senior Construction Loan Interest Rates
32  Project Gantt Chart
33  Monthly Construction Cost Curves Lookup Table
34  Floating Rate Index