Duplex calculator

Duplex calculator in Google Sheets: a worked example

A duplex is the easiest property to get wrong on paper, because two rents look like twice the safety and the arithmetic does not work that way. The vacancy, the management fee, the repairs and the capital reserve all scale with the rent, and the mortgage does not care how many doors there are.

So here is the whole calculation, worked through on one real duplex, with every step shown. You can build this yourself in Google Sheets — the formulas are below, and there is nothing secret in them.

The six numbers you have to type

Everything else is derived. If you only get these right, the rest follows.

The purchase price, and what it costs to close. $285,000 and $8,550 in this example. Closing costs are not part of the loan and they are not optional.
The rent per unit. $1,600 and $1,600, so $3,200 a month. Two units, typed separately, because in a real duplex they rarely match.
The loan. 25% down — $71,250 — leaves $213,750 borrowed at 6.75% over 30 years.
The vacancy rate. 5% here. This is the number people leave at zero, and it is the number that decides whether the rest is fiction.
The fixed annual costs. $3,420 of property taxes, $1,680 of landlord insurance, $250 of legal and licenses, and $85 a month of water, sewer and trash.
The costs that scale with the rent. 8% management, 8% repairs and maintenance, 6% capital reserve, 4% leasing and turnover. These four are where a duplex analysis usually goes wrong, because they are easy to leave out and they total 26% of the rent.

The arithmetic, in order

Five lines. Each one uses the line above it.

Effective gross income. $3,200 × 12 = $38,400 of gross rent, less 5% vacancy, is $36,480. This is the number to start from, not the $38,400.
Operating expenses. The fixed costs plus the four percentages, applied to the rent: $15,931.60. Note that the mortgage is not here. Operating expenses are what the property costs to run, whether or not there is a loan on it.
Net operating income. $36,480 − $15,931.60 = $20,548.40. This is the figure a lender and an appraiser care about, and the one that a cap rate is built from.
Annual debt service. A $213,750 loan at 6.75% over 30 years costs $16,636.54 a year in principal and interest. In a spreadsheet this is PMT, not an estimate.
What is left. $20,548.40 − $16,636.54 = $3,911.86 a year, which is $326 a month.

Two rents of $1,600 and the property keeps $326 a month. That is not a bad duplex — it is an ordinary one, priced the way duplexes are priced. The gap between $3,200 and $326 is the entire point of doing the arithmetic before you sign.

Break-even rent calculator — run your own price and rent through it. Free.

The four numbers that actually decide

Cash flow alone does not tell you whether the money is well placed. These four do, and each one answers a different question.

Cash-on-cash return — 4.61%. $3,911.86 divided by the $84,800 actually invested: down payment, closing costs and the reserve you set aside. This is the number to compare against whatever else you would have done with $84,800.
Cap rate at purchase — 7.21%. $20,548.40 divided by $285,000. It ignores your loan entirely, which is exactly why it is useful: it describes the property, not your financing.
DSCR — 1.24. $20,548.40 divided by $16,636.54. It is how many times the property covers its own mortgage. Lenders generally want to see 1.20 or better, and this is the number that decides whether a commercial lender says yes.
Break-even occupancy — 84.81%. The share of the year both units have to be rented before the property stops paying for itself. That is just over ten months of the twelve, across both doors.

Two more worth having on the same page. The gross rent multiplier is 7.42 — the price divided by a year of gross rent, where lower is better. And the 1% rule comes out at 1.12%: monthly rent against purchase price, which clears the usual 1.00% reference with very little room.

What the spreadsheet cannot tell you

This matters more than the formulas.

None of these numbers know the roof is fifteen years old, that one unit is under market because the tenant has been there seven years, or that the street floods. A spreadsheet takes the numbers you type and adds them up correctly. It does not approve the property, and it cannot tell you whether the deal is good — only what it does if your assumptions hold.

The useful habit is to run it twice: once with the numbers you expect, and once with the rent 10% lower and the repairs higher. If it only works in the first version, you have learned something.

The other worked examples

Each file on this site carries a worked example already loaded, and each one has its arithmetic written out in full:

Break-even rent calculator — run your own numbers — break-even rent, and the free first-pass filter.
Where a landlord year actually goes — twelve categories, and what each one took.
A short-term rental year, measured in nights — occupancy, ADR, RevPAR, and what was kept.
Five properties on one page — the portfolio view, and the vacancy column.

If you would rather not build it

Every formula above is ordinary spreadsheet arithmetic and you can write it yourself in an afternoon. If you would rather start from something that already works, there are two files here.

The Deal Screener is free. It puts five properties side by side on a first-pass filter — cash flow, cap rate, break-even rent and how far the rent can fall — so you can throw out the ones that do not survive before you spend an evening on any of them.
The Rental Property Analyzer is the file this worked example comes from. One property, up to eight units, with the same deal shown three ways, a thirty-year projection, the full amortization schedule with an editable extra-payment column, and a tab that defines every figure it prints — including where each one misleads. See the Rental Property Analyzer

Get the Deal Screener — free

Both open in Excel and in Google Sheets, and every formula is readable. The numbers on this page are not illustrations: they are the worked example loaded in the Analyzer, recalculated from the file itself.

This template is a calculation tool, not financial, tax, or legal advice. Results are estimates based on the numbers you enter. Verify with a qualified professional before making any purchase, financing, or tax decision.