Host Metric Studio
Airbnb Google Sheets Template: Build a Free Host Tracker

You can build a complete Airbnb host tracker in Google Sheets in about an hour: import your reservations CSV into a raw tab, clean it into a fixed Bookings tab, log expenses, and let a small Dashboard calculate occupancy, ADR, RevPAR, net profit, booking window and pacing. This guide gives you every step and formula; all of them use built-in functions – no add-ons, no scripts, no logins to any platform.
Google Sheets is a good home for a host tracker: it's free, works on your phone, and you can share it with a co-host or cleaner without emailing files around. The definitions used here are explained in the short-term rental spreadsheet template guide; this article focuses on the build.
Step 1 Export your reservations (all of them)
Most booking platforms let hosts download reservations as a CSV from the reservations or earnings area of the host dashboard. Two things matter:
- Export a long date range, including upcoming stays – for example from 1 January last year to the end of next year. You need last year's data for year-on-year comparisons and future bookings for pacing. A payouts-only report usually lists money already paid, not stays that haven't happened yet.
- Set the spreadsheet locale first (File > Settings > Locale) to match the date format of the export. If dates import as text (they stay left-aligned), every date formula below will fail.
If you also take bookings on Vrbo, Booking.com or directly, export or type those into the same structure. That's what the Channel column is for.
Step 2 Import the CSV into a "Raw" tab
File > Import > Upload, choose your file, and select Insert new sheet(s). Rename the tab Raw. Never edit it by hand. Each time you update, replace it with a new full-range export (Import > Replace current sheet), so cancellations and changes in older bookings are picked up too.
Step 3 Build a clean "Bookings" tab
Exports use different column names ("Start date" vs "Check-in", and so on). Create a Bookings tab with your own fixed headers in row 1:
A Property · B Check-in · C Check-out · D Nights · E Accommodation revenue · F Cleaning fee · G Platform fees · H Net payout · I Booking date · J Status · K Channel · L Nights in month · M Accommodation revenue in month · N Booking window · O Gross revenue in month · P Net payout in month
Pull the raw data in with formulas, not by typing next to it. For example, if Raw has the listing name in column E, check-in in C and check-out in D, put this in A2 (headers = 0, because your own headers are already in row 1):
=QUERY(Raw!A2:Z,"select E, C, D where C is not null",0)
Map E–K the same way (one QUERY per block of adjacent columns, or =Raw!X2:X references). Important: don't type values manually next to QUERY output. When the export changes order, the rows move and your typed values end up on the wrong booking. Anything you need to add by hand (for example, direct bookings) goes in rows below, or in a separate tab that you stack with {range1; range2}.
Normalise the status in column J so cancellations are always spelled the same, whatever the export says:
=ARRAYFORMULA(IF(Raw!H2:H="",,IF(REGEXMATCH(LOWER(Raw!H2:H),"cancel|declin"),"Cancelled","Confirmed")))
(Change Raw!H2:H to wherever your export keeps the status.) Accommodation revenue (E) should exclude cleaning fees and any taxes; if your export only gives a total, subtract the cleaning fee and taxes in a formula.
Then add the calculated columns. ARRAYFORMULA fills a whole column automatically, so new rows calculate without dragging (Google Sheets only – in Excel, use the row formulas and fill down):
| Column | Formula (row 2) |
|---|---|
| D Nights | =ARRAYFORMULA(IF(B2:B="",,C2:C-B2:B)) |
| N Booking window | =ARRAYFORMULA(IF((B2:B="")+(I2:I=""),,B2:B-I2:I)) |
| M Accommodation revenue in month | =ARRAYFORMULA(IF(D2:D="",,IF(D2:D=0,0,E2:E/D2:D*L2:L))) |
| O Gross revenue in month | =ARRAYFORMULA(IF(D2:D="",,IF(D2:D=0,0,(E2:E+F2:F)/D2:D*L2:L))) |
| P Net payout in month | =ARRAYFORMULA(IF(D2:D="",,IF(D2:D=0,0,H2:H/D2:D*L2:L))) |
L – Nights in month. A stay from 29 June to 3 July has two nights in June and two in July. With the month's first day in Dashboard!B1, put this in L2 and drag it down (works in Sheets and Excel):
=IF(J2="Cancelled",0,MAX(0,MIN(C2,EOMONTH(Dashboard!$B$1,0)+1)-MAX(B2,Dashboard!$B$1)))
Or, as a single array formula in Sheets (MIN/MAX don't work row by row inside ARRAYFORMULA, so it uses IF instead):
=ARRAYFORMULA(LET(s,Dashboard!$B$1, e,EOMONTH(s,0)+1,
n, IF(C2:C<e,C2:C,e) - IF(B2:B>s,B2:B,s),
IF(B2:B="",, IF(J2:J="Cancelled",0, IF(n>0,n,0)))))
Step 4 Expenses tab and dropdowns
Expenses columns: A Date · B Property · C Category · D Amount · E Type (Variable/Fixed) · F Notes.
Select the Property column and use Data > Data validation > Dropdown (from a range) pointing at a list of your property names. Do the same for Channel, Category and Type. A property typed two different ways ("Loft 1" and "Loft1") is one of the most common reasons totals don't add up.
Step 5 The dashboard formulas
Create a Dashboard tab. Put the first day of the month in B1 (e.g. 1 June 2026), the property in B2 and blocked nights (owner use, maintenance) in B3.
| Cell | KPI | Formula |
|---|---|---|
| B4 | Available nights | =DAY(EOMONTH(B1,0))-B3 |
| B5 | Booked nights | =SUMIF(Bookings!A:A,B2,Bookings!L:L) |
| B6 | Occupancy | =IFERROR(B5/B4,0) |
| B7 | Accommodation revenue | =SUMIF(Bookings!A:A,B2,Bookings!M:M) |
| B8 | ADR | =IFERROR(B7/B5,0) |
| B9 | RevPAR | =IFERROR(B7/B4,0) |
| B10 | Gross revenue | =SUMIF(Bookings!A:A,B2,Bookings!O:O) |
| B11 | Net payouts | =SUMIF(Bookings!A:A,B2,Bookings!P:P) |
| B12 | Kept from cancellations | =SUMIFS(Bookings!H:H,Bookings!A:A,B2,Bookings!J:J,"Cancelled",Bookings!B:B,">="&B1,Bookings!B:B,"<="&EOMONTH(B1,0)) |
| B13 | Expenses | =SUMIFS(Expenses!D:D,Expenses!B:B,B2,Expenses!A:A,">="&B1,Expenses!A:A,"<="&EOMONTH(B1,0)) |
| B14 | Net profit | =B11+B12-B13 |
| B15 | Margin | =IFERROR(B14/(B10+B12),0) |
| B16 | Avg booking window (days) | =IFERROR(AVERAGEIFS(Bookings!N:N,Bookings!A:A,B2,Bookings!J:J,"<>Cancelled",Bookings!B:B,">="&B1,Bookings!B:B,"<="&EOMONTH(B1,0)),"-") |
Revenue, payouts and profit are spread by nights, so they sit in the same month as the occupancy they belong to. For cancelled bookings, column H should hold only what you actually keep (0 if fully refunded).
Format occupancy and margin as percentages. Add conditional formatting that turns RevPAR red when it's below the same month last year – comparing with last month mostly shows seasonality.
🧮 Example only – invented numbers.
Loft 1, June 2026, 2 blocked nights: available 28, booked 21, occupancy 75%, ADR 120, RevPAR 90, gross revenue 2,880, net payouts 2,448, expenses 1,730, net profit 718, margin 24.9%, average booking window 24 days. These are the same invented figures used across this site's guides.
Step 6 Booking window mix
How far ahead guests book for the selected month and property:
Last-minute (0–6 days):
=COUNTIFS(Bookings!A:A,B2,Bookings!J:J,"<>Cancelled",Bookings!B:B,">="&B1,Bookings!B:B,"<="&EOMONTH(B1,0),Bookings!N:N,">=0",Bookings!N:N,"<7")
1–4 weeks: same formula with ">=7" and "<30"
30+ days: same formula with ">=30" (and no upper limit)
🧮 Example only – invented numbers.
June: 6 stays booked 3, 10, 15, 21, 45 and 50 days ahead → 1 last-minute, 3 within 1–4 weeks, 2 at 30+ days; average 24 days.
How to use it: your last-minute window is where unsold nights go cheaply or not at all. If most of a month's bookings arrive inside 7 days, that's when gap-night discounts and shorter minimum stays make sense; reductions far in advance mostly give money away to guests who would have booked anyway.
Step 7 Pacing – are you ahead of last year?
Pacing compares what's already booked for a future month with what was booked for the same month at the same point last year. It's the earliest signal that a price is wrong. On the Dashboard:
| Cell | This year (column E) | Last year (column F) |
|---|---|---|
| Row 1 – first day of target month | e.g. 1 July 2026 | =EDATE(E1,-12) |
| Row 2 – "as of" date | =TODAY() |
=EDATE(E2,-12) |
| Row 3 – day after month-end | =EOMONTH(E1,0)+1 |
=EOMONTH(F1,0)+1 |
| Row 4 – nights on the books | formula below | same formula, copied to F4 |
=SUMPRODUCT((Bookings!$A$2:$A$2000=$B$2)*(Bookings!$J$2:$J$2000<>"Cancelled")*(Bookings!$I$2:$I$2000<=E2)*(Bookings!$B$2:$B$2000<E3)*(Bookings!$C$2:$C$2000>E1)*((Bookings!$C$2:$C$2000<E3)*Bookings!$C$2:$C$2000+(Bookings!$C$2:$C$2000>=E3)*E3-(Bookings!$B$2:$B$2000>E1)*Bookings!$B$2:$B$2000-(Bookings!$B$2:$B$2000<=E1)*E1))
Pace vs last year (E5): =IFERROR(E4/F4-1,"n/a"). Extend the 2000 row limit if you have more bookings.
🧮 Example only – invented numbers.
On 15 June 2026, Loft 1 has 4 nights booked for July. On 15 June 2025 it had 8 nights booked for July 2025 → pace −50%.
Read pace together with ADR: behind pace at a higher ADR may be a deliberate trade-off; behind pace at the same ADR, with comparable listings filling up, usually means you're priced above the market for those dates. One caveat: bookings that were later cancelled are excluded from last year's figure, so last year's pace is slightly understated.
Step 8 Share it safely
- Share with a co-host as Editor, with an owner as Viewer.
- Protect the formula columns: Data > Protect sheets and ranges.
- Before experimenting, File > Make a copy.
- Keep guest personal data out of the sheet – a reservation code is enough.
A monthly routine (15 minutes)
- 1Download a full-range reservations CSV and replace the Raw tab.
- 2Add expenses from receipts or your bank export.
- 3Change the month in
Dashboard!B1and copy occupancy, ADR, RevPAR and net profit into a 12-month table. - 4Compare with the same month last year, then check pacing for the next three months.
For a full profit-and-loss view and break-even occupancy, see the Airbnb profit spreadsheet guide. To turn these numbers into a monthly target, read what a good occupancy rate is for Airbnb.
FAQ
Can Google Sheets connect directly to Airbnb?
Not with the method on this page – you download a CSV and import it. That avoids sharing any login.
Why do my dates not work after importing?
They probably came in as text because the spreadsheet locale doesn't match the export's date format. Change the locale and re-import.
Do these formulas work in Excel?
Everything except ARRAYFORMULA, QUERY and REGEXMATCH. In Excel, use the row-by-row versions and fill them down.
How many bookings can the sheet handle?
Thousands of rows is fine for a handful of properties; keep whole-column references in SUMIFs and fixed ranges in SUMPRODUCT.
If you'd rather skip the build: there's a ready-made version, the STR Income & Expense Tracker for Google Sheets and Excel (CSV import with column mapping, up to 10 properties, dashboard, pacing and owner statement) – $19 at https://emprendemos.gumroad.com/l/str-income-expense-tracker. The steps above work fine on their own, too.
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.