The job
What an airbnb analysis spreadsheet actually does
An airbnb analysis spreadsheet is an underwriting tool, not an operations tracker. The two get conflated and the conflation costs money. The analysis model decides whether you buy the property; the operations spreadsheet runs the property once you own it.
The analysis question has one output: cash-on-cash return on the capital invested. Everything else (cap rate, IRR, payback period) is a derivative of that single number against the time horizon. Get cash-on-cash right and the rest follows.
The model
The four-tab structure
Tab 1, Inputs
Purchase price, down payment, mortgage rate, mortgage term, ADR, occupancy rate, seasonality factors, operating cost ratio, property tax, insurance, and a capital reserve as percent of revenue. One screen. Highlighted cells the user edits.
The inputs tab is the only one a buyer should touch. Everything else flows from it. Lock the other tabs to prevent accidental edits.
Tab 2, Revenue
Monthly grid. Each month: nights available x occupancy x ADR x seasonality factor = monthly gross. Sum to annual. The seasonality column is what separates a serious model from a back-of-napkin guess.
Pull seasonality factors from AirDNA, Mashvisor, or by manually comping the market’s busy and slow months. Twelve numbers averaging to 1.0; peak months might run 1.3-1.6, slow months 0.5-0.7.
Tab 3, Costs
Operating: cleaning per stay (estimate stays from occupancy and average stay length), utilities, internet, supplies, management or self-management hours costed, insurance, property tax, capital reserve. Debt service: mortgage principal and interest by month. Sum to annual.
The line most underwriters skip is the capital reserve. Set five to eight percent of gross revenue aside for the broken dishwasher, the roof that fails, the HVAC that needs replacing. Models without a reserve assumption blow up in year three.
Tab 4, Returns
Cash-on-cash = annual cashflow / cash invested. Cap rate = NOI / purchase price. Payback = cash invested / annual cashflow. Five-year IRR if you want the long view. These are the four numbers a co-investor or a lender will ask for.
Tab 5, Sensitivity
The tab no host should skip. Re-run the model with ADR down ten percent, occupancy down ten percent, and both at once. If the cash-on-cash holds up at the both-down case, the deal is real. If it collapses, the deal only works in the optimistic case.
Underwrite at the trailing-twelve-month median for the comp set, not the peak month. The peak month is what brokers show; the median is what the bank account sees.
Common mistakes
Three modelling mistakes that show up in almost every first-time analysis
- Optimistic ADR. Buyers pull peak-month numbers from comps and use them as the year-round average. Use trailing-twelve-month median; if you can’t get TTM, divide peak by 1.3 as a rough adjustment.
- Missing capital reserve. Without five to eight percent reserved, year three is where the dishwasher and the HVAC both fail and the deal goes negative for a quarter.
- Self-management hours uncosted. A self-managed unit at fifteen hours a month is real operating cost. Cost it at $25-50 per hour even if you don’t pay yourself; otherwise the model overstates returns.
How to comp
Where to find honest ADR and occupancy numbers
| Source | Best for | Caveat |
|---|---|---|
| AirDNA | Market-level ADR and occupancy, sub-market comps | $40-100/month; data is averaged and smoothed |
| Mashvisor | Comparable Airbnb listings, neighbourhood drill-down | Subscription; occupancy estimates can run optimistic |
| Direct scraping of comp listings | Free; rough monthly ADR via blocked-out calendar dates | Time-intensive; doesn’t give occupancy |
| Local hosts (talk to them) | The ground truth nobody else has | Sample size of one; biased toward success stories |
The combination that works: AirDNA for the market median, Mashvisor for the sub-market comp set, and one phone call to a local host to gut-check the numbers. Three sources beat any single one.
We’re working on a full Airbnb bundle. The closest fit today is our Airbnb spreadsheet template, which runs the operations side of this stack. The analysis model and the operations model are different files; the spreadsheet template covers the second.
If you’ve underwritten a deal recently and want a second pair of eyes on the model, drop the sheet in the comments. Investor math holds up better when more eyes see it.
FAQ
Common questions, answered briefly
What’s the difference between an airbnb analysis spreadsheet and an operations spreadsheet?
Analysis underwrites a property you don’t own yet, modelling forward returns. Operations tracks a property you run today, recording actuals. Different files, different math.
What ADR should I use in an airbnb analysis spreadsheet?
Trailing-twelve-month median for the comp set, not the peak month. Peak-month numbers from brokers consistently overstate annual returns by twenty to thirty percent.
What’s a reasonable cash-on-cash return for an airbnb investment?
Eight to twelve percent is what most underwriters look for. Below eight, the deal competes poorly with passive index returns. Above twelve, double-check the assumptions before getting excited.
How do I model seasonality in an airbnb analysis spreadsheet?
Twelve monthly factors that average to 1.0. Peak months 1.3-1.6, shoulder months 0.9-1.1, slow months 0.5-0.7. Pull from AirDNA or comp listings; never assume flat across the year.
Should I cost self-management in an airbnb deal analysis?
Yes. Self-managed units take ten to twenty hours a month. Cost the time at $25-50/hour even if you’re not paying yourself. Otherwise the model overstates returns and you compare badly against managed deals.
Got a question we didn’t cover? Drop it in the comments and we’ll either answer it inline or fold it into the next update of this guide.
People also ask