This is a debt module that will allow the user to add one to three tiers of debt to his or her DCF model. This excel file should be fairly intuitive and easy to understand and the entire model is on one sheet. Download it from the link below and you can watch the video at the bottom and/or continue reading below.
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.
3-Tiered Debt Module
This module has lots of functionality and will allow the user to do the following:
- Choose how many months of interest only payments, amortization period, and total term for each of the three tiers
- Account for taking out additional capital to finance a portion of capital improvements during the hold period. The model will automatically reamortize the loans when additional capital is drawn.
- Select how the lender will calculate interest on each loan – 30/360, actual/360, or actual/365.
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.
Model Highlights and Important Notes
The Inputs Section
The top of the sheet, from rows 8 to 35, contains almost all the inputs for the model. In cells D9 and D10, the user should link the acquisition price and date from his or her model. Rows 12-35 contain all the manual input cells for the debt calculations as well as some essential return summary numbers for all three tiers of debt. Additionally, there is a box on the far right that summarizes combined loan details such as a total dollar amount of leverage and a weighted average cost of the debt combined. The weighted average cost of debt is an approximation of the weighted average over the hold period and fails to account for additional capital drawn during the hold period as well as if the durations of the loans are different.
There are two safety and warning measures put into the model that warn you if you have input a loan term that is longer than the interest only period plus the amortization period. If you try to input a number in the term input cell that is greater than the amortization and interest only period, a popup will appear telling you the input is too large, if you change the input cells in either the amortization period input cell or the interest only period input cell and combined they are less than the term, both the cells will turn red. If this happens, be sure to alter the appropriate cells to mitigate the issue.
Selecting the Lender’s Interest Calcs
In row 24, for the three loans, you will see ‘Loan Type’. Here you can select between 30/360, 365/360, and 365/365. These are three common methods that lenders use to calculate interest, they all effect a borrower’s actual interest rate and if you’d like to understand more about how these methods are calculated, check out my blog post on this topic by clicking here.
To see how this model works by choosing between each loan method, scroll down to row 46. Below are the Chosen Loan Amortization Schedules for each loan. First, move your mouse or click around the cells to see the formula used. Next, scroll to row 114 and there you will notice three boxes with plus (+) signs in the far left hand side of your screen (between row 114 – 192), click to open the amortization schedules. These are the amortization tables that are chosen from in the Chosen Loan Amortization Schedules.
Funding Cap Ex
Rows 31 through 35 will allow you to input the details for additional funding for capital improvements. Simply answer ‘Yes’ to the ‘Funding Cap Ex?’ question and the cells below will open up. The newly opened up cells will ask you what percent of cap ex would you like to fund with the loan.
It is critical to note that you must input the capital expenses from your own model into this module in order for this to work. To do this, scroll down to row 235 and in the far left corner of the screen you will see a box with a plus (+) sign in it. Click to open. In rows 199-201, link from your own DCF model your capital expenses. To illustrate how to do this, I included a second sheet in the excel file titled ‘CapEx’ with a capital expense portion of a DCF model to use as an example.
Below the cap ex inputs, starting in row 203, are the cap ex draws schedules according to your inputs. These draw schedules are used in the amortization schedules to reamortize the loans.
Inserting Debt Payments Into Your DCF Model
Take the cash flows in rows 39-44 and simply insert them into the debt section of your model. Be sure to match up the dates in row 35 with the dates in your model.
As always, I hope you find some value in this model and feel free to reach out with any questions, comments, or concerns.
About the Author: Michael Belasco has over eight years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.