Host Metric Studio

Short-Term Rental Spreadsheet Template: Free Formulas

Published 2026-10-27

Short-Term Rental Spreadsheet Template – Host Metric Studio

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

  1. 1Bookings – one row per reservation.
  2. 2Expenses – one row per cost.
  3. 3Dashboard – where you pick a property and a month and the KPIs are calculated.
💡 Pro tip

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:

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

Formula
Available nights = nights in the month - blocked nights
Occupancy        = booked nights / available nights

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)

Formula
ADR = accommodation revenue in the month / booked nights in the month

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)

Formula
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

Formula
Net profit = net payouts for the nights in the month
           + money kept from cancelled bookings
           - expenses in the month
Margin     = net profit / gross revenue

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):

Google Sheets / Excel
=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)
75%
occupancy (21 of 28 nights)
90
RevPAR (120 × 0.75)
718
net profit, 24.9% margin

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

⚠️ Common mistakes
  • 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.