Condominium Development Model (Updated April 2023)

After posting the construction draw module that demonstrates how to calculate the true LTC of a development loan, I decided that since we have yet to publish a condo development model, to build upon that module to create one.

This condominium model has a lot of really great functionality that will allow the user to get a solid understanding of the financial feasibility of their for-sale condominium project. The goal, as with all my models, was to make it powerful and comprehensive, while being as simple and user friendly as possible. The model includes six, easy to navigate tabs: 1) Summary, 2) Inputs, 3) Budget, 4) Cash Flow, 5) Custom Cash Flow, and 6) a Waterfall tab.

What’s In The Condo Model and How to Use It – A Brief Tab By Tab Overview

1) The Summary Tab

The summary tab contains all of the high level relevant information for review and is designed to be easy to read, print, and save to pdf. It includes general info, a sources and uses summary, square footage and unit info, timing, dollar per sellable square foot analysis, and unlevered, levered, and partnership level returns.

What the User Needs to Do in This Tab

Input name, address, and project start date, lot size, project start date, and loan assumptions.

2) The Inputs Tab

The Inputs tab contains workboxes for 1) timing of sales, 2) unit programming, 3) a stacking plan, and 4) pre-sales expense calculations for calculating tax payments and HOA fees before sellout.

3) The Budget Tab

The budget tab gives you the ability to create up to six  main budget categories with 27 sub line items each. As you fill out the Budget Inputs section, the budget to the left will auto populate and auto-format and the main budget categories will shift up or down within the budget as line items are added. What’s great about this is that new items can be added to the budget under any category at any time and the user will not need to continuously reformat the page when they remember a budget line item they need to include.

4) The Cash Flow Tab (‘CF’ in the model)

This tab contains the construction cash flow, unlevered cash flow, levered cash flow, and the equity and debt draw schedule. This tab is where you will also control the timing of the cash flow for each budget sub line item.

What the User Needs To Do In This Tab

On the left hand side between rows 7 and 186 you will see each budget category group has been grouped and hidden. Open each section to see the line items and set the timing and methodology for the cash flow. Please see both videos below for more thorough instructions and a demonstration of the functionality.

5) Custom Cash Flow (‘CustCF’)

When you open the budget groups in the CF tab and select ‘Custom’ for a line item, come to this tab, open up the category group using the box with the ‘+’ sign in it on the left and look for the row highlighted in green. Fill in the custom cash flow to the right in the blue field. Guts of the model In many of my models posted on the site, the columns and rows that hold the mechanics of the model or are blank are hidden for aesthetic purposes. If you would like to see these areas to gain an understanding of how things are working in the background, you can simply unhide the cells to review. If you don’t know how to do this, you can click on the light gray arrow in the top left between the column A and row 1 labels or press Ctrl+A and go to Home>Cells>Format>Hide&Unhide>Unhide Rows. Repeat and Unhide Columns.

6) Waterfall Module

Click the header above for the separate page that reviews this tab.

Videos

Initial Summary of the Condo Model

 

Version 1.5 Updated Model – Review of Major Changes

Version 2.0 Updated Model – Review of Changes

Download the Condominium Development Model

To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical real estate development models sell for $100 – $300+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.

We regularly update the model. Paid contributors to the model receive a new download link via email each time the model is updated.

---

Version Notes

beta v2.0

• Major Summary tab overhaul and redesign
• Moved construction debt assumptions and summary section from the Inputs tab to the Summary tab
• Added Recalibrate Loan notifications on all tabs

beta v1.86

• Added a recalibrate button above the Financing Assumptions on the Input tab for the loan so when you change assumptions you can just hit a button to recalculate to the proper LTC rather than having to use Solver or Goal Seek
• Added conditional formatting with warning messages on various worksheets to notify the user if they need to recalibrate the loan
• Moved Inputs tab next to Summary tab

beta v1.85

• Added an additional option to have construction costs follow a steadily decreasing disbursement over time. The dropdown in column K now has a
  Steady Decrease option.
• Error fix in tier 2 waterfall tab for cash flow available to GP and dates in CF tab

beta v1.8

• Minor formatting updates
• Error fix on dates in header in CF and Waterfall tab

beta v1.7

• Added annual cash flow tab
• Text corrections in loan calculation instructions
• Error fix on cumulative debt balance formula

beta v1.6a

• Additional 2 years added to the model so you can now underwrote out to five years
• Added a Year header to the CF tab, Waterfall tab, and CustCF tab
• Update to accrued interest formula in Waterfall tab
• Created conditional formatting so that the Sell Out Date (Cell E14 on the Inputs tab) turns red if the sell out date surpassed the 5 year hold period
• Minor formatting updates

beta v1.5

• Overall Aesthetic Update
• Summary Tab
  •  Sources/Uses can now be analyzed on a per unit, per GSF, and per SSF basis.
  • Cost revenue analysis on a per unit, per GSF, and per SSF basis added.
• Inputs Tab
  • Manually fill in unit type within stacking plan to auto calculate in summary.
  • Date of Closing Presales now links to end of construction.
  • Updated interest projection so that you can now copy in a benchmark curve projection (e.g. LIBOR) in the CF tab and simply put in the basis  point spread in this section.
• Budget Tab
  • Added %, GSF, SSF review for each line item.
• CF Tab
  • Revamped, simplified, and cleaned up.
  • Ability to project each line item using a bell curve, steady growth, and custom growth scenarios.
  • Updated interest projection so that you can now copy in a benchmark curve projection (e.g. LIBOR) in the CF tab and simply put in the basis  point spread in this section.
  • Fixed error where bell curve forecast projection was inverted. Selecting a Steep curve was giving a Flat curve and vice versa.

beta v1 to 1b.6

• Initial model launch

---