As promised, I’ve recorded a tutorial on how to use my construction draw and interest calculation model. The 20 minute tutorial walks you through how to use the model to forecast construction cash flows during your development period. The model calculates construction loan interest, forecasts the distribution of sources and uses throughout construction, projects an equity and debt draw schedule, and visualizes the outcomes in two column graphs and a pie chart.
Note: Since recording this tutorial, I’ve updated the model. The latest version includes alerts regarding budget cash flow timing as well as a dynamic Gantt chart. It also fixes an error on the Budget tab in how the subtotal items’ Lengths were calculated and an issue involving the S-Curve calc date header. If you find any more errors, please let me know.
Because I chose not to use iterative calculation when figuring loan interest, I show you instead how to use Goal Seek with the model. The Goal Seek method allows you to properly calculate the total equity and debt required; which in turn calculates an interest reserve for the construction period. While this method is not fully dynamic, I believe it is preferable to dealing with a circular reference when calculating construction interest.
The model does not include lease-up cash flow, but does give you the option to calculate interest beyond the end of actual construction. This model should be easy to integrate with a typical acquisition model to create a pretty sophisticated development model. I think this might be the next project I work on. If you have any other requests, comments, or questions, feel free to get in touch.