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.
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).
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
Measure
Value
Cash invested
108,000
NOI, year 1
20,844
Cap rate
5.96%
Debt service, year 1
20,957
Cash flow, year 1, after capex reserve
-1,793
DSCR
0.99x
1% rule
0.80%
Ten-year levered IRR
7.7%
Equity multiple
2.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
Enter price, financing and rent on Inputs.
Enter the operating costs as amounts or shares of rent.
Read Year 1: NOI, cash flow and DSCR before anything else.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.