, , ,

Refinance Risk Analysis Tool in Excel

Simply put, 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 on this blog are largely geared toward the equity investor mindset, while too often ignoring the metrics that debt investors care about.

This post is meant as a counterbalance, a tool for those of us who think about risk metrics as much as return metrics; who think in terms of what could go wrong as much as what could go right. I present to you, a refinance analysis tool in Excel.

refinance-building


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


Update: October 14, 2016

One of our readers alerted me to an error in the cash flow after debt service calculation in row 8. Debt service was entered as a negative value in row 7, but cash flow after debt service is calculated as net operating income minus debt service. The result was that debt service was being added to net operating income rather than the reverse. The error has been corrected and revised v1.01 uploaded. Note that the error wasn’t affecting any of the other calculations in the model, since the cash flow after debt service line was for display purposes only, so if you’d downloaded an earlier version your analysis won’t change with this updated version.

Download the Refinance Analysis Tool

This tool is offered for free; just click the ‘Download Now’ button below.

Refinance Analysis Module
  • Tool to assess the refinance risk of a real estate investment
  • Models up to five scenarios
  • Apply shock to projected NOI to determine how adverse events would affect ability to refinance
  • Analyze refinance proceeds
  • Analyze NOI debt service coverage requirements
  • Analyze loan-to-value requirements
  • Analyze debt yield requirements

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 14 years of real estate investment and development experience. In his current position, Spencer assesses new investments for a $40bn real estate fund. He resides with his wife and kids in Dallas, TX.

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply