Host Guides › Short-Term Rental Expense Tracker Spreadsheet: How to Set One Up
Short-Term Rental Expense Tracker Spreadsheet: How to Set One Up
A good short-term rental spreadsheet answers three questions in under a minute: What did I earn? What did I spend? What did I actually keep, per property? Most hosts' spreadsheets can't, because they were built as a single long list that grew every month until nobody wanted to open it.
This guide shows you how to set up a tracker that stays usable: which tabs to create, which columns each needs, the expense categories to use, and the exact formulas (they work in both Excel and Google Sheets). For the habits that keep it accurate all year, such as receipts, mileage and a monthly routine, see our companion guide on how to track short-term rental expenses for taxes.
The structure: five tabs
Keep raw data and reports separate. You type into the logs. Everything else calculates.
| Tab | What goes in it | You type in it? |
|---|---|---|
| Setup | Property names, year, available nights per property, category list | Once a year |
| Bookings | One row per reservation | Yes, weekly |
| Expenses | One row per expense or receipt | Yes, weekly |
| Mileage | One row per business trip | Yes, as you drive |
| Summary | Monthly totals, per-property profit, occupancy, ADR, RevPAR | No, formulas only |
The rule that makes this work: one row per event, never one row per month. Monthly rows hide detail you'll want later, like which property a repair belongs to, or whether a payout included a cleaning fee.
Tab 1: Setup
List each property once, with a short name you'll use everywhere ("Lake Cabin," "Downtown 2B"). Then, in column B, add available nights for the year: 365 minus any nights you blocked for personal use or renovation. You'll need it for occupancy and RevPAR.
Also put your expense category list here and turn the Category column on the Expenses tab into a dropdown from this list (Data → Data validation in both Excel and Google Sheets). Dropdowns stop "Cleaning," "cleaning " and "Cleaner" from becoming three different categories.
Tab 2: Bookings
| Column | Example | Notes |
|---|---|---|
| A: Check-in | 2026-10-03 | Use real dates, not text |
| B: Check-out | 2026-10-06 | |
| C: Property | Lake Cabin | Dropdown from Setup |
| D: Platform | Airbnb | Airbnb, Vrbo, Booking.com, Direct |
| E: Nights | =B2-A2 | Format as a number |
| F: Accommodation revenue | 540.00 | Nightly charges only |
| G: Cleaning fee charged | 150.00 | |
| H: Other guest fees | 0.00 | Pet fee, extra guest fee |
| I: Platform fees | -106.95 | Enter as a negative number |
| J: Taxes collected | 0.00 | Only if you remit lodging tax yourself |
| K: Payout | =F2+G2+H2+I2+J2 | Should match the deposit |
| L: Payout date | 2026-10-04 | For bank reconciliation |
Record the gross amounts and the fees, not just the payout. Platform fees are a business expense, and you want them visible. On Airbnb, the single host service fee applies to the booking subtotal, which includes the cleaning fee (Airbnb: simplifying service fees), so each reservation's fee line will differ.
Tab 3: Expenses
| Column | Example |
|---|---|
| A: Date | 2026-10-08 |
| B: Property | Lake Cabin (or "All" for shared costs) |
| C: Category | Cleaning and maintenance |
| D: Vendor | Sparkle Cleaning Co. |
| E: Amount | 95.00 |
| F: Paid with | Business card |
| G: Receipt? | Yes / No |
| H: Notes | Turnover after the Oct 3–6 stay |
The Receipt? column is the one hosts skip and later regret. Add conditional formatting so any row marked "No" turns red, and clear the red at your monthly review.
Expense categories that match the tax form
US hosts who report rental income on Schedule E (Form 1040) will find it easiest to use categories that mirror the expense lines in Part I of that form. Those lines are: advertising; auto and travel; cleaning and maintenance; commissions; insurance; legal and other professional fees; management fees; mortgage interest paid to banks; other interest; repairs; supplies; taxes; utilities; depreciation; and other (IRS Schedule E instructions).
A practical host version:
- Advertising (listing photos, direct-booking site)
- Auto and travel (see the Mileage tab)
- Cleaning and maintenance (cleaners, laundry, pest control, landscaping)
- Commissions and platform fees
- Insurance
- Legal and professional fees (tax preparer, permit filings)
- Management fees (co-host or property manager)
- Mortgage interest
- Repairs
- Supplies (toiletries, coffee, linens, small items)
- Taxes (property tax, lodging tax you remit)
- Utilities (power, water, internet, streaming)
- Software and subscriptions
- Furnishings and equipment (ask your preparer how to treat larger purchases)
- Other
Not every host reports on Schedule E. IRS Publication 527 explains that if you provide substantial services primarily for your guests' convenience, such as regular cleaning during the stay or meals, the income may belong on Schedule C instead (IRS Publication 527). The spreadsheet works either way. Ask a tax professional which form applies to you.
Tab 4: Mileage
Columns: Date, Property, From, To, Purpose, Miles. Then a total. The IRS standard mileage rate for business use in 2026 is 72.5 cents per mile for January 1 to June 30 and 76 cents per mile from July 1 to December 31 (IRS standard mileage rates). Because the rate changed mid-year, keep the date column accurate and calculate each half separately:
=SUMIFS(F:F, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,6,30)) * 0.725
=SUMIFS(F:F, A:A, ">="&DATE(2026,7,1), A:A, "<="&DATE(2026,12,31)) * 0.76
Tab 5: Summary formulas
Here's where the spreadsheet starts saving you time. Put property names down column A and months across row 1 (as real dates: the first of each month). Then use SUMIFS, which works the same way in Excel and Google Sheets.
Revenue for one property in one month:
=SUMIFS(Bookings!$K:$K, Bookings!$C:$C, $A2, Bookings!$A:$A, ">="&B$1, Bookings!$A:$A, "<"&EDATE(B$1,1))
Expenses for one property in one month:
=SUMIFS(Expenses!$E:$E, Expenses!$B:$B, $A2, Expenses!$A:$A, ">="&B$1, Expenses!$A:$A, "<"&EDATE(B$1,1))
Expenses by category for the year (for your preparer):
=SUMIFS(Expenses!$E:$E, Expenses!$C:$C, A2, Expenses!$A:$A, ">="&DATE(2026,1,1), Expenses!$A:$A, "<="&DATE(2026,12,31))
Net profit is simply revenue minus expenses for the same property and period. Shared costs entered with Property = "All" need a decision: split them evenly, by available nights, or by revenue. Pick one method, write it on the Setup tab, and use it all year.
A note on bookings that cross a month boundary: the formulas above assign each booking to the month of check-in. That's fine for a simple tracker. If you want exact monthly nights, you'll need a nightly breakdown, which is one of the things a pre-built tracker handles for you.
The three numbers worth tracking
Payouts tell you what arrived in the bank. These three tell you how the property is performing:
- Occupancy rate = booked nights ÷ available nights
- ADR (average daily rate) = accommodation revenue ÷ booked nights
- RevPAR (revenue per available night) = accommodation revenue ÷ available nights, which is also occupancy × ADR
In the sheet:
Occupancy: =SUMIFS(Bookings!E:E, Bookings!C:C, A2) / VLOOKUP(A2, Setup!A:B, 2, FALSE)
ADR: =SUMIFS(Bookings!F:F, Bookings!C:C, A2) / SUMIFS(Bookings!E:E, Bookings!C:C, A2)
Use accommodation revenue (column F), not total payout, for ADR and RevPAR, so cleaning fees don't inflate your nightly rate. RevPAR is the most useful single number for comparing months or properties, because a high nightly rate with low occupancy and a low rate with high occupancy can't hide behind each other.
Common spreadsheet mistakes
- Logging payouts only. You lose the fee detail and can't reconcile to platform reports.
- Free-typed categories. Use a dropdown.
- Dates stored as text. SUMIFS date filters silently return zero. If a date is left-aligned in the cell, it's probably text.
- Mixing personal and business spending. Use a separate bank account and card for the rental, and the Expenses tab mostly fills itself from one statement.
- No receipt column. You'll want it the day someone asks for documentation. IRS guidance on how long to keep records is on its recordkeeping page.
- One giant tab. Separate logs and summaries, and lock the summary tab so formulas don't get typed over.
Frequently asked questions
Should I use a spreadsheet or accounting software?
For one to a few properties, a well-built spreadsheet is often enough, and it costs nothing per month. Accounting software becomes worth it when you have many properties, employees or bookkeeping help. Either way, the categories and habits are the same.
Does this work in Google Sheets?
Yes. SUMIFS, EDATE, VLOOKUP, DATE, dropdowns and conditional formatting all work in both Excel and Google Sheets.
How often should I update the spreadsheet?
Weekly entry plus a 15-minute monthly review works for most hosts: reconcile payouts to bank deposits, clear missing receipts, and check each property's profit.
What if a cleaning fee is more or less than what I pay my cleaner?
Track both: the fee you charge goes on the Bookings tab, and the amount you pay goes on the Expenses tab under cleaning and maintenance. The difference shows whether your cleaning fee is set right. Our guide to how much to charge for an Airbnb cleaning fee walks through the math.
Want it already built?
The Key & Kit STR Income & Expense Tracker is this spreadsheet, finished and tested, for Excel and Google Sheets: a booking log that calculates nights, gross, fees and payout; an expense log with host categories and per-property tagging; a mileage log; missing receipts highlighted; and a dashboard with occupancy, ADR, RevPAR and net profit by month for up to 10 properties. It includes a sample-data copy so you can see every formula working.
See the STR Income & Expense Tracker →
Not ready yet? Try our free STR profit calculator, or grab the free one-page Apartment Turnover Card.
Key & Kit templates are organizational tools, not tax, legal or accounting advice. Tax rules depend on your situation; consult a qualified tax professional.