Short-Term Rental Pro Forma in Excel: ADR, Occupancy, and Seasonality

Short-Term Rental Pro Forma in Excel: ADR, Occupancy, and Seasonality

Short-term rental underwriting fails in a specific, predictable way: the buyer models the revenue like a hotel and the expenses like a long-term rental. The revenue side gets the excitement — nightly rates, occupancy dreams, screenshots of peak-season comps — while the expense side inherits a landlord's mental template that is wrong by half. The result is a pro forma that overstates NOI structurally, not marginally, and a buyer who discovers the STR expense load one management statement at a time.

This pillar builds the model that prevents that: a monthly pro forma (annual columns cannot hold a seasonal business), revenue as ADR × occupancy with a real seasonality curve, the full STR expense stack, and the analysis the two sides produce together — including the one comparison every STR purchase should run and most skip. One illustrative property carries through: a 3-bedroom vacation-market house at a $450,000 purchase with $35,000 of furnishing and setup. Every figure is an illustrative example, not market data — STR markets vary enormously, and this article's job is the machinery, which transfers, rather than the numbers, which do not.

Why Monthly Columns Are Non-Negotiable

Every other pro forma on this site runs annual columns; the STR model cannot. The reason is the business's shape: our illustrative property earns 3.8 times as much in July as in January, and its top four months produce over half the year's revenue. An annual model averages that shape away — and with it, everything the shape determines: the winter months that run cash-negative even in a good year, the reserve those months require, the catastrophic cost of losing a peak month versus a trough month, and the seasonally honest answer to "can this property carry its mortgage." The monthly layout is not extra precision; it is the minimum resolution at which an STR's economics are visible.

Revenue: ADR × Occupancy × Nights, Twelve Times

STR revenue has three components per month — average daily rate, occupancy, and the calendar — and the model carries a row for each:

Month's revenue = ADR × occupancy % × nights in month

Our illustrative curve: ADR ranging from $180 in deep winter to $350 in peak summer, occupancy from 45% to 88%, producing gross revenue of about $64,500 — a blended ADR of $270 at 65.5% average occupancy, or an annual RevPAR (revenue per available night, the metric that unifies the two levers) of about $177.

Three disciplines keep the revenue block honest:

1. Comp the curve, not the peak. The seasonality profile — all twelve ADR/occupancy pairs — comes from comparable listings' actual performance, via market data tools and comparable operators, at your property's bedroom count, quality tier, and location. The classic error is comping July and extrapolating; July is the month that needs the least underwriting.
2. Model economic occupancy. Calendar-blocked nights (owner use, maintenance turns, minimum-stay orphan gaps) are not rentable nights; a 90%-bookable calendar at 70% booking is 63% occupancy, and the model should compute it that way.
3. ADR and occupancy trade off — respect the frontier. They are not independent inputs: price higher and occupancy falls, and vice versa. Underwriting the market's top-decile ADR at the market's top-decile occupancy is claiming both ends of a trade-off simultaneously. The mechanics of the trade-off, and the RevPAR frame for navigating it, get their full treatment in the ADR × occupancy guide.

The Expense Stack: Where STR Underwriting Is Won and Lost

Now the half that surprises buyers. Run the full stack on our property's $64,500 of revenue (illustrative rates throughout):

• Platform fees (~3% host-side on major platforms' standard splits): ~$1,900
• Management — the big one: full-service STR management commonly runs several times a long-term manager's rate, reflecting genuinely different work (pricing, guest communication, turnovers, reviews). At an illustrative 20% of revenue: ~$12,900
• Utilities — all of them: unlike an LTR, the owner pays power, water, internet, streaming: ~$4,800 plus ~$1,800 of internet/subscriptions
• Supplies and consumables (linens, toiletries, kitchen restock): ~$2,400
• Insurance — STR-rated coverage, materially above landlord policies: ~$3,600
• Maintenance — elevated by usage intensity: ~$4,000
• Property taxes: ~$5,400, plus lodging licenses/permits: ~$600
• The furnishing reserve — the line almost every homemade STR model omits: furniture, mattresses, and appliances in hospitality service are consumables on a multi-year cycle, and the $35,000 initial setup implies a recurring replacement reserve, here ~$5,000/year

(Cleaning fees are handled as a pass-through — guest-paid, offsetting cleaning cost — with the model carrying only the owner's net exposure from mid-stay cleans and quality control. Where the market forces cleaning into the nightly rate instead, both sides move into the model explicitly.)

Total operating expenses: roughly $42,400 — about 66% of revenue — leaving NOI of ~$22,100. Read that expense ratio against the long-term-rental template in most buyers' heads (operating ratios commonly in the 35–45% zone for self-contained SFR math) and the structural point lands: an STR converts revenue to NOI at a fundamentally worse rate, because it is a hospitality operation, and hospitality has hospitality's cost structure.

The Comparison Every STR Purchase Should Run

Which sets up the model's most valuable output. The same house rents long-term for an illustrative $2,600/month — $31,200 gross. Run both columns:

2.07× the revenue becomes 1.28× the NOI. The STR premium is real on this illustrative property — but it is a fraction of what the top line advertises, and it is the compensation for everything the LTR column doesn't carry: revenue volatility, regulatory exposure, operational intensity, and the furnishing capital. A pro forma that shows only the STR column answers "what will this earn?"; the two-column version answers the actual question — is the premium worth the risk? — and properties where the premium runs thin are announcing that the answer is no before closing does.

Debt, Cash Flow, and the Seasonal Reserve

Below NOI, the model runs the standard machinery — debt service, cash flow, returns — with two STR-specific additions. First, coverage on honest NOI: financed at illustrative terms, this property's coverage is computed on the $22,100, not on a fantasy LTR-ratio version of the STR revenue (and note that STR-friendly loan programs have their own qualifying-income conventions, typically haircut — a cousin of the DSCR loan program conventions). Second, the seasonal reserve as a computed output: the monthly layout sums the winter's cumulative negative cash flow against the fixed mortgage, producing the operating reserve the property requires — a number annual models cannot produce and STR owners without it discover in February.

Setup Capital: the Second Sources-and-Uses

One block sits outside the operating model and inside the returns: the setup budget. Furnishing, appliances, linens, smart locks, photography, initial supplies — our illustrative $35,000 — is equity, exactly as much as the down payment is, and the return math must divide by the true denominator. On this property the cash invested is the down payment plus closing costs plus the full setup: computing cash-on-cash against the down payment alone flatters the return by the setup's entire share, which on furnished properties is not rounding error. Two disciplines follow: the setup budget gets its own line-item build (it is a rehab budget's cousin, and the same scope-first template logic applies), and the annual furnishing reserve above is its recurring shadow — the same capital stock, amortized forward. The quick-screen version of the full return math, with the setup capital handled correctly, is the Airbnb investment calculator; this pillar's model is that calculator with twelve months of resolution.

The Exit Question

STR pro formas habitually stop at cash flow, but the hold has to end somehow, and STR exits have their own shape. A single-family STR generally sells one of two ways: as a house, on residential comps, to a buyer indifferent to your revenue — in which case the STR premium was purely operational and the exit is the housing market's; or as an operating STR, furnished and permitted, to another operator — in which case the revenue history and, critically, the transferability of the permit are the asset. The model should be honest about which exit it assumes, because they can diverge widely, and the regulatory line item is really an exit assumption in disguise: a jurisdiction tightening its rules compresses the operator-exit price toward the house-exit price overnight. The stabilizing fact worth modeling explicitly is the LTR fallback — the two-column comparison above doubles as the downside floor, since a property whose long-term economics stand on their own converts a regulatory or market shock into a strategy change rather than a distressed sale. Properties that only work as STRs carry a risk the pro forma should name.

Sensitivity: Operating Leverage Cuts Against You

The expense stack's shape creates the model's final lesson. Because a large share of the costs are fixed or semi-fixed (insurance, taxes, utilities' base load, the reserve), revenue misses amplify: on our property, ADR coming in 10% light cuts NOI by 22.5% — better than 2-to-1 amplification — and a 10-point occupancy miss lands similarly. This is ordinary operating leverage, but STR buyers underwriting from revenue screenshots rarely price it. The model's sensitivity grid therefore runs ADR × occupancy as its two axes — the STR equivalent of the exit-cap grid in acquisition models — with NOI and coverage repriced in every cell, and the honest question asked of the grid: at which plausible cell does this property stop covering its mortgage?

The STR-Specific Mistakes

1. Annual averaging — the seasonality, the reserve, and the winter all vanish.
2. Peak-month extrapolation — comping July, earning January.
3. The LTR expense template — landlord ratios on a hospitality operation.
4. No furnishing reserve — treating a consumable capital stock as a one-time cost.
5. Claiming both ends of the ADR/occupancy trade-off.
6. No regulatory line — permits, lodging taxes, and the risk that the rules change; underwriting an STR without confirming the jurisdiction's current and pending rules is underwriting a business that may not be legal at your exit.
7. Skipping the LTR comparison — buying the premium without measuring it.

Frequently Asked Questions

How do I project revenue for a short-term rental? Twelve months of ADR × occupancy × nights, with the curve comped from actual comparable listings' performance at your property's tier — never from peak months or listing prices. Blend to check: does the implied RevPAR sit plausibly within the comp set?

What expenses should an Airbnb pro forma include? The full hospitality stack: platform fees, STR-rate management, all utilities, supplies, STR-rated insurance, elevated maintenance, lodging taxes and permits, and a furnishing replacement reserve — plus the seasonal operating reserve the monthly model computes. Expense ratios well above long-term-rental norms are the structural expectation, not a red flag in the model.

What is a good occupancy rate for a short-term rental? Whatever the comparable set actually achieves at your intended ADR — the two are a trade-off, so neither is "good" in isolation. RevPAR against comps is the meaningful benchmark.

Are short-term rentals more profitable than long-term rentals? Sometimes — by a margin far smaller than the revenue gap suggests, as the worked comparison shows (2.07× revenue, 1.28× NOI on our illustrative property). The two-column analysis, run on your actual candidate, is the only version of this answer worth having.

Do lenders finance short-term rentals? Some programs do, with STR-specific income conventions (history-based or market-data-based, typically haircut) and their own qualification rules — confirm the specific program's treatment before underwriting to it.

The Model, Built for the Shape of the Business

The STR Pro Forma implements everything this pillar describes: the twelve-month ADR × occupancy revenue engine with the seasonality curve as inputs, the complete hospitality expense stack including platform, management, and furnishing-reserve lines, the LTR comparison column, seasonal cash flow with the computed reserve, and the ADR × occupancy sensitivity grid — fully unlocked, formula-transparent, versioned, with a documented methodology PDF and walkthrough guide. The full model catalog is in the store.

This article is for educational purposes only and does not constitute investment, legal, or tax advice. All figures are illustrative examples, not market data, rate quotes, or revenue projections; STR markets and regulations vary by jurisdiction and change frequently — verify local rules and confirm all figures against current market data. Consult qualified professionals before making investment decisions.