Refinance Risk Analysis Tool in Excel (Updated Nov 2023)
Generally speaking, equity real estate investors spend their time modeling the upside while their debt partners spend their time modeling the downside. 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.).
In late 2023, I released v3.0 of this model. That version contains both a ‘Forward Looking’ analysis – the original version – as well as a ‘Current State’ analysis, which assesses the impact of refinancing today. This ‘Current State’ analysis was added to better understand the impact of rising interest rates on a need/decision to refinance.
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. I should note that this video only covers the ‘Forward Looking’ portion of the model, and does not yet include a walkthrough of the ‘Current State’ tool released in v3.0 of this model.
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.
Frequently Asked Questions about the Refinance Risk Analysis Tool
Version Notes
v3.0
- Updated placeholder values based on 2022/2023 rate increases
- Renamed primary refinance analysis tab to: ‘Fwd Looking Refi Analysis’
- Added ‘Initial Rate’ and updated time zero assumptions in Forward Looking Refi Analysis for more intuitive analysis
- Changed benchmark from 10-yr UST to dynamic based on dropdown menu
- Renamed ‘Disposition Assumptions’ to ‘Valuation Assumptions’
- Added gray background to time zero assumptions to signal importance
- Added IO vs Amort flag in cell D14
- Created new ‘Current State Refi Analysis’ to assess impact of refinance today and at maturity
- Enter values as of the ‘Initial Loan Closing’ (column E)
- Enter values as of today (Current State) and at Loan Maturity
- Row 40 calculates proceeds from refinance; if negative, borrower will need to bring cash to refinance
- Misc formatting updates
v2.2
- 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
v2.1
- 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
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
