Free Excel templates

A free rental property calculator in Excel: cash flow, cap rate and ten-year IRR

Year-one income and expenses down to NOI and cash flow, cap rate, cash-on-cash, DSCR, break-even occupancy and the 1% rule, then ten years of rent, expenses, loan and a sale: four sheets, no macros.

The file runs a 350,000 single-family rental bought with 25% down and a 7.00% mortgage, rented at 2,800 a month. After vacancy, tax, insurance, maintenance and management, NOI is 20,844: a 5.96% cap rate. The mortgage costs 20,957 a year, so the property loses 1,793 in year one once a capex reserve is set aside, and fails the 1% rule at 0.8%. Held ten years with 3% rent growth and appreciation, it still returns 7.7% on the 108,000 invested, almost all of it from the sale.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Rental_Property_Calculator.xlsx (17 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with Commercial Real Estate Investing, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Four sheets. Inputs are blue cells on a pale blue fill on the Inputs sheet.

Read me
What the file does and its conventions.
Inputs
Price, closing costs, repairs, down payment, rate, term, rent, other income, vacancy, tax, insurance, maintenance, management, HOA, capex reserve, growth rates, hold and selling costs.
Year 1
Gross rent to NOI to cash flow, line by line, and the ratios: cap rate, cash-on-cash, DSCR, expense ratio, the 1% rule, break-even occupancy and gross rent multiplier.
Projection and Checks
Ten years of operations, loan balance and property value, the sale, the equity cash flows, IRR and multiple; then the checks: all OK.

What the worked case returns

MeasureValue
Cash invested108,000
NOI, year 120,844
Cap rate5.96%
Debt service, year 120,957
Cash flow, year 1, after capex reserve-1,793
DSCR0.99x
1% rule0.80%
Ten-year levered IRR7.7%
Equity multiple2.12x

Read the cap rate against the mortgage rate. At 5.96% on the property and 7.00% on the loan, every borrowed dollar costs more than it earns, so leverage lowers the cash yield. The return depends on rent growth and appreciation, which is a bet, not a cash flow.

How to use it on your own property

  1. Enter price, financing and rent on Inputs.
  2. Enter the operating costs as amounts or shares of rent.
  3. Read Year 1: NOI, cash flow and DSCR before anything else.
  4. Set growth, hold and selling costs and read the IRR on Projection.

Questions people ask about it

Is this template really free?

Yes. The file costs nothing: you enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference.

What is cash-on-cash return?

Year-one cash flow after debt service divided by the cash you put in (down payment, closing costs and repairs). Here it is negative, -1.7%, because the mortgage costs more than the property earns.

Can I use it for a real property?

Yes, by replacing the inputs. The property is fictional, and nothing here is investment advice.

Go further

For how investors value income property and read cap rates against financing, Commercial Real Estate Investing and its companion workbooks go further.

See the book and its free files →

Other free templates

This template goes with Commercial Real Estate Investing. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.