Short-Term Rental Calculator Spreadsheet: Airbnb Cash Flow, Break-Even Occupancy and Nightly Rate (Excel & Google Sheets)

Short on time? The Short-Term Rental Analyzer ($19) is the finished, tested version of what this tutorial builds.

Most short-term rental deals look great in a listing spreadsheet because the listing spreadsheet multiplies a nightly rate by 365 and stops there. The real deal is smaller: fewer nights are available than you think, every stay costs a cleaning, the platform takes its fee off the top, and the mortgage doesn't care how slow February was. The most useful single number is break-even occupancy: the share of available nights you must book just to cover every cost and the mortgage.

This tutorial builds a one-tab analyzer that works out gross revenue, NOI, cash flow, cash-on-cash, cap rate, DSCR, RevPAR, break-even occupancy and break-even nightly rate, plus a small what-if grid. It works the same in Excel, Google Sheets and LibreOffice. We tested every formula below by building a workbook with these exact formulas, recalculating it in LibreOffice, and comparing every cell to the worked example and to an independent Python model (break-even values solved numerically), plus 40 randomized deals.

1. The inputs

Make a tab called Analyzer. Labels go in column A, your numbers in column B:

A few of these trip people up:

• ADR is what guests pay per night before cleaning fees and lodging taxes. Lodging and occupancy taxes you collect and pass on to the city or state are not your revenue, so leave them out.
• Occupancy is booked nights divided by available nights, not by 365. Blocked nights are owner stays and maintenance days that can't be booked.
• Average stay sets how many turnovers you pay for. Shorter stays mean more cleaning fees collected, but also more cleanings paid and more wear.
• Platform host fee: on Airbnb's single-fee structure, the whole service fee comes out of the host payout, and most hosts pay 15.5%. On the older split-fee structure, most hosts pay 3% and the guest pays the rest. Airbnb is moving all hosts to the single fee, and it's mandatory for hosts who use property management software (Airbnb Help Center: service fees). The fee applies to the nightly price plus fees you charge, such as the cleaning fee, which is how the formulas below use it.
• Fixed costs are the monthly bills that don't change with bookings: property tax, STR insurance, utilities, internet, HOA, supplies and software.

2. The formulas

Put these in column B, below the inputs:

B19:  =365-B10
B20:  =B19*B9
B21:  =IF(B11>0,B20/B11,0)
B22:  =B20*B8+B21*B12
B23:  =B22*(B13+B15+B16)+B21*B14+12*B17
B24:  =B22-B23
B25:  =IF(B2*(1-B3)=0,0,IF(B4=0,B2*(1-B3)/(B5*12),PMT(B4/12,B5*12,-B2*(1-B3))))
B26:  =B24-12*B25
B27:  =B2*B3+B6+B7
B28:  =IF(B27=0,"",B26/B27)
B29:  =IF(B2=0,"",B24/B2)
B30:  =IF(B25=0,"",B24/(12*B25))
B31:  =IF(B19=0,0,B20*B8/B19)
B32:  =(B8+IF(B11>0,B12/B11,0))*(1-B13-B15-B16)-IF(B11>0,B14/B11,0)
B33:  =IF(OR(B32<=0,B19=0),"Never",12*(B17+B25)/(B19*B32))
B34:  =IF(OR(B20=0,B13+B15+B16>=1),"Never",(12*(B17+B25)+B21*B14)/(B20*(1-B13-B15-B16))-IF(B11>0,B12/B11,0))

Label them in column A: Available nights, Booked nights, Stays (turnovers), Gross revenue, Operating expenses, NOI, Mortgage P&I / month, Annual cash flow, Total cash invested, Cash-on-cash, Cap rate, DSCR, RevPAR, Kept per booked night, Break-even occupancy, Break-even nightly rate. Format B28:B30 and B33 as percentages (B30 as a plain number). What each one does:

• B19–B21, nights and stays: available nights are 365 minus blocked nights; booked nights are available nights × occupancy; stays are booked nights ÷ average stay.
• B22, Gross revenue: nightly revenue plus the cleaning fees guests pay.
• B23, Operating expenses: the platform fee, management and maintenance/CapEx reserve as a share of gross revenue, plus what you pay the cleaner for every turnover, plus twelve months of fixed costs. The mortgage is not an operating expense.
• B24, NOI: gross revenue minus operating expenses.
• B25, Mortgage P&I: the standard monthly payment. The IFs handle a cash purchase (100% down, no loan) and a 0% loan, where PMT isn't needed.
• B26, Annual cash flow: NOI minus twelve mortgage payments. Divide by 12 for a monthly figure.
• B27–B30, returns: cash-on-cash is cash flow ÷ the cash you put in (down payment, closing and furnishing); cap rate is NOI ÷ price; DSCR is NOI ÷ annual debt service. Below 1.0 means NOI doesn't cover the mortgage.
• B31, RevPAR: nightly revenue per available night, the number to compare against market data that reports RevPAR.
• B32, Kept per booked night: what one booked night is worth after every cost that scales with bookings: the rate plus its share of the cleaning fee, less the percentage fees, less its share of the cleaning cost.
• B33, Break-even occupancy: the fixed costs and mortgage for a year, divided by what each available night would earn if it were booked. It's the occupancy at which B26 is exactly 0, with everything else held where it is. "Never" means each booked night loses money; a result above 100% means the deal can't break even at this nightly rate.
• B34, Break-even nightly rate: the same idea turned around: the ADR at which cash flow is 0 at your occupancy.

3. A what-if grid

One estimate of occupancy and rate isn't enough; you want to see how wrong you can be. Make a tab called WhatIf with nightly rates across row 2 (B2:D2) and occupancies down column A (A3:A5), then put this in B3 and fill it across and down:

WhatIf!B3:  =Analyzer!$B$19*$A3*((B$2+IF(Analyzer!$B$11>0,Analyzer!$B$12/Analyzer!$B$11,0))*(1-Analyzer!$B$13-Analyzer!$B$15-Analyzer!$B$16)-IF(Analyzer!$B$11>0,Analyzer!$B$14/Analyzer!$B$11,0))-12*(Analyzer!$B$17+Analyzer!$B$25)

Each cell is the annual cash flow at that occupancy and that nightly rate, with every other input taken from the Analyzer. Add conditional formatting (red when less than 0, green when greater than 0) and the boundary between the colors is your margin of safety. You can extend it to as many rows and columns as you like.

Worked example

A $350,000 property, 25% down at 7.00% for 30 years, $10,500 closing costs and $25,000 to furnish ($123,000 cash in). $185 a night at 62% occupancy, 14 blocked nights, 3.2-night average stay, $95 cleaning fee charged and $85 paid to the cleaner, a 15.5% host fee, self-managed, 9% for maintenance and CapEx, and $920 a month in fixed costs:

What-if grid (annual cash flow):

What the example shows:

1. A 5.3% cap rate can still lose money. NOI is positive, but the mortgage is $1,746.42 a month, so the deal is about $209 a month short and DSCR is below 1.
2. Break-even is the number to check against comps. At $185 a night you need 67.3% occupancy. At 62% occupancy you need $200.24 a night. Before making an offer, look at whether comparable listings near you actually book at those levels.
3. The host fee structure is worth $5,840 a year here. The same deal with a 3% host fee instead of 15.5% shows $3,336 of annual cash flow instead of −$2,504. Use the fee you'll really pay.
4. Hiring a manager changes everything. Add 20% management and annual cash flow drops to −$11,848, with break-even occupancy at 98.5%. If you won't self-manage, underwrite with the management fee from the start.

Common mistakes

• Occupancy against 365 nights. If you block 14 nights for yourself, 62% occupancy is 217.6 booked nights, not 226.3.
• Counting lodging taxes as revenue. Taxes you collect and remit aren't yours. Enter ADR before taxes.
• Forgetting the cleaner. The cleaning fee you charge is revenue; the cleaning you pay for is an expense on every turnover. Shorter stays raise both.
• Using last year's platform fee. Check your platform's current fee page and the structure your account is on.
• Ignoring personal use. Under IRS rules, a property you also use personally counts as a home if your personal use is more than the greater of 14 days or 10% of the days it's rented at a fair price, which changes how expenses are deducted (IRS Publication 527). Talk to a tax professional before you count on deductions.
• Skipping local rules. Many cities and HOAs restrict, license or tax short-term rentals. A deal that pencils out but can't get a permit is not a deal.

Related tutorials

• Cap rate vs. cash-on-cash vs. DSCR: a worked rental example.
• Rental property cash flow spreadsheet: monthly NOI and cash flow per property.
• How to track rental income and expenses by Schedule E line.

Want it done for you?

The Short-Term Rental Deal Analyzer is a finished workbook built on the same math: three scenarios side by side (conservative, base and optimistic, or three different properties), itemized monthly bills, every result above including break-even occupancy, and a 90-cell sensitivity grid of annual cash flow at occupancy 40–85% and nightly rate −$40 to +$40, colored red and green. It comes with a filled-in $350,000 sample and a PDF guide, and works in Excel, Google Sheets and LibreOffice with no macros.

Not tax, legal or investment advice. Short-term rental rules, taxes and fees depend on your city, state, platform and lender. Check them before you buy.

Free tool: Free Rental Property Calculator: Cash Flow, Cap Rate, Cash-on-Cash, DSCR

Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.