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