There are all kinds of methodologies and styles I have seen in my financial modeling career for forecasting the use of a construction loan or debt to help get a real estate deal funded. What I have built in the three templates below involves the best and most flexible configuration possible.
The three templates that all use this type of logic in order to get accurate monthly forecasted cash flows are:
- Mixed-Use Real Estate Model
- Hotel Acquisition / Development Model
- Assisted Living Facility Acquisition / Development Model
All three of those financial models have the ability to define monthly costs to build, acquire, or buy and renovate or construct. These costs will flow into a construction loan or p+i loan if the option to use debt is selected as yes on the control tab. The old logic took any negative cash flows up to a defined month and had that flow into an interest only loan with a single defined percentage that was used to determine how much of those negative cash flows were subject to financing.
The new logic simply lets the user select a given 'yes' or 'no' option for every single line item in the development / acquisition cost schedule tab. Also, there is an input for each row for the percentage of each cost that is going to be financed.
If you are not using debt at all in the forecast, you can still turn it all off simply by selecting 'no' on the main control tab and no costs will be financed.
I also had a few updates on the Hotel and Assisted Living Facility models for staffing costs and general operating costs so that the start period is more dynamic. The mixed-use template uses a different type of flow to defined operating costs so it didn't need to be updated.
After working with a few users who purchased these templates, I really felt that this new logic was required. For example, if you had a situation where you were purchasing 100% of the land and then financing the development costs at some Loan-to-Cost rate, there was no easy way to toggle that on and off. You would manually have to adjust the data and it can get messy. The new style is much more flexible and can easily handle situations like that as well as anything it was able to do before.
All of these real estate models have an IRR hurdle-based waterfall connected with GP/LP style inputs.
Enjoy!
