What’s in a Real Estate Private Equity Case Study? Part 3: A Pre-Sold Condo Development in São Paulo
Would you like to move to Brazil?
The beaches have tempted me before, but I’ve had some reservations about moving there… though I do want to visit at some point.
But some of those reservations went away when I saw the sketches for V:House, a high-end luxury residence under development in São Paulo:
I liked it so much that I was inspired to create a real estate private equity case study based on this development, using data from a WSJ article and other sources.
This one uses elements from the previous cases and mixes in some brand-new concepts – such as how to model cash flows and returns when development and unit sales are taking place simultaneously:
The Video, the Excel and Solution Files, and More
Table of Contents:
- 2:41: Part 1: The Types of RE PE Case Studies, and Where This One Fits In
- 4:30: Part 2: This Case Study and What Makes It Tricky
- 10:47: Part 3: Doing the Quick Math on This Deal
- 13:12: Part 4: The Assumptions and Construction Timeline
- 20:12: Part 5: Expenses, Equity and Debt Draws, the Waterfall, and Sensitivities
- 27:08: Part 6: The Investment Recommendation
- 29:13: Recap and Summary
And in text form:
What Makes This Case Study Different?
I mentioned before that there are three types of real estate investment strategies, each of which corresponds to a type of case study you might receive:
- Core or Core-Plus:The property exists. It’s stabilized. You acquire it. You don’t change it, or you change it very little. Then you sell it. Low risk, and low potential returns (5-10%).
- Value-Added:The property exists, but it’s not “stabilized” because of a low occupancy rate, below-market rent, or a sorely needed renovation. You buy the property, spend money to improve it, and sell it. Higher risk, but also higher potential/targeted returns (10-15%).
- Opportunistic:The property doesn’t exist, or it does exist but will be redeveloped. You’re building something brand-new, which may result in GREAT SUCCESS… or catastrophic failure. This category offers the highest risk, but also the highest potential returns (15-20%+).
This property is in category #3 because it doesn’t exist yet.
However, it’s also in a special sub-category within category #3: pre-sold developments.
So you don’t draw on equity and debt, develop the property, and hope to sell it for a good price when it’s done… you sell the units in advance and then fund the construction partially with upfront deposits from the new owners.
This approach means that the standard “Cap Rate and NOI” method for determining the exit proceeds no longer applies.
Instead, the calculation is much simpler: you assume a selling price per square meter, and then you do a weighted multiplication to sum up the values of all units sold.
So that part is simpler, but a few points also make this trickier than it appears:
- Problem #1: Timing of Unit Sales – You can’t sell all the units upfront all at once. You tend to sell units in phases, and after a certain percentage of units are sold, construction begins, you start selling units in the next phase, construction there begins, and so on.
- Problem #2: Variable Debt and Equity – With an existing property, you pay a lump sum and assume a certain Loan-to-Value (LTV) ratio. But you can’t do that here because you’re not allowed to draw on debt until a certain percentage (40%) of units have been pre-sold – which means that the debt and equity will differ based on the timing of pre-sales.
- Problem #3: Multiple Payment Phases – When condos are pre-sold, typically there’s an upfront deposit and then a final deposit when the construction is complete. To make things even more fun, though, I added in intermittent payments (“construction-phase deposits”) that must also be paid as the construction is taking place.
The result is that the construction timeline becomes a lot more complex:
You have to track multiple phases, multiple deposits, different equity and debt draws at different times, and more.
And then the IRR and multiples calculations and waterfall schedule must still work properly.
Developers and investors go through the extra effort of pre-sales to mitigate risk.
Think about how risky it is to develop a property and then hope that you sell the entire thing, or all the units individually, at the end:
- What if construction takes longer than expected?
- What if no one’s interested in the area?
- What if the economy crashes?
- What if construction costs skyrocket, or home prices suddenly fall?
Starting construction only after a certain percentage of units are pre-sold lets developers and investors gauge interest and adjust their plans as market conditions change.
It’s similar to how tech start-ups might release a “minimum viable product” (MVP) at first, and then refine it after getting feedback from paying customers.
How to Avoid Putting the Cart Before the Horse: Doing the Quick Math
Before jumping into Excel in any case study, you should always do some quick math first to see if the deal is feasible.
If the back-of-the-envelope math does not work, I guarantee 100% that a more complex model will also not work – and you will have wasted hours or days building such a model.
We can do a quick check of the unleveraged cash-on-cash multiples by adding up the total selling prices for all the units and then subtracting the land acquisition costs, hard costs, and developer fees and soft costs:
Note that you have to “back into” the Hard Costs by calculating the rentable square meters for the property, using that to get the gross square meters, and then using that 49,000 sq. m. figure in the calculations:
From this simple math, you can tell that the deal is unlikely to work.
Even in the most optimistic cases, the unleveraged multiple is only 1.2x, but we’re targeting 2.0x per the instructions:
Using leverage will boost this multiple, but leverage alone won’t boost it to a 2.0x multiple in the Base Case – especially since 40% of the total units must be pre-sold before we can even draw on debt:
This math won’t end up being exactly right because we haven’t factored in inflation – which is quite significant in Brazil.
But given the time frame – only a few years – and this quick analysis above, we would certainly lean against the deal before doing any real work in Excel.
Part 1: Assumptions and Operating/Market Scenarios
You cannot do much with a pre-sold development in this step because:
- You can’t create a Sources & Uses schedule since the amount of debt and equity drawn will depend on how quickly the property is constructed and how quickly the units are pre-sold.
- There aren’t extensive “scenarios” in the same way there were for the first and second case study, because only a few numbers change here.
We do make one significant change: it does not make sense how the Hard Costs would be highest in the Downside case and lowest in the Upside case, so we tweak those numbers:
When home prices rise, everything else – construction costs, land costs, fees, etc. – will also tend to increase, and vice versa when prices drop. So it’s more reasonable to flip the order.
We also reduce the overall Hard Costs per square meter figure because the numbers, as provided, don’t make sense:
- Using the provided figures, the Total Hard Costs exceed the construction costs of Parque Global, an even bigger and higher-end property.
- And they also come out far ahead of the R$ 175 million quoted in the WSJ article.
These new figures put us a bit closer to both of those.
Part 2: Construction Timeline and Monthly Sales
This part is the trickiest section of this case study.
After inputting the # of units to be sold and the average unit size in each phase, you should create an area that tracks the month #, year #, % of total building units sold, and total monthly units sold:
Then, you can create an area that tracks the monthly units sold, cumulative units and percentage sold in each phase, and total value of units sold:
We use the NUMBERVALUE function here extensively, but if you’re using an older version of Excel that doesn’t support it, check out the version of the Excel file that skips NUMBERVALUE:
NUMBERVALUE converts text to a number such that “1” becomes 1 while “” becomes 0, so it’s incredibly useful for schedules where there might be blank cells.
The first “Monthly Units Sold” formula is:
- =IF(F51=””,””, MIN(Apt_Sales_per_Month, Phase_1_Units-NUMBERVALUE(E57)))
If we’re before the start of the timeline, we set the monthly sales to a blank (“”), but if we’re in the first month or beyond, we set this equal to the lower of the planned unit sales per month or all the remaining units to be sold in this phase.
So if the phase has 50 units, we’ve already sold 42, and 6 units are pre-sold each month, we’ll –pre-sell 6 in this month; but if we’ve already sold 47 units, we’ll only pre-sell 3.
Once you finish this for Phase I, you have to go back and use a COUNTIF function to see how long the pre-construction phase takes – 60% of the units in each phase must be pre-sold before construction can begin:
And then once you have this, you can determine the start month for Phase 2. And then you can repeat the same process for Phases 2 and 3.
Once this is done, you have to split up each phase’s sales by Initial Deposits, Construction-Phase Deposits, and Final Deposits.
Initial Deposits are easy: check if you’re in or beyond the start month but before the end of construction, and if so, multiply the monthly sales by the upfront payment percentage (30%).
For the Final Deposits, check if you’re in the construction end month, and if you are, take the total value of units sold in the phase and subtract the cumulative initial and construction-phase deposits up to this point.
Construction-Phase Deposits are tricky to get right, at least if you want to ensure that new owners pay exactly 30% of the total value of condos sold in this phase.
To reduce the complexity, we simplify it and take the cumulative sum of the values of units sold so far, multiply by 30%, and divide by the number of construction months (24) to get the monthly figure:
Food for Thought: If you don’t immediately understand why this method throws off the numbers, think about it and explain the issue. Also explain why it would be difficult to write a single, clean Excel formula to fix this problem.
You repeat this process for all the phases, referencing different assumptions and rows each time, and sum up everything at the bottom.
Part 3: Expenses and Equity and Debt Draws
Expenses are easy to project in this case study: see if construction is taking place, and if so, divide the Total Hard Costs by the Total Number of Construction Months and do a straight-line allocation over that period.
Developer Fees and Soft Costs are both percentages of monthly sales.
For the Land Acquisition Costs, you need to check to see if you’re in the month of the initial deposit or the month of the final deposit, and use the correct numbers if you are.
The formulas for the Equity & Debt Schedule look intimidating at first, but they are not that complicated if you break them down:
The first part checks to see if Gross Income is negative and if we cannot yet draw on debt – if both those conditions are true, then we draw on equity to fund the property this month.
Since the Developers and Investors split the funding 20/80, the -F$108*(1-Tier_1_IRR_Investors) part corresponds to the amount of funding required * 20%.
The part at the end, +IF(OR(F$51=Ph3_Constr_End, F$51=Final_Month),MAX(E$131+F$118-F$110,0),0)*(1-Tier_1_IRR_Investors), is a “catch-all” that checks to see if we’re in the final month of construction, or the final month of the model (as in, the final month shown in Excel).
If we are, then we need to repay all remaining, outstanding debt.
The MAX(E$131+F$118-F$110,0),0) part takes the previous month’s Construction Loan balance, adds interest accrued this month, and subtracts positive cash flow or adds the cash flow deficit this month.
If that number is positive, it means there’s a remaining debt balance we must repay with additional equity; if it’s negative or 0, we don’t need to draw on additional equity.
Remember that unlike in a traditional acquisition and sale, you can’t just use the “exit proceeds” to repay debt – there are no exit proceeds!
If you end up past the end of the pre-sales period and still have outstanding debt, you must draw on equity to repay it – there is no NOI or cash flow, so the principal must be repaid with equity.
The rest of this schedule is straightforward: calculate monthly interest, but make it accrue to the loan principal if monthly gross income is negative; if there’s positive cash flow, use it to repay debt principal.
Part 4: Waterfall Returns Schedule
I covered the structure of the waterfall returns schedule in the second RE PE case study, and it’s largely the same here.
Track how much equity the investors and developers are injecting, increase it by the monthly percentage that corresponds to an annual 20% return in the first IRR tier, and then subtract any distributions for what investors are owed in this tier.
And then you split up the cash flow between investors and developers 80/20, 70/30, and 60/40 depending on the tier.
The “Accrual Distributions” should correspond only to the IRR in the tier you’re in – so if you’re in the 20-25% tier, the Tier 2 Accrual Distribution should represent only the amount of proceeds that correspond to the IRR between 20% and 25%.
Anything less than or equal to 20% is represented by the Tier 1 Accrual Distribution, and anything above 25% is represented by the Tier 3 Accrual Distribution.
At the bottom, you sum these up and calculate the unleveraged and leveraged IRR and multiples, and the IRR and multiples to the investors and developers.
Part 5: Summaries, Sensitivities, and Case Study Presentation
A monthly model like this one is unwieldy to review and print out, so you should always create an annual summary and a transaction summary that explain the main points at a glance.
On our Summary tab, we show the Sources & Uses schedule (which can be completed only after the entire model is done), the returns, and the construction timeline and building profile:
For the sensitivities, you should use the most impactful assumptions: the selling price per square meter, the lot price per square meter, the # of units pre-sold each month, and the Hard Costs per square meter.
We also include the # of construction months in each phase, though that ends up not mattering much.
In my opinion, this is a “No” because it’s too hard – in any case – to achieve the targeted 2x cash-on-cash multiple.
We do get there in the Upside case, but not the Base case; and the Downside case is admittedly closer to the 1.2x target.
There are also other problems: lack of good market data, results that are incredibly sensitive to small changes in Hard Costs, and an inability to mitigate key risks.
We might change our mind if:
- The construction time were faster – The Hard Costs would matter less if the entire process took only 3-4 years across all the different cases.
- We could lock in the Hard Costs at R$ 4,000 / sq. m. – In this case, we’d only lose money if unit selling prices declined substantially.
- We could guarantee higher unit selling prices – For example, if units always sold for R$ 9,000 / sq. m., the deal would still work even if Hard Costs rose by more than expected.
To be fair, this is a more “borderline” deal than the first two case studies.
If the multiples had been, say, 2.2x in the Base Case, 3.0x in the Upside Case, and 1.5x in the Downside Case, I probably would have said, “Yes.”
But the results aren’t that good, and there are plenty of risks we have a limited ability to mitigate, so we take the opposite view here.
That brings us to the end of this third case study, and we’re done with real estate private equity for now.
To get the full, step-by-step tutorials, sign up for the Real Estate Financial Modeling course.
There are also sample videos from the other RE PE case studies on that page.
There will be two new RE case studies on a hotel renovation in Dubai and a mixed office/retail development in London, but those are a long way away (middle-to-late next year). I do hope to share samples when they’re available.
In coming months, I’m planning to share samples of recent and upcoming new courses, including ones on private company valuation, PowerPoint tricks, ECM/DCM analysis, and more.
Other Articles in This Series of Real Estate Private Equity Case Studies:
Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews
Read below or Add a comment