Free Airbnb Guest-Stay Tracker: 7-Day Rule Spreadsheet Template

The 60-second answer

The 7-day rule is an average, not a max. Reg. §1.469-1T(e)(3)(ii)(A) requires the property's average period of customer use for the taxable year to be 7 days or less; Reg. §1.469-1T(e)(3)(iii)(A) defines the computation as total customer-use days divided by number of periods of customer use.
One long booking can undo a short-stay year. Ten 3-night bookings plus one 60-night booking averages 8.2 days per period; the property fails the exception for the whole year.
Track continuously, not once at year-end. The tracker's Summary tab shows the rolling year-to-date average per property and lets you project what a proposed booking would do to the annual average before you accept it.
§280A(d) is a separate ceiling. Even a property that clears the 7-day rule gets reclassified as a residence if personal use exceeds the greater of 14 days or 10% of rental days, triggering §280A(c)(5)'s income-limited deduction. The companion tab tracks this axis on the same per-property basis.
The spreadsheet handles days; it doesn't handle material-participation hours. Material participation under §469 is the third axis of the strategy and needs its own contemporaneous record. See our year-long walkthrough for the full three-axis discipline.

Not tax advice; general framework only. Confirm with a qualified tax professional how the 7-day rule and §280A(d) ceiling apply to your specific facts.

What the 7-day rule actually says

Rental real estate is treated as a passive activity under §469(c)(2) by default. Reg. §1.469-1T(e)(3)(ii) lists six exceptions that pull a property out of the rental-activity classification and into trade-or-business territory. The first and most common: §1.469-1T(e)(3)(ii)(A), where the property's average period of customer use for the taxable year is 7 days or less. That's the loophole most STR hosts pursue.

The computation itself is in a different sub-paragraph. Reg. §1.469-1T(e)(3)(iii)(A) defines the average period of customer use as the total customer-use days for the tax year, divided by the number of periods of customer use during the year. A 6-night stay contributes 6 days and 1 period. A 30-night stay contributes 30 days and 1 period. The math runs on the whole taxable year in aggregate, not per booking, and one long booking can pull the annual average above the threshold. See our 7-day rule guide for worked examples in more depth.

The tracker's Summary tab computes both numbers per property automatically, using year-to-date data from the Guest Stays tab and the tax year you set in cell B1. A "Reached / Above" status marker turns green when the year-to-date average is at or below 7.0 and red when it goes over.

What's in the sheet

Guest Stays tab: one row per completed reservation

Log each reservation as it completes: reservation ID (Airbnb confirmation code, VRBO number, or free-form), property (from a dropdown fed by the Properties tab), check-in date, check-out date, and an optional notes column for guest name or platform. Three formula columns compute automatically: nights (check-out minus check-in), whether the reservation lands in the current tax year, and whether it counts toward the year's average (cancellations and no-shows drop out).

The tab is seeded with 200 formula rows. Enough for most portfolios for a full year; extend by copying row 5 down if you need more.

Summary tab: three tables in one place

Top of the tab, cell B1 sets the tax year everything references. Change it to run the sheet for a different year without touching the log data.

Table 1: year-to-date average customer use per property. Reservations counted, total nights, average customer use in days, and a "Reached / Above" status per property row. The status cell turns green when at or below 7.0, red when over.

Table 2: booking projection. Pick a property, enter a proposed number of nights, and the sheet computes what the year's average would become if you accepted that booking. Column D returns "No; safe" or "Yes; consider refusing or negotiating shorter." Use this table before you accept any booking that looks long relative to your typical mix.

Table 3: §280A(d) personal-use ceiling. Personal-use days year-to-date (from the Personal Use tab), rental days year-to-date (from the Guest Stays tab), the ceiling (greater of 14 or 10% of rental days), and a status per property. Turns red when personal-use days exceed the ceiling and the property faces reclassification as a residence under §280A(d).

Personal Use tab: the §280A companion log

One row per day of personal-adjacent use. Date, property, type (dropdown: "Personal / family stay," "Repair or maintenance day," "Property showing," or "Other"), and free-text notes. A fifth formula column returns "Yes" for personal / family stays, "No (repair-day exception under flush language following §280A(d)(2)(C))" for maintenance days, and "No (not personal purposes under §280A(d)(1))" for property showings. The Summary tab's Table 3 counts only rows returning "Yes."

Properties tab: the dropdown source

A single column with 50 property-name slots. Every dropdown in the sheet (Guest Stays column B, Personal Use column B, Summary property columns) references this tab. Rename or add properties here and every dropdown updates automatically. Two example rows show the pattern; delete them and enter your own property names.

README tab: the how-to

A one-page overview: what the tracker computes, five-step how-to for first-time setup, the not-tax-advice disclaimer, and links back to the underlying guides on this site. Read it once when you make your copy.

The weekly capture rhythm

The tracker is designed to be updated in short bursts, not at year-end. A defensible working rhythm looks like:

Log each reservation the day it checks out. Two minutes: paste the reservation ID, pick the property, drop in the two dates. The formulas do the rest.
Log personal-use days the same day they happen. A weekend at the property in April, a family stay in July, a "checking on things" overnight in September; each of these lands on the Personal Use tab as its own row. Reconstructing at year-end from memory almost always undercounts.
Check the Summary tab monthly. Fifteen seconds to scan the three tables: is the year-to-date average at or below 7.0 per property, are any properties trending toward the §280A ceiling, is any specific booking still open on the "should I accept?" question.
Use the booking-projection table before accepting a longer booking. This is the whole point of the mid-year discipline: catch the booking-that-would-fail before it enters the log, not after.
Do a year-end reconciliation in late December. Cross-check the tracker against your platform payout statements; any missing bookings surface now, while corrections are still possible.

See our year-long walkthrough for the full operational rhythm across all three axes of the STR loophole (material-participation hours, average customer use, and §280A personal-use days). This tracker covers the second and third; the first needs its own record.

Where the spreadsheet stops (and Field Ledger picks up)

A spreadsheet works if you actually maintain it. Most hosts start strong in January and lose the habit by May. That's the honest failure mode of any download: it depends on the reader remembering to open it each week.

Field Ledger is a record-keeping tool for STR hosts. The default workflow is structured single-record forms and prefixed-line entries (Trip:, Activity:, Expense:). Opt into AI-assisted capture on a supported plan if you'd rather describe the day in one sentence and have a language model stage the entry as draft lines you review before anything saves. Either path produces the same day-count records this tracker computes (average customer use, §280A personal-use days), plus the material-participation hour log this spreadsheet deliberately does not cover, plus mileage under §274(d) substantiation and per-property Schedule E CSV export at year-end. Material participation is a fact-specific IRS determination; the tool never machine-asserts it, and the §469(h) call stays with you and your CPA.

No pressure. The tracker is yours whether you try Field Ledger or not. But if the "start strong, lose the habit" pattern is what happens to your records, that's the exact gap the app is designed to close.

Start free trial

714-day free trial, no credit card required. Renews monthly or annually at the plan price you select until canceled. Cancel anytime in Manage Billing. Plus applicable US sales tax. Not tax advice.

Frequently asked questions

What does the tracker actually compute?

The tracker logs every completed reservation with a check-in date, check-out date, and property, and then computes two numbers per property for the current tax year: (1) the year-to-date average customer use in days, per Reg. §1.469-1T(e)(3)(iii)(A), which totals the customer-use days for the tax year and divides by the number of periods of customer use; and (2) the projected new average that would result from a proposed booking of N nights, so you can see whether accepting a longer booking would push you over the 7.0-day annual threshold in Reg. §1.469-1T(e)(3)(ii)(A). A companion tab tracks §280A(d) personal-use days per property against the greater-of-14-days-or-10%-of-rental-days ceiling. Not tax advice; consult a qualified tax professional.

Do cancellations and no-shows count in the average?

No. Reg. §1.469-1T(e)(3)(iii)(A) defines the computation on "periods of customer use." A cancelled reservation is not a period of customer use; a no-show where the property was never occupied is generally not either. In the tracker, leave the check-in and check-out fields blank on any row for a booking that was not consummated, and the row is excluded from both the counted-reservations and total-nights totals automatically. Not tax advice; consult a qualified tax professional on edge cases specific to your booking mix.

What happens if I take one 30-night booking mid-year?

One long booking can pull the annual average across the 7.0-day threshold even when every other reservation is short. Example: ten 3-night bookings plus one 60-night booking averages (60 + 30) / 11 = 8.2 days per period, which fails the §1.469-1T(e)(3)(ii)(A) exception for the whole tax year. The tracker's "Booking projection" table on the Summary tab is designed for exactly this decision: enter the proposed booking length before you accept it, and see what the year-end average would become. This is the moment to negotiate a shorter stay or refuse the booking if the projected average goes over 7.0. Not tax advice; consult a qualified tax professional on how the 7-day rule interacts with your specific booking mix.

Why does the sheet have a Personal Use tab if this is a guest-stay tracker?

The STR loophole depends on two separate day-count records lining up across the year: (1) average customer use of 7 days or less under Reg. §1.469-1T(e)(3)(ii)(A), and (2) §280A(d) personal-use days under the greater of 14 days or 10% of rental days. Losing either one can undo the strategy. The Personal Use tab tracks the second record on the same per-property basis so both are visible in one sheet. Days spent principally on repairs or maintenance are excluded from personal use under the flush language following §280A(d)(2)(C), and property showings to prospective renters are not "personal purposes" under §280A(d)(1) in the first place; the tab's dropdown categorizes each day accordingly. Not tax advice; consult a qualified tax professional on §280A treatment for your specific facts.

Does this work for VRBO and Booking.com too, or only Airbnb?

The tracker is platform-agnostic. The §469 average-customer-use computation runs on the taxable year's total customer-use days and periods of customer use regardless of which platform sourced the booking. Enter every completed reservation from every platform (Airbnb, VRBO, Booking.com, direct-booked, or off-platform) into the same Guest Stays tab with the reservation ID and property; the totals aggregate correctly. Naming the file "Airbnb" in the download is a shorthand for the largest platform in that market, not a scope limit. Not tax advice; consult a qualified tax professional on how the §469 tests apply to your specific facts.

What if I own more than 10 properties?

The Properties tab has 50 rows of slots for property names, and the Guest Stays and Personal Use tabs are seeded with 200 rows of formulas each. The Summary tab's per-property tables have 10 rows by default; readers with more than 10 properties can copy the formula pattern in row 5 down to as many additional rows as needed (the COUNTIFS and SUMIFS formulas use column references, so they scale automatically). Portfolios of that size typically also involve a §1.469-4 grouping decision that's outside this spreadsheet's scope; see our multi-property STR record-keeping guide for the grouping mechanics. Not tax advice; consult a qualified tax professional on grouping and portfolio-level material-participation analysis for your specific facts.

Related guides

Statutory sources

General information about U.S. federal tax rules for STR average-customer-use and personal-use record-keeping; not tax advice.

The key takeaway

The 7-day rule is an annual average, not a per-booking maximum, and one long booking can pull the year across the threshold. The tracker's job is to make that math visible mid-year while corrective options are still open: refuse the booking, negotiate a shorter stay, or accept passive treatment for the current year. The §280A(d) companion tab does the same job for the personal-use ceiling. Both records are needed for a defensible STR-loophole return; both are simple enough to keep in a spreadsheet if you actually keep the habit. Not tax advice: this is a general framework, not personalized planning. Consult a qualified tax professional on how the tests apply to your specific facts.