Host Metric Studio
Short-Term Rental Spreadsheet Template: Free Formulas

A good short-term rental spreadsheet needs just three tabs – Bookings, Expenses and a Dashboard – and six numbers calculated the same way every month: occupancy, ADR, RevPAR, net profit, booking window and pacing. Below are the exact columns and copy-paste formulas for Excel and Google Sheets, tested on example data, including stays that cross month-end.
Why bother? Your payout emails tell you how much money landed in your account. They don't tell you whether the property is actually making money, whether this month is better than the same month last year, or whether your nightly rate is too low. I have experience on the numbers side of a short-term rental management company with more than 1,000 homes, and the reports that mattered most were never complicated – they were just consistent.
The three tabs every short-term rental spreadsheet needs
- 1Bookings – one row per reservation.
- 2Expenses – one row per cost.
- 3Dashboard – where you pick a property and a month and the KPIs are calculated.
Keep raw data (bookings, expenses) separate from calculations. If you mix them, sooner or later someone types over a formula.
Columns for the Bookings tab
| Column | Field | Example (invented) |
|---|---|---|
| A | Property | Loft 1 |
| B | Check-in | 2026-06-05 |
| C | Check-out | 2026-06-09 |
| D | Nights | =C2-B2 → 4 |
| E | Accommodation revenue (your nightly prices; no cleaning fee, no taxes) | 480 |
| F | Cleaning fee charged to the guest | 60 |
| G | Platform/channel fees deducted from you | 81 |
| H | Net payout (excluding taxes) | =E2+F2-G2 → 459 |
| I | Booking date | 2026-05-26 |
| J | Status | Confirmed / Cancelled |
| K | Channel | Airbnb / Vrbo / Booking.com / Direct |
Three rules that keep the numbers honest:
- Taxes are not revenue. VAT, tourist or occupancy taxes that you collect and pay on are a pass-through. If the platform collects and remits them for you, they usually never reach your payout; if they do pass through your hands (direct bookings, some channels), record them in an extra column at the end and leave them out of E, F and H.
- Cleaning fees are revenue, but not ADR. They go into gross revenue and net payout (column F), and the cleaning cost goes into Expenses. Keep them out of ADR so short stays don't look more expensive than they are.
- Normalise the status. Exports use different wording ("Canceled by guest", "Cancelled", "Declined"…). Map them all to just Confirmed or Cancelled in column J, otherwise every formula that filters on "Cancelled" silently misses some rows.
Most platforms let you download reservations as a CSV. Each export names its columns differently, so map them to these columns once and keep that mapping in a note. The Google Sheets step-by-step guide shows how to automate the import.
Columns for the Expenses tab
A Date, B Property, C Category (cleaning, laundry, supplies, utilities, rent or mortgage, maintenance, software, insurance, management fee, other), D Amount, E Type (Variable or Fixed), F Notes. Use dropdowns (Data > Data validation) for Property, Category and Type so totals don't break because of a typo.
The free formulas
Create a Dashboard tab. Put the first day of the month you want to analyse in B1 (e.g. 1 June 2026), the property name in B2 and the nights you blocked (owner use, maintenance) in B3.
1. Nights inside the month (stays that cross month-end)
A stay from 29 June to 3 July has two nights in June (29th and 30th) and two in July (1st and 2nd). If you count all four in June, occupancy, ADR and profit are all wrong. Add helper columns to Bookings and fill them down:
| Column | Field | Formula (row 2) |
|---|---|---|
| L | Nights in month | =IF(J2="Cancelled",0,MAX(0,MIN(C2,EOMONTH(Dashboard!$B$1,0)+1)-MAX(B2,Dashboard!$B$1))) |
| M | Accommodation revenue in month | =IF(D2=0,0,E2/D2*L2) |
| N | Booking window (days) | =B2-I2 |
| O | Gross revenue in month | =IF(D2=0,0,(E2+F2)/D2*L2) |
| P | Net payout in month | =IF(D2=0,0,H2/D2*L2) |
Columns M, O and P spread each stay's money evenly over its nights, so revenue, payouts and profit are all assigned to the month in which the nights happen – the same basis as occupancy. (If your accountant books income by check-in or payout date, keep that for tax; for performance analysis, nights-based is the cleaner comparison.)
2. Occupancy rate
Available nights = nights in the month - blocked nights
Occupancy = booked nights / available nights
- Available nights (
B4):=DAY(EOMONTH(B1,0))-B3 - Booked nights (
B5):=SUMIF(Bookings!A:A,B2,Bookings!L:L) - Occupancy (
B6):=IFERROR(B5/B4,0)
Only subtract nights you deliberately took off sale – owner stays and maintenance. A one-night gap that nobody could book because of your minimum stay is still an available night: it went unsold, and the number should show it. (Some hosts also report occupancy over all calendar nights; that's fine as a second figure, as long as you label it.)
3. ADR (average daily rate)
ADR = accommodation revenue in the month / booked nights in the month
- Accommodation revenue (
B7):=SUMIF(Bookings!A:A,B2,Bookings!M:M) - ADR (
B8):=IFERROR(B7/B5,0)
ADR here is calculated before platform fees and without cleaning fees or taxes. Some managers prefer a "net ADR" after commission; either works if you never mix them when comparing months or channels.
4. RevPAR (revenue per available night)
RevPAR = accommodation revenue / available nights (= ADR × occupancy)
RevPAR (B9): =IFERROR(B7/B4,0)
RevPAR is the most useful single number for comparing months or properties because it moves with both price and occupancy. It still ignores costs – a high RevPAR built on many one-night stays can carry heavy cleaning costs – so read it next to net profit.
5. Net profit and margin
Net profit = net payouts for the nights in the month
+ money kept from cancelled bookings
- expenses in the month
Margin = net profit / gross revenue
- Gross revenue (
B10):=SUMIF(Bookings!A:A,B2,Bookings!O:O) - Net payouts (
B11):=SUMIF(Bookings!A:A,B2,Bookings!P:P) - Kept from cancellations (
B12), by original check-in month:=SUMIFS(Bookings!H:H,Bookings!A:A,B2,Bookings!J:J,"Cancelled",Bookings!B:B,">="&B1,Bookings!B:B,"<="&EOMONTH(B1,0))– for a cancelled booking, put in H only what you actually keep (0 if fully refunded). - Expenses (
B13):=SUMIFS(Expenses!D:D,Expenses!B:B,B2,Expenses!A:A,">="&B1,Expenses!A:A,"<="&EOMONTH(B1,0)) - Net profit (
B14):=B11+B12-B13 - Margin (
B15):=IFERROR(B14/(B10+B12),0)
Spread annual bills (insurance, licences) over 12 months and add a monthly maintenance reserve, otherwise one month carries the whole cost. The profit spreadsheet guide goes deeper, including break-even occupancy.
6. Booking window (lead time)
Average booking window for stays checking in this month (B16):
=IFERROR(AVERAGEIFS(Bookings!N:N,Bookings!A:A,B2,Bookings!J:J,"<>Cancelled",Bookings!B:B,">="&B1,Bookings!B:B,"<="&EOMONTH(B1,0)),"-")
Compare it with the same month last year, not with last month – a summer month is naturally booked further ahead than a winter one. A booking window that gets shorter year on year can mean your early prices were too high, that demand softened, or simply that your market books later than it used to; check it against your pacing and your comparable listings before changing prices. The same Google Sheets guide includes a pacing formula (nights on the books vs the same date last year).
A worked example (invented numbers)
🧮 Example only – these figures are invented to show the maths, not market data.
Loft 1, June 2026 (30 days). 2 nights blocked for maintenance → 28 available nights. 6 confirmed stays, all inside June: 21 booked nights, accommodation revenue 2,520, cleaning fees 360 (6 × 60), platform fees 432, so net payouts 2,448. Expenses: 1,730 (cleaning 270, laundry & supplies 90, utilities 180, rent 1,100, software 30, maintenance reserve 60).
- Occupancy = 21 / 28 = 75%
- ADR = 2,520 / 21 = 120
- RevPAR = 2,520 / 28 = 90 (check: 120 × 0.75 = 90)
- Net profit = 2,448 − 1,730 = 718; margin = 718 / 2,880 = 24.9%
- Average booking window: 24 days (stays booked 3, 10, 15, 21, 45 and 50 days ahead)
With these numbers you can answer the questions that matter: Is June better than June last year? Is the rate holding? Is the property paying for itself?
Common mistakes that make your numbers lie
- Counting cancelled bookings as nights – or forgetting the money you kept from them.
- Putting a whole stay in the check-in month. Use the nights-in-month helper.
- Including cleaning fees or taxes in ADR.
- Subtracting unsold gap nights from available nights. Only owner and maintenance blocks come out.
- Treating payouts as profit. Expenses, including fixed ones, come off first.
- Changing the method mid-year. Consistency beats precision.
Next step
Start with one property and one month. Paste last month's bookings, add the expenses and check that occupancy × ADR equals RevPAR – if it doesn't, a nights or status column is wrong. Then copy the results into a 12-month table so you can compare each month with the same month last year. To set a target, read what a good occupancy rate is for your property.
FAQ
Should cleaning fees count as revenue?
Yes – include them in gross revenue and net payout, and record the cleaning cost as an expense. Just keep them out of ADR.
Should owner stays count as available nights?
No. Nights you blocked for yourself or for maintenance weren't for sale, so subtract them. Nights that simply didn't sell stay in.
Excel or Google Sheets?
All formulas on this page work in both. Google Sheets is easier to share with a co-host; Excel is handy offline.
How do I handle a stay that crosses into the next month?
Split it by nights with the "nights in month" helper column, and spread its revenue the same way.
If you'd rather not build it yourself: I also made a ready-made version, the STR Income & Expense Tracker (Excel + Google Sheets, up to 10 properties, CSV import, dashboard, pacing and owner statement) – $19 at https://emprendemos.gumroad.com/l/str-income-expense-tracker. The free formulas above are all you need to get started, though.
Not affiliated with or endorsed by Airbnb, Vrbo or Booking.com. Platform names are used only to describe their data exports. Example figures are invented. This article is not tax or financial advice.
Short on time? I can set it up for you.
If you'd rather not build this yourself, I set up the income and KPI tracker for your properties, using your own bookings and costs, so you can start reading your numbers right away. See the service on Upwork.