Real Estate Debt Module in Excel (Updated May 2022)

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, 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 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 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

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 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.

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” 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.

 

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.

Download the Real Estate Debt Module In Excel

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’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