Rental Property Investment Model: Cap Rate, DCR, Payback – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Debt coverage ratio reported as a headline with a written verdict - NEGATIVE, THIN or OK - so you see whether a lender will fund it before you interpret the number.
2. A payback year that is a real answer or an honest refusal, never a meaningless negative figure when payback does not happen inside the horizon.
3. Sixteen plain-English integrity tests built into the file, all passing and all live, so they catch mistakes in your assumptions too.

REAL ESTATE EXCEL DESCRIPTION

Rental Property Investment Model: Cap Rate, DCR, Payback is an Excel template (XLSX) available for immediate download upon purchase.

Most rental spreadsheets compute a cap rate and stop. A cap rate tells you what the property yields unlevered; it does not tell you whether the bank will lend against it, and it does not tell you when you get your money back. This model carries all three answers side by side, on the same assumptions, with a ten-year schedule underneath them.

The example that ships in the file is a $460,000 property bought with 25% down – $115,000 of equity and a $345,000 mortgage at 5% over 25 years – renting at $4,000 a month with 5% vacancy, 3% annual rent growth, $6,000 of property tax, $1,500 of insurance, maintenance at 8% of gross rent and management at 8% of effective gross income.

On those numbers: gross yield 10.43%, cap rate 6.65%, debt coverage ratio 1.26x, cash-on-cash return 5.57%, and cumulative net cash flow covering the down payment in year ten.

Three things this model does that comparable rental spreadsheets do not.

First, it reports the debt coverage ratio as a headline metric with a written verdict next to it, not as a number buried in a schedule. The verdict reads NEGATIVE if the ratio is below 1.00 – the rent does not cover the mortgage – THIN if it is below 1.20, which is under most lenders' minimum, and OK above that. The shipped example sits at 1.26x, which is a deal a bank will look at. Change the down payment to 15% and watch the verdict change before you have to work out what the number means.

Second, the payback year is a real answer or an honest refusal. It is the first year in which cumulative net cash flow covers the down payment, found by counting the years that fall short rather than by a formula that returns a negative or nonsensical year when payback never happens. If the property does not pay back within the ten-year horizon, the cell says so in words.

Third, the whole sheet is driven from one place. Every operating cost is a stated percentage or a stated amount on the Assumptions sheet, and the ten-year schedule and all four metrics recompute from it. There are no hard-coded figures hiding inside the cash flow, which is the usual reason a downloaded rental model gives the wrong answer when you change the rent.

What is inside. Five sheets. ReadMe with a colour key and a sheet-by-sheet guide. Assumptions – purchase price, down payment, rate, term, rent, vacancy, tax, insurance, maintenance, management and rent growth, with the derived down payment amount, loan amount, monthly payment and annual debt service computed below them. Cash Flow – a ten-year annual schedule from gross rent through vacancy loss, effective gross income, each operating expense, net operating income, debt service, net cash flow and the cumulative running total. Metrics – gross yield, cap rate, cash-on-cash, debt coverage ratio, payback year and the leverage verdict. Checks – sixteen pass/fail tests.

How you know the numbers are right. The Checks sheet runs sixteen tests inside the workbook and all sixteen pass in the shipped file. They are written in plain English, so you can read what is being tested: that the down payment plus the loan equals the purchase price, that the monthly payment reproduces the loan amount when amortised, that net operating income is effective gross income less total operating expenses in every one of the ten years, that the cap rate is year-one NOI over price, that the payback year really is the first year whose cumulative cash flow clears the down payment, and that vacancy loss is never a positive number. They stay live when you change inputs, so they also catch mistakes in your own assumptions.

Separately, every output in this model was re-derived in an independent script using different arithmetic, and the results agree. That verification covered this model together with three others in the same family, 691 comparisons in all, with no disagreements.

What this model does not do. It is a single-property, annual, pre-tax model. It does not handle depreciation and the tax shield, a refinance partway through, multiple units with separate lease terms, or a sale and the capital gain on exit – the payback test uses operating cash flow only. If you need a full multifamily underwriting model with unit-by-unit rent rolls and an exit waterfall, that is a different and much larger file. If you are looking at one rental property and need to know the yield, whether a lender will fund it, and when you get your deposit back, this does exactly that and shows its work.

Source: Best Practices in Real Estate, Integrated Financial Model Excel: Rental Property Investment Model: Cap Rate, DCR, Payback Excel (XLSX) Spreadsheet, Paweł Dudek, Balancewright