A calculator tells you what happens if you are right. An underwrite tells you what happens if you are wrong. Here is the five-tab structure, and the two Excel habits that prevent most modeling errors.
Last revised August 8, 2026
There are two kinds of short-term rental spreadsheets. One is a calculator: you type inputs, it prints a return. The other is an underwriting template: it forces you to state your assumptions, shows you which ones the deal is actually sensitive to, and makes it awkward to lie to yourself. The first kind sells a lot of courses. The second kind is what you want before you wire earnest money.
Most people are handed the first and think they have the second.
What "underwriting" means in a spreadsheet
Underwriting is not computing a return. It is establishing the range of outcomes and deciding whether the bad end of the range is survivable. In Excel terms, that means the template needs structure a flat calculator does not have.
A separated inputs sheet. Every assumption lives in one place, one cell each, clearly labeled. No hardcoded numbers inside formulas. This sounds like housekeeping; it is the single biggest determinant of whether the model is auditable. If your purchase price appears in fourteen formulas, you will never confidently change it.
Monthly granularity, not annual. Twelve columns with their own ADR and occupancy. An annual average cannot show you the four-month trough that determines your reserve requirement, and the reserve requirement is frequently the real answer to "can I afford this deal."
A debt service block that models the actual loan. Rate, term, down payment, points, and — if you are using anything other than a conventional 30-year — the amortization structure that goes with it. Interest-only periods and balloon dates change the risk profile completely and belong in the model, not in your memory.
A sensitivity table. Excel has had Data → What-If Analysis → Data Table for decades and almost nobody uses it. Two variables, usually occupancy and ADR, gridded against your key output. This is the sheet that tells you whether you are looking at a robust deal or a knife-edge one.
A comparison tab. Underwriting one property tells you very little. Underwriting six and ranking them tells you what your market actually offers, and it stops you from falling in love with the first thing you modeled.
The structure I use
When we rebuilt our STR income analyzer into something I would hand to a lender, it settled into five tabs:
- Inputs — purchase, financing, furnish budget, per-turn cleaning, utilities, management percentage, tax rates. Every cell named.
- Monthly model — real day counts, per-month ADR and occupancy, bookings derived from average length of stay so cleaning scales correctly.
- Annual roll-up — NOI, cash flow after debt service, cash-on-cash, and a minimum-month line that is the number I actually look at first.
- STR vs. LTR — the same property modeled as a long-term rental. This is the comparison almost no template includes, and it is the honest one: STR has to beat LTR by enough to pay for the work and the volatility, or it is just a job you bought.
- Sensitivity — occupancy × ADR against cash flow, with the break-even cells conditionally formatted so the cliff is visible.
The STR-versus-LTR tab is the one people resist and the one that changes decisions. A property that nets $6,200/year as an STR and $5,400 as an LTR is not an STR deal. You are taking on guest management, seasonal volatility, regulatory risk, and higher turnover cost for $800.
Assumptions that need a source, not a vibe
An underwriting template is only as good as its inputs, and the inputs are where discipline collapses. Anchor these:
- Occupancy and ADR by month: pull from a market data provider such as AirDNA and record the pull date in the sheet. Comp data ages.
- Tax treatment and depreciation: IRS Publication 527 is the governing document for residential rental property, including personal-use rules that can reclassify the whole property. Do not model after-tax returns without reading it or paying someone who has.
- Rate environment: date-stamp your financing assumption against a published series such as Freddie Mac's PMMS so a stale model announces itself.
- Insurance: STR coverage is not homeowner's coverage, and it is not landlord coverage either. Get a real quote before the model goes final; the delta from a guess is routinely hundreds of dollars a month.
- Local legality: licensing caps, primary-residence requirements, and HOA restrictions are binary risks. HUD and your municipal code are better sources than a forum post.
Two Excel habits that prevent most errors
Name your input cells. =Occupancy_Jan*ADR_Jan*Days_Jan is reviewable. =B7*C7*D7 is not. Named ranges cost five minutes and make every subsequent formula self-documenting.
Build a check row. One row that recomputes a total by a different path and flags a mismatch. Sum your monthly revenue two ways — from nights × rate, and from bookings × length-of-stay × rate — and have the sheet turn red if they diverge. Every spreadsheet error I have ever shipped would have been caught by a check row.
The bar to clear before you buy
Set occupancy to the worst month in your comp data and hold it for a full year. Does the property survive? If yes, the deal is robust and the upside is a bonus. If it only clears at the annual average, then you are not buying a cash-flowing asset — you are buying a seasonal business that requires working capital, and the template's job is to tell you exactly how much.
That is the difference between a calculator and an underwrite. A calculator tells you what happens if you are right. An underwrite tells you what happens if you are wrong, which is the only question that has ever cost anyone money.