Financial Modelv1.0.5

From Rent to NOI, NCF and Cash-on-Cash Return

Build net operating income line by line from gross potential rent, take out the replacement reserve to reach net cash flow, pay a year of debt service, and divide what is left by the equity invested.

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 line by line from gross potential rent, then take out the replacement reserve to reach net cash flow. They will subtract a year of debt service and divide what is left by the equity invested to compute the cash-on-cash return.

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. Rent Collected

Start from the rent the property could charge, then take out what it will not collect.

B7Gross Potential Rent (GPR)

$1,000,000

Annual rent for every unit: actual rents for occupied units plus market rents for vacant ones.

B8Vacancy (% of GPR)

5.00%

Rent lost to empty units over the year, as a share of GPR.

B9Vacancy Loss

$50,000

=GPR*VacancyPct

GPR times the vacancy rate: the rent the empty units would have paid.

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)
Fannie Mae Multifamily Selling and Servicing Guide, Part II, Chapter 2, Section 203 Income Analysis, Section 203.01 (Underwritten NCF), footnote 1 to the Underwritten NCF table

B10Concessions

$10,000

Rent given up as incentives to sign leases, such as free months, over the year.

B11Bad Debt

$5,000

Rent billed but not collected and written off as uncollectable, over the year.

B12Net Rental Income (NRI)

$935,000

=GPR-VacancyLoss-Concessions-BadDebt

GPR less vacancy loss, concessions and bad debt: the rent actually 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, the EQUALS line that closes Items 4 to 6

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.