Building a Short Term Rental Analysis Spreadsheet from Scratch
You spend more time building the tool than you do analyzing properties when you start. That is normal. Most people begin with a blank sheet, copy a revenue projection from somewhere online, and then realize two weeks later that their vacancy assumptions are completely disconnected from the actual market they are looking at. I built three versions of this thing before I stopped trying to make it look pretty and focused on making it actually useful. A Short Term Rental Analysis Spreadsheet needs to answer one question: does this property make money after every expense you actually have, not just the ones you expect. The simplest layout has five sections. Input data on the left, monthly projections in the middle, annual summary at the bottom, sensitivity analysis on another tab, and a quick property comparison sheet if you are evaluating multiple units. Start with the property input tab. You need these fields: purchase price, down payment percentage, interest rate, loan term, monthly HOA, estimated insurance, property management fee percentage, cleaning fee per booking, nightly rate, occupancy rate, seasonal adjustments, utilities, maintenance reserve, property tax, vacancy buffer, and platform fees. That last one is Airbnb's service fee plus the host cut, which varies by region and listing type. I typically assume 14 percent for entire homes and 20 percent for shared rooms because the data backs that out.
Don't separate gross income from net income early on. I used to build them as different rows so I could see the revenue line clearly. What actually happened was I stopped checking the net row because I got comfortable with the gross number and missed how fast expenses ate the profit. Put everything in one pass. Revenue minus every single line item equals net. Simple.
Occupancy Is Where Everyone Messes Up
Occupancy drives your entire model. Most beginners pull a number from Airbnb's official statistics page and use it directly. Those numbers are city-wide averages across every neighborhood, every property type, and every season. They are useless for a specific street. I learned this with a two-bedroom condo in Nashville near the Gulch area. The city-wide occupancy rate for that market was sitting around 62 percent. I plugged that in, ran the numbers, and the deal looked fine. Then I spent a weekend pulling actual listing data from the three nearest competing properties using AirDNA. Their average occupancy was 47 percent because the building had a homeowners association that restricted short-term rentals to 90 days per year. The official market data did not reflect that restriction at all. I rewrote the spreadsheet to pull neighborhood-level data and added a hard maximum cap based on any local regulatory constraints. Now I verify occupancy from at least three real listings in the immediate area, not from a market report. If the restrictions exist, I reduce the allowable nights and recalculate. That Nashville deal turned into a cash flow negative once I applied the 90-day limit instead of a full 365. Seasonality matters too. A beach property in July does not need the same occupancy assumption as the same property in November. Build a monthly occupancy row where you can adjust each month individually. Most tools just let you set one annual average and call it done. That works for rough screening but it fails when you are underwriting a deal where winter revenue covers almost nothing.
Get the Full Details
Expense Categories People Forget
The big ones everyone remembers are mortgage, insurance, taxes, and property management. The ones that destroy returns quietly are capital expenditure reserves, advertising costs, furniture replacement, and turnover supply restocking. A standard rule of thumb is setting aside 8 percent of gross revenue for CapEx. That covers HVAC servicing, water heater replacement, appliance lifespan, paint jobs, linens, and carpet cleaning. On a $40,000 annual revenue property, that is $3,200. You can argue it is too high or too low depending on the asset condition, but ignoring it entirely is how you end up writing a check for a new roof in month fourteen. Platform algorithm changes also affect your effective income without showing up in any spreadsheet row. I had a cabin listing in Smoky Mountains that saw its booking volume drop 30 percent after Airbnb adjusted its search ranking algorithm in 2023. The property itself had not changed. The guest reviews were good. The photos were fine. The platform just stopped pushing the listing to the top of results the way it used to. I do not have a line item for algorithm risk in my current model because I cannot quantify it, but I do note it in the assumptions section so I remember that the occupancy number is a moving target, not a permanent state.
The Comparison Tab
Once you have one model working, duplicate the sheet for every other property you are evaluating. Keep the input cells color-coded in yellow so you know exactly what you are changing between properties. Do not rename tabs to individual addresses. Use property ID or address number and keep the structure identical. When you are comparing ten deals, consistency matters more than anything else. The key metric to compare across all tabs is cash-on-cash return. It tells you what percentage of your actual deployed capital the property generates annually. Net operating income divided by total cash invested. Everything else is secondary when you are choosing between similar properties. Cap rate is useful for valuing the real estate itself but it ignores leverage, which is the whole point of buying with a mortgage in this market.
Limitations to Accept
This spreadsheet will not predict whether a property will actually book. It will only tell you what happens if the occupancy and rate assumptions hold. If local regulations change, if a competitor opens across the street, if your management company raises fees, none of that is captured automatically. You have to update the inputs manually and rerun the model. I have seen people treat a single spreadsheet projection as proof a deal works. It is not proof. It is a scenario based on assumptions you made yesterday. For short-term rental analysis, the spreadsheet is a screening tool, not a decision maker. Use it to eliminate bad deals quickly. Then validate the promising ones with real comparable data before you write any offer. That is the process that has worked for me across dozens of purchases and failures. I still get surprised sometimes. The spreadsheet just makes sure I am surprised by something real instead of something I overlooked in the model.
