An important component in any acquisition, development, and value-add model is the Sources and Uses of Capital section. Of course, how the sources and uses are modeled and how those cash flows are visualized will vary from one model to the next. But no model is complete without an accurate estimation of the sources and uses of an investment.
If you’ve downloaded any of my more recent models (e.g. apartment development model, self storage acquisition model), you’re likely aware that I now have the discipline of building my models using bolt-on modules. This approach to modeling allows me to build the model faster and with fewer errors. And thus, my library is full of modules that I pull out when building a full model.
One particular module that I recently improved, but hadn’t yet shared with the A.CRE audience, is my Sources and Uses module And so while updating the module, I decided to add it to our A.CRE Library of Real Estate Models.
Note: If you’re an A.CRE Accelerator member, this module is generally included in the Investment Cash Flow section of the real estate DCF. Refer to lesson 2.4 of the Anatomy of the Real Estate DCF as well as lesson 2.10 of the Development Cash Flow course. Not yet a member of our Accelerator training program? Consider joining today.
What is a Sources and Uses of Capital module?
A Sources and Uses of Capital module is a section of a real estate model where the uses of capital (e.g. project costs, acquisition costs, etc) and the sources of capital (e.g. debt, equity, etc) are calculated. This module will generally include a Sources and Uses of Capital schedule that summarizes what those sources and uses of capital are. It will also include a forecast of the cash flow of sources and uses in each period.
So for instance in a development project, uses of capital would be things like land costs, hard costs, and soft costs. Those are summarized in a project budget. Additionally, the cash flow for each of those uses would be forecast over the development period. Since all uses of capital must have a source of capital to fund that use, the source of capital section will identify what source will cover the required use.
In most development deals, equity will first be deployed to cover early uses of capital. At a certain point, debt will replace equity as the source of capital deployed to cover the required uses going forward.
About My Real Estate Sources and Uses of Capital Module
I built this particular module so as to make it easy to insert a robust Sources and Uses module into any model. Personally, I use this module anytime I build a new value-add or development model. I simply drag the worksheet into the model, link the Operating Cash Flows and relevant deal assumptions to this module, and then link the outputs from this module to the real estate DCF, and done.
All of the elements of the module are housed on the same worksheet, the module does not include any named cells or ranges, nor does it include any macros. This makes copying the module into an existing or new model simple.
Each of the project cost line items are all on the same line. This makes adding/deleting cost line items simple. It also makes it possible to build a macro to automate the process of adding or deleting line items, as I include in most of my production-ready models.
How to Use the Real Estate Sources and Uses of Capital Module
If you’re familiar with our models and/or with real estate financial modeling, than using the model should be very straightforward. If not, the following written and video instructions will help you get started.
The module is broken into four sections:
- General model assumptions. These include inputs such as Analysis Start Date, Measurement Type, Units, etc. In all likelihood, you will move these to a different location in the model or replace them with existing inputs. But in the module file, these assumptions are placed at the top of the S&U worksheet.
- Uses of Capital. This section is sometimes called project budget in real estate models. This is where you will enter the amounts that are expected to be spent to acquire, renovate, or develop the project. In this section, you also forecast how those amounts will be spent. You can choose up to five methods for forecasting the cash flows, including Straight-Line, S-Curve, Steady Growth, Steady Decline, and Detail (i.e. custom).
- Sources of Capital. In this section, you will model debt and equity. This includes the allocation of debt to equity, the total amount, and how those amounts are deployed to match the uses of capital. You can model up to three levels of equity and two levels of debt. Since the model does not use circular references, you will need to manually (or via Goal Seek) adjust the Total Equity value in cell L50 until the target LTC (input cell E57) equals the actual LTC (cell E56). The model will alert you when the Total Equity value needs to be adjusted.
- Operating Cash Flow. As mentioned above, the model uses a non-circular method for calculating interest reserve and operating shortfall reserve. That logic requires pulling in the Cash Flow from Operations line (i.e. NOI ÷ CapEx = CFO) from the Operating Cash Flow section of your model to this module. The simplest way to integrate this element, is to simply link the CFO line in the Sources and Uses module with the CFO line in your Operating Cash Flow section.
In terms of the nuances of using the module, and especially in terms of handling interest and operating shortfall reserve without a circular reference, I’ve recorded a video tutorial that walks you through the module. I recommend downloading the module (see end of this post) and following along as I walk you through the module.
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365
Download the Real Estate Sources and Uses Module
To make this module accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you need) or maximum (your support helps keep the content coming – typical real estate modules sell for $100+ per license). 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.
We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.
- Added conditional formatting such that line items turn blue (i.e. required input) when the forecast method is toggled to ‘Detail’
- Initial release