I’ve been meaning to include this equity/construction loan draw and interest calculation tool in our library of real estate financial analysis models, and finally got around to uploading it today. I built this awhile back at a time when I was working to improve my cash flow modeling skills. Complex cash flow tracking, as is often the case when modeling development cash flows or equity waterfalls, is a learned skill best acquired through practice and repetition (like most things!). So I chugged away for quite some time at this basic model to practice, and it has since turned into a viable analysis tool.
Since completing the first iteration of the model earlier this year, I’ve cleaned up a few errors, added more budget line items, included a few graphs to visualize how the sources and uses flow over the construction period, and dropped in s-curve forecasting capability – a technique I blogged about a few weeks back.
Using the Construction Draw and Interest Calculation Model
The model includes a sources and uses tab and budget tab for inputs. It also includes three calculation tabs: an interest calc tab, s-curve calc tab, and raw data tab for data validation lists. The construction interest calculation avoids a circular reference, which is pretty cool. And I used dynamic named ranges, another topic I wrote about a few posts ago, to make the graphs dynamic to changes in construction length.
Check out the expanded video tutorial I created for this model.
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.
Construction Draw and Interest Calculation Model + Gantt Chart
- Sources and uses
- 50 budget line-items
- Construction interest calculation (without Excel circular reference)
- S-curve forecasting module included (Columns EG:GV of Budget tab)
- Construction period up to 60 months
- NEW: now includes a Gantt chart tab that automatically visualizes the construction schedule by budget item
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.
Updates to the Model
UPDATE: Version 1.2 includes budget timing alerts and a fix in how Length was calculated in column F of the Budget tab. Also fine-tuned the error check to hopefully eliminate a few false error alerts that were occurring.
UPDATE: Version 1.31: added calculation for construction loan fees (origination fee, points, etc), and made color scheme consistent across charts.
UPDATE: Version 2.0: added a Gantt chart tab that automatically visualizes the construction timeline
UPDATE: Version 2.2: moved the S-Curve calculations to columns EG:GV of the Budget tab to make it easier to add/delete budget rows
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.