Real Estate Debt Module in Excel (Updated May 2024)
I thought I’d share the real estate debt module I originally built for the ARGUS alternative in Excel. 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, and 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 Debt Module Layout
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 font 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
General Assumptions
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.
Loan Amount
With your general assumptions in place, you will now set the loan amount. I’ve included with this iteration of the module, just two calculation methods for loan amount. These are the “Stated” or a manually entered value or “LTV” or a percentage of the property’s value. You could fairly easily add other calculation methods. Possible options would be loan amount sized by debt coverage or debt yield. Since this is only a module (i.e. the module isn’t calculating NOI), however, it wasn’t possible to include this functionality.
Loan Terms
Next, you’ll enter the loan terms. If you are modeling a new loan, you will first enter the “Avg Rate Over Term.” This 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. However, it has 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 a few things. It shows each month, 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. 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.
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
Compatibility
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365.
Additional Resources
As an additional resource to the Real Estate Debt Module, we recommend using our Advanced Amortization Table Creator GPT. This tool allows for more sophisticated tracking and analysis of debt payments and amortization schedules. This tool offers customized schedules for various loan structures, flexible inputs of different loan terms, interest rates, and payment frequencies, and a schedule you can automatically updated based on changes in loan assumptions or refinancing terms. By leveraging the advanced features of this tool, you can create more robust and accurate financial models, streamlining the debt modeling process and gaining deeper insights into your real estate financing strategies.
In addition, you can find our other Excel tool for modeling debt here.
Download the Real Estate Debt Module In Excel
To make this module accessible to everyone, we have offered it 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 modules sell for $25 – $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 file (see version notes). Paid contributors to the module receive a new download link via email each time the module is updated.
Version Notes
v1.1
- Added a tool for sizing the loan amounts by debt coverage or debt yield
v1.0
- Initial release
