I thought I’d share the real estate debt module I built for the ARGUS alternative in Excel that I’ve been working on over the last few months. Most debt modules I’ve seen in real estate financial models only include the ability to model fully amortizing senior debt. Mike challenged me some time ago to come up with a more robust option -this debt module is what I came up with.
What’s Under the Hood
My real estate debt module includes some interesting features:
- Model both new senior and secondary debt, with entirely different assumptions for each,
- Choose to assume existing senior and/or secondary debt, as well as to refinance the existing debt during the hold period,
- Opt between three different interest calculation methods: 365/Actual, 360/Actual, or 360/30,
- Include interest-only payments, fully amortizing payments, or some combination of the two,
- Layer in loan origination or loan assumptions fees,
- Preview a fully dynamic amortization table, that rolls up all debt into one place
How to Use the Model – Video and Written Tutorial
The model is simple to use. The ‘Debt’ tab is where all inputs/assumptions are located. The tab is colored blue to represent an input tab.
The ‘Amortization’ tab is a report showing the combined amortization of the senior and secondary debt, combined payments made, as well as combined principal owed, based on the inputs entered in the ‘Debt’ tab. The ‘Amortization’ tab is colored green to represent a report tab. This tab is for viewing only – no inputs are made on the ‘Amortization’ tab.
The ‘Amort Calc’ tab is where the calculations are done. I’ve grouped the logic for the senior debt and the logic for the secondary debt so that you can hide/unhide the two financing types for ease of navigation. Again, no inputs are made on this tab and generally, unless you want to tweak the logic or fix an error, you will not need to open this tab.
Link Basic Assumptions and Turn on Senior/Secondary Financing
Start by heading to the ‘Debt’ tab and linking some basic assumptions from your model to the Debt Module. As always, blue fonted cells are inputs cells. Link the Analysis start [Debt F4] to your analysis start date. Next, link the analysis period length [Debt F5] to your analysis period length. Be sure the length is in years. Finally, link the property value [Debt F6] to your property value or purchase price.
With the model-specific cells linked, you can go ahead and set the debt parameters. To turn on the senior debt module, set the “Model Senior Debt?” drop-down [Debt F8] to “Yes”. Likewise, to turn on the secondary financing module, set the “Model Secondary Financing” drop-down to “Yes”. Notice, that if you set either drop-down to “No”, that the input cells for that module disappear.
You might also like: Real Estate Equity Waterfall Model
With the module(s) that you want turned on, you will next set the “General Assumptions” [Rows 11 – 14] for your debt. Choose “New” vs “Existing” debt to decide whether a new loan will be originated or whether you will assume existing debt. Next, set the “Interest Calc Method” to either 360/30, 365/Actual, or 360/Actual – the default is 360/30. Finally, enter the “Loan Type” and “Lender Name” for informational purposes only.
With your general assumptions in place, you will now set the loan amount. I’ve include with this iteration of the module, just two calculation methods for loan amount: “Stated” or a manually entered value or “LTV” or a percentage of the property’s value. You could fairly easily add other calculation methods such as loan amount sized by debt coverage or debt yield but since this is only a module (i.e. the module isn’t calculating NOI), it wasn’t possible to include this functionality.
Next, you’ll enter the loan terms. If you are modeling a new loan, you will first enter the “Avg. Rate Over Term” which is the contract interest rate if the rate is fixed or your best guess of the average rate over the term if the rate is floating. Next, you will enter the “Amortization” in years, with a default of 30 years and a max. of 50 years. Finally, you will enter how many years of interest-only will be modeled (default being zero) and the loan fee charged. With the above parameters entered, the Loan Terms section will display the first month’s interest-only period (if applicable), the monthly payment with amortization (monthly constant), the payoff at the end of the analysis period, and the payoff month.
If you are modeling an existing loan (loan assumption), your inputs will be slightly different. First, you will enter the “Avg. Rate Over Term” similar to above. Next, you will enter the original amortization, and the original term of the loan you will be assuming together with the number of years of interest-only the loan included. Finally, you will enter the number of months before the loan matures (“Remaining Term”), the loan assumption fee (if any), and the current monthly payment of the loan. With this information, the Existing Loan Terms section will automatically calculate the “Actual Loan Balance”, the “Original Loan Balance”, the “Remaining IO Period”, the “Monthly I/O Payment”, the payoff at maturity, and the month that the loan will mature.
If you are modeling an existing loan (loan assumption), you will also need to enter “Refinance Terms”, or the terms of the loan that will take out the existing loan once is matures (assuming the loan matures during the analysis period. The “Refinance Term” assumptions are similar to the new loan terms, with the exception that you will also need to include a refinance loan amount.
Preview Debt Service and Balance – Amortization Tab
With your assumptions entered in the ‘Debt’ tab, you can go to the ‘Amortization’ tab to see how your debt is being modeled. The Amortization tab shows each month, and its corresponding debt service payment [Column E], amount paid to principal [Column F], amount paid in interest [Column G], and the principal balance of the combined senior and secondary debt at the beginning of the month [Column H] and the end of the month [Column I].
There are a lot of moving parts with this module, and it still needs more testing to work out possible bugs. So if you find anything that needs correcting, or you have suggestions that will make the module better, don’t hesitate to reach out.
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.
Real Estate Financing – Debt Module
- Built for both new and existing senior and secondary debt, with entirely different assumptions for each,
- Three interest calculation methods available: 365/Actual, 360/Actual, or 360/30,
- Interest-only payments,
- Fully amortizing payments,
- Loan fees,
- Financing report tab rolls up payments, principal and interest owed, and debt outstanding by month,
- In v1.1, added a tool for sizing the loan amounts by debt coverage or debt yield.
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.
UPDATE 2/24/2017: Added a quick tool for sizing loan amount based on DSCR or debt yield.