Generally speaking, equity real estate investors spend their time modeling the upside while their debt partners spend their time modeling the down side. Equity investors generally focus on an investment’s internal rate of return, cash-on-cash return, equity multiple and other return metrics while debt investors focus on debt coverage, loan to value, debt yield and refinance risk.

The models we’ve shared to our Library of Real Estate Excel Models are geared toward both equity and debt investors, with a heavy weight toward tools that assist equity investors. This model is meant to provide some counter balance to the heavy equity focus, by providing a tool that helps you assess risks that are important to debt investors (e.g. debt service coverage, balloon risk, default risk, etc.).

Since creating this tool, I’ve since built a comprehensive, standalone Commercial Mortgage Loan Analysis model. That model fully integrates this refinance risk analysis module, and is meant to be used by lenders in sizing, analyzing, and pricing real estate debt.

Balloon balance (or balloon payment) refers to “the final payment on a loan. In commercial real estate, the balloon payment is the entire outstanding balance of the loan as of the loan maturity date. A balloon payment is only due when the loan has not fully amortized.

What is the Refinance Risk Analysis Tool?

To explain what this tool does, consider this scenario. You’re considering purchasing a $1,000,000 building. You have $300,000 cash so you’ll need to borrower 70% of the purchase price (or $700,000) in order to close.

You go to a bank and ask for a $700,000 non-recourse loan against the property with a 10-year term and 30 years of amortization. Whether that lender agrees to lend you the full 70% at those terms will depend on a whole host of factors, including whether she believes you’ll be able to refinance the loan at the end of the loan term and pay off the balloon balance. Knowing a property’s refinance risk before walking into the lender’s office will give you an upper hand.

The Refinance Analysis tool (download link below) allows you to assess the likelihood under various scenarios that the property and market conditions will be sufficiently strong to refinance the property at loan maturity. The tool performs three tests: loan-to-value, debt service coverage, and sufficient loan proceeds based on a minimum lender debt yield to alert you of potential issues.

The tool is not standalone. You’ll need to model income and debt separately. You’ll then link the tool to your income and debt assumptions and make certain market lending assumptions such as treasury rates, spreads above treasuries, and NOI cap rates.

You’ll also apply your assumptions for market loan-to-value, debt service coverage, and debt yield requirements. With the assumptions set, you’ll apply reductions to your base NOI and be able to see what impact those reductions have on your ability to refinance the property in each year of the hold period.

How to Use the Refinance Risk Analysis Tool

I recorded a short video that illustrates how to use the model. If you have further questions on how to use the model please don’t hesitate to reach out.


Compatibility

This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365

Download the Refinance Risk Analysis Tool

To make this tool 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 tools sell for $50+ 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.


Version Notes

v2.0

  • Added Versions tab
  • Update naming convention for consistency
  • Removed USD $ symbol to accommodate non-US users
  • Added selling costs assumption (row 13)
  • Reversed sign on DSCR calculation
  • Updated ‘Market Mortgage Interest Rate’ (row 26 ) to pull from row 41
  • Removed group
  • Rearranged layout to make it more intuitive
  • Misc. formatting updates

v1.1

  • Fixed error in Cash Flow after Debt Service calculation (row 8)

v1.0

  • Initial release

About the Author: Born and raised in the Northwest United States, Spencer Burton has nearly 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30 billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently Head of Real Estate Investments and member of the founding team at Stablewood Properties. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.