Commercial Mortgage Loan Analysis Model (Updated Mar 2024)
Over the years, I’ve received various requests to augment our library of real estate Excel models to include a model for underwriting and analyzing commercial mortgage loans. Specifically, people have asked for a tool to calculate the loan amount (ie. loan sizing) based on a combination of tests – debt service coverage ratio, loan-to-value, and debt yield. In response to that, I built this commercial mortgage loan analysis model.
In addition to both the underwriting and loan sizing modules included in this standalone model, I also added a variation of my refinance analysis tool that uses the underwritten NOI and loan sizing assumptions as a baseline to sensitize the future balloon risk. I hope you enjoy this one!
Update: In v2.3 we added a ‘Prepayment Analysis’ module that calculates Yield Maintenance and % of Balance prepayment fees (i.e. prepayment penalties). This is helpful to estimate the prepayment fee of a loan at some future point in time. Are you an Accelerator Advanced member? Refer to the ‘Advanced Debt Modeling’ endorsement to learn the math behind Yield Maintenance and how to calculate other prepayment fee methods.
What’s Included in the Commercial Mortgage Loan Analysis Model
The model includes six tabs – a version notes tab, four input and analysis tabs, and a data tab. The version tab includes notes on changes to the model since it’s initial release, as well as input links and notes. It also lists the version of Excel the model is currently compatible with:
- Excel 2013
- Excel 2016
- Excel 365
The model is likely also backward compatible with older versions of Excel, since I’ve not used any VBA in the model. Nonetheless, I have not tested it with older version of Excel.
To help you get started with the model, below I’ve written a description of each tab, embedded a video walk through of the model, included a link to download the model, and listed changes to the model by version.
Loan Summary Tab
The Loan Summary tab includes seven sections:
Investment Information. In this section, the user enters general information about the investment such as name, address, property type, and size. Loan type (e.g. acquisition vs. refinance) is also selected in this section.
Underwriting Summary. A basic summary of the underwriting results from the Pro Forma tab are reported here. This includes underwritten net operating income, underwritten cap rate, and underwritten value.
Pro Forma Summary. The Pro Forma summary section shows the effective gross revenue, operating expenses, and net operating income.
Proposed Loan Terms. This section includes the loan terms as selected on the Loan Sizing tab, such as loan amount, loan term, and interest rate. The section also outputs the expected balloon balance of the loan at the end of the term.
Loan Metrics. The model then calculates various loan metrics based on assumptions entered on the Pro Forma and Loan Sizing tabs. Loan metrics include payment, DSCR, debt yield, and LTV.
Sources and Uses. A breakdown of the sources (e.g. debt and equity) and uses (e.g. purchase price, closing costs, etc) for the investment.
Notes. The tab concludes with a notes section, where the user can enter salient comments about the loan opportunity.
Pro Forma Tab
In this tab, the analyst uses the direct capitalization method to arrive at an underwritten value for the subject property. This involves making assumptions for rent, other income, and expenses to solve for an underwritten net operating income.
I’ve included an apartment operating statement as default, but the user should add/remove rows and change income/expense labels as the property type dictates.
So for instance, if you’re underwriting a retail property you’d likely remove the Parking Income line, and rename the Storage Income line to Percentage Rent. You may also separate Reimbursement Income into CAM Reimbursement and Tax Reimbursement. Finally, you’d edit the expense line items to more closely match the expense items found in the historical operating statements.
With the Pro Forma complete, the user then enters a market cap rate. The model then takes the underwritten net operating income and assumed market cap rate, and automatically calculates an underwritten value. The net operating income and underwritten are key components for sizing the loan (see next section).
Along the right-hand side of the Pro Forma, I’ve included a notes section. It is within this notes section that the user enters comments and justifications for the assumptions she used in her Pro Forma. So for instance, if the user entered $120,000 for parking, she might write the following comment immediately to the right of that Parking Income assumption: “$100/month x 100 parking stalls”.
Loan Sizing Tab
One the Loan Summary and Pro Forma tabs are complete, the user calculates a proposed loan amount using the Loan Sizing tab. This is done first by entering proposed loan perimeters such as the loan term (in years), the interest-only period (in years), the amortization period (in years), and the interest rate. The interest rate is the sum of the proposed benchmark rate (e.g. US treasury rate) plus some loan spread.
With the loan parameters set, the user then sets tests for debt service coverage ratio (DSCR), debt yield (DY) , and loan-to-value (LTV). These tests solve questions such as, what is the maximum loan amount such that the payment does not exceed some user-set DSCR? Or what is the maximum loan amount, such that the resulting debt yield is not less than some user-set minimum debt yield?
The Maximum Loan Amount, based on the minimum DSCR, minimum debt yield, and maximum LTV as set by the user, is calculated at the bottom of the Loan Sizing tab. That value, by default, is automatically entered into the Proposed Loan Amount cell contained within the bold black box at the top of the Loan Sizing section.
While the Proposed Loan Amount cell contains a formula linking it to the Maximum Loan Amount cell, it is nonetheless an input (blue font cell) and can be changed manually by the user. However, if the value entered into the Propose Loan Amount cell causes one of the DSCR, DY, or LTV tests to fail, an alert will appear next to the failed test.
Refinance Analysis Tab
As an added bonus, I’ve inserted my Refinance Analysis tool into this model. If you recall, refinance risk relates to the possibility that the borrower will be unable to refinance the balloon balance at the end of the loan term. This occurs when either net operating income falls, interest rates rise, or market loan metrics (e.g. DSCR, DY, LTV) for sizing loans change.
To learn more about how to use this tool, read the tutorial and watch the video for the Refinance Risk Analysis Tool.
Prepayment Analysis Tab
In version 2.3 of the model, I added a module to estimate the fee for prepaying (i.e. paying off a loan before the end of the loan term) a mortgage loan. This allows you to estimate the fees based on either a Yield Maintenance or a % of Loan Balance prepayment penalty method.
The Prepayment Analysis module includes the following sections ‘Prepayment Summary’, ‘Prepayment Assumptions’, ‘Benchmark Assumptions’, and ‘Lender Cash Flow’. The Benchmark Assumptions and the Lender Cash Flow sections are specific to calculating Yield Maintenance.
Amortization Tab
The amortization tab was added in v2.0 of the model, and thus may not appear in the walk-through video. This tab includes a printable report of the loan cash flows from loan closing to loan maturity. This includes tracking loan payment date, loan balance, payment, principal, interest, and lender cash flows.
The tab also includes a weighted average life (WAL) calculation, which is then reported on the Loan Terms and Loan Summary tabs. The amortization table can support loan terms up to 50 years.
Data Tab
The model includes two drop-down menus – property type and loan type. The list of items that appear in those drop-down menus are housed on the Data tab. Additionally, the unit type labels for each property type (as shown in heading of various sections) are entered on the Data tab.
At anytime, changing the values in either the Property Type or Loan Type lists on the Data tab will change the values in their respective drop-down menus on the Loan Summary tab. This allows the user to add or delete property types from the model. It also allows for future customization of the model to include other loan types (e.g. construction loan).
Video Tutorial – Using the Commercial Mortgage Loan Analysis Excel Model
A brief tutorial on how to use the Commercial Mortgage Loan Analysis tool. Note that this tutorial was based on v1.0 of the model. Various changes have since been made to the model. View the model’s version notes.
Download the Commercial Mortgage Loan Analysis 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 Excel models sell for $100 – $300+ 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.6
- Added ‘UST Yield Curve’ line chart to the ‘Prepayment Analysis’ tab
- Added ‘Rates on ‘ date to the ‘Prepayment Analysis’ tab cell C15; linked line chart to ‘Rates on’ cell
- Updated UST and related links on the Prepayment Analysis tab
- Misc updates to ‘Version’ tab
- Updates to placeholder values
v2.5
- Fixed link to ‘Other Debt Models’ on the Version tab
- Fixed Max Loan calculation under ‘DSCR Amort. Test’ (Loan Terms E33); had been assuming annual payments, instead of monthly payments
- Added a heading to cell G32 of the Loan Terms tab (‘Included’) to help users understand ‘Yes/No’ values
- Updated ‘Maximum Loan Amount’ formula in H37 on the Loan Terms tab to make the formula dynamic to included tests (F33:G36)
- Updates to placeholder values
v2.4
- Loan Terms tab: under ‘Interest Rate’ renamed ‘Type’ to Interest Calculation Method
- Loan Terms tab: Added Variable Interest Rate module
- New input on Loan Terms tab: Fixed vs Floating in cell C13
- Reorganized Interest Rate section
- Added conditional formatting to hide unnecessary rows on Loan Terms tab when Floating is selected
- Renamed ‘Loan Payment’ to ‘Loan Payment (Month 1)’ to clarify that the loan payment may change over the term
- Amortization Tab: Created Variable Interest Rate section to track the periodic rate of variable rate loan
- Amortization Tab: Added input in cell U3 for Benchmark type (e.g. SOFR) and forward curve for benchmark rate (column T)
- Pro Forma tab: Added two additional sections for historicals (e.g. T12, previous year, two years ago)
- Updates to placeholder values
v2.3
- Added Prepayment Analysis module
- Yield Maintenance prepayment method
- % of Balance prepayment method
- Prepayment Summary
- Prepayment Assumptions
- Benchmark assumptions for Yield Maintenance calculation
- Lender CF tracking for Yield Maintenance calculation
- Minor updates to placeholder inputs
v2.2
- Updated 10-Yr UST default rate on Loan Terms tab
- Fixed issue on ‘Refinance analysis’ tab where ‘Existing Loan Balance’ (row 13) wasn’t calculating correctly when partial-IO is used
- Misc. updates to placeholder values
v2.1
- Fixed issue where Payment amount during IO period was incorrectly calculated for non-30/360 interest calc scenarios
- Misc. formatting enhancements
v2.0
- Added max 50 year loan term to Loan Sizing / Loan Parameters
- Renamed Loan Sizing tab to Loan Terms tab to better represent its purpose
- Added Loan Fee and Lender Yield to Loan Terms tab
- Added Loan Type toggle to Loan Terms tab to accommodate 30/360, Actual/360, and Actual/365 interest calc
- Moved Loan Closing date assumption from Refinance Analysis tab to Loan Terms tab
- Created Amortization report tab
- Tracking Payment, Principal, Interest, and Balance
- Calculating Lender Yield; reported to Loan Terms and Loan Summary tabs
- Dynamic Print Range with custom footer
- Minor changes to headings on Loan Terms tab
- Added Trailing 12 Months actuals section to Pro Forma tab to help develop Underwriting Pro Forma
- Added Print Range and footer to Loan Summary, Pro Forma, Loan Terms, and Refinance Analysis Tabs
- Weighted Average Life (WAL) module added
- Created section on Amortization tab to calculate WAL
- Included Average Life (WAL) result on Loan Terms and Loan Summary tabs
v1.11
- Added Medical Office property type
- Minor changes to Refinance Analysis assumptions
- Changed name of model to Commercial Mortgage Loan Analysis Model, to clarify that it’s NOT for residential loans
- Misc. formatting changes
v1.10
- Misc formatting changes
- Added refinance risk module
