One of the reasons Mike and I blog, is to force ourselves to be continually learning. I built the first version of my real estate waterfall model, because I wanted to better understand the mechanics of modeling an equity waterfall with IRR hurdles. Mike started his Understanding Leases Series, because there were nuances of modeling complicated (and less complicated) lease provisions that he wanted to understand better. Most of the ideas for my posts come from a curiosity to understand the why and how behind the things we do in commercial real estate. One concept I’ve wanted to explore deeper, is the concept of using the s-curve (sigmoid growth curve) to forecast development/project cash flows. Now I’d seen this used professionally, but had never built a model with an s-curve before and didn’t entirely understand the formulas behind this method of distributing cash flows. So I set out to build a basic real estate development cash flow model that incorporated this concept.
You might also check out my Construction Draw and Interest Calculation Model
What is the S-Curve Method?
If you’re unfamiliar with modeling development cash flows using an s-curve, the concept is simple. Most project costs are not spent linearly (or straight-line). You typically start by spending less per period in the earlier months while you’re ramping up construction, spend more per period in the middle months when construction is humming along, and then less per period in the latter months as construction winds down. The result, if you graph the cumulative cash outflows, looks like an ‘S’ tipped on its side. And interestingly enough, if you graph the cash flows by period, the resulting graph is a normal distribution (think back to your statistics courses in college).
Knowing what I wanted, but not entirely sure of how to accomplish it, I opened up Excel and plunked out a basic layout with inputs. The inputs would be 1) Budget Item, 2) Amount Per Item, 3) Cash Flow Distribution Method (Straight-line, S-Curve, and Manual Input), 4) Start Month (when cash flows for each budget item begin), and 5) Length (how many months cash flows will occur for each budget item). I would also later add an Analysis Start Date and an “S-Curve Distribution” input to change the steepness (i.e. standard deviation) of the normal distribution curve. I then laid out the cash flow periods, and wrote conditional formatting formulas and logic statements to model the “Straight-Line” and “Manual Input” options for the “Cash Flow Distribution Method.”
With the easy part done, I turned to the S-Curve formula. I had a few ideas of how to do this, but as I often do and to avoid reinventing the wheel, I opened up a browser window and went to Google. I wasn’t entirely sure what to look for, so I began searching different keywords and phrases related to s-curve, real estate, and development. I found an Excel add-in that models an S-curve, numerous academic discussions on the sigmoid growth curve, and a few forums where people were asking how to do exactly what I wanted to do but with few satisfactory responses. Then, I came across a post from Ben Bulloch on MrExcel.com. Ben had come up with a very good normally-distributed s-curve generation formula for spreading project costs over a number of months – it was exactly what I was looking for.
Watch the Tutorial and Download the Excel Model
With a few tweaks, I was able to incorporate Ben’s formula and thus, complete my basic development budget cash flow distribution model using the s-curve method. I’ve recorded a video that guides you through using the model, as well as how to expand the number of budget items to fit your specific project. You’ll find below, a link to download the model for yourself.
Download The 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 models sell for $100 - $300 each). 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.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
S-Curve Forecasting in Real Estate Development
- Forecast real estate project cash flows across a number of periods using three methods:
- Manual Input
- Straight-Line Method
- S-Curve Method
- Not a standalone model, but can be easily incorporated into your real estate development model
- Add unlimited budget line items
- Dynamic S-Curve distribution functionality, meaning easily change the steepness of your s-curve
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
About the Author: Born and raised in the Northwest United States, Spencer Burton has over 15 years of real estate investment and development experience. In his current position, Spencer assesses new acquisition, development, and debt opportunities for a $45bn real estate fund. He resides in Dallas, TX.