Refinance Risk Analysis Tool in Excel (Updated Feb 2022)
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.
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.
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.
- Added ‘Pro Forma’ NOI and Debt service in cells
- Added ‘Pro Forma’ loan rate assumptions (E23:E25)
- Revised DSCR formula (row 18) to use current year NOI divided by current year DS
- Re-built Refinance Sensitivity Analysis section to remove hidden values in column T and to simplify the inputs/formulas
- Added probability to placeholder cap rate projections, tied to changes in interest rates
- Renamed Debt Service (Amortizing) to Debt Service (cell B7) to accommodate loan terms with IO
- Modified RAND() UST formula (row 23); more realistic possible change from previous year, floor of 1.00%
- Added ‘Spread Above Benchmark’ input in cell D24 that is quoted in basis points
- Minor updates to placeholder input values
- Misc. formatting updates
- 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
- Fixed error in Cash Flow after Debt Service calculation (row 8)
- Initial release