Financial Modelv1.3.9

Commercial Loan Underwriting: Which Constraint Sizes the Loan

Build NOI and NCF from rent, value the property, size the loan three ways (LTV, DSCR, Debt Yield), and see which one binds, how much room the others leave, and whether a requested loan passes.

Licensed under CC BY 4.0. Share and adapt it, commercially too, as long as you credit Globalbiz CRE Academy. What is open and what is not: License.

Learning objective

Learners will build net operating income and net cash flow from a rent roll, value the property at a cap rate, and size a commercial mortgage three ways, by loan-to-value, debt service coverage and debt yield. They will identify which limit binds, how much room the others leave, and whether a requested loan passes all three.

This is the complete model, not a sample or a preview. Every step, input, formula and download on this page works on its own, and nothing here requires an account or a paid subscription.

1. Income

Start from what the property could earn if fully leased, then take out what it will not collect.

B7Gross Potential Rent

$1,000,000

Annual rent if every unit or suite were leased at market rent for the full year.

B8Vacancy & Credit Loss (% of GPR)

5.00%

Rent lost to empty space, concessions and tenants who do not pay, as a share of GPR. Lenders underwrite at least the market vacancy rate even when the building is full today.

B9Vacancy & Credit Loss ($)

$50,000

=GPR*VacancyPct

The dollar amount of GPR that will not be collected.

Source

Fannie Mae Multifamily Selling and Servicing Guide, Part II, Chapter 2, Section 203 Income Analysis, Section 203.01 (Underwritten NCF), Underwritten NCF table, Item 4 (MINUS)

B10Other Income

$25,000

Recurring income that is not base rent: parking, laundry, storage, fees.

B11Effective Gross Income (EGI)

$975,000

=GPR-VacancyLoss+OtherIncome

What the property actually collects: GPR, less vacancy and credit loss, plus other income.

Source

Fannie Mae Multifamily Selling and Servicing Guide, Glossary, “Effective Gross Income”
Fannie Mae Multifamily Selling and Servicing Guide, Part II, Chapter 2, Section 203 Income Analysis, Section 203.01 (Underwritten NCF), Underwritten NCF table, the EQUALS line that closes Items 7 to 16

This model runs in your browser. No spreadsheet software of any kind is required. The .xlsx download is optional, and it opens at no cost in LibreOffice Calc and in Google Sheets.

Download as Excel (.xlsx)

Solved modelYour numbers with every formula live. Build it yourselfYour numbers, with every calculation cell left empty. Write each formula from scratch. Answer keyThe expected formula for every cell, to check your build.

Every download uses exactly the numbers on screen, and the cell addresses are the same in all three files. Values are stored at full precision and only displayed rounded.