How to Track Airbnb Income and Expenses in a Spreadsheet

Your Airbnb dashboard tells you what you earned. It does not tell you what you made. It shows gross booking revenue with the cleaning fee bundled in, before you paid the cleaner, before the mortgage, before the $310 HOA bill and the STR insurance premium that costs three times a normal homeowner’s policy. Ask a host what their place brings in and you will get the gross number, because it is the only one anybody hands them.

This guide builds the other number. It walks through a spreadsheet that takes one row per booking and one row per expense and turns them into occupancy rate, ADR, RevPAR, net profit and a Schedule E-ready tax summary — per property and across a portfolio.

Everything below runs on one worked example so you can see the arithmetic. The figures are assumptions I have chosen to be plausible for a two-bedroom coastal condo, not national averages. Yours will differ. The structure will not.

The example property

Harbor Loft — a two-bedroom condo listed on Airbnb and VRBO. Over a full calendar year:

• 42 bookings, 168 nights booked
• Average daily rate of $190
• 14 nights blocked for owner use
• $110 cleaning fee charged to the guest, $95 paid to the cleaner
• Platform commission: assume 3% of the booking subtotal (read your own off a payout report — this varies by platform and fee model)

Hold on to those numbers. They come back in every section.

Layer 1: the booking log

This is the spine of the whole spreadsheet. One row per reservation, entered when the booking is confirmed. Nine columns do all the work:

Two habits make this log worth keeping.

Enter check-in and check-out as real dates and let the sheet count the nights. Typing “4” in a nights column feels faster and it is how the log rots — one fat-fingered number and every metric downstream is wrong with no error message to warn you. A date subtraction cannot silently disagree with the calendar.

Keep the cleaning fee in its own column, never folded into the nightly rate. The cleaning fee is not revenue in any meaningful sense. It is money you collect on a cleaner’s behalf and pass through, and the moment it is mixed into your room revenue your ADR is inflated and every performance comparison you make is meaningless. It belongs on the row, and it belongs in its own column.

For Harbor Loft, the year’s booking log rolls up to:

That $35,444 is the number the platform actually deposits. It is not profit. It has not met a single bill yet.

Layer 2: the expense log, categorised the way the IRS wants it

One row per transaction: date, property, category, description, vendor, amount, payment method, receipt saved, deductible yes/no.

The category column is the one that saves you money, and the trick is to pick your categories now to match the lines on the tax form you will eventually file, rather than inventing your own and re-sorting a year of transactions the week before the deadline. For most hosts that form is Schedule E, whose expense lines include advertising, auto and travel, cleaning and maintenance, commissions, insurance, legal and professional fees, management fees, mortgage interest, repairs, supplies, taxes, utilities and depreciation. (Whether your rental belongs on Schedule E or on Schedule C is a real question with a real cost attached — it is worth ten minutes of your time.)

Harbor Loft’s year:

Note what is and is not in that table. Mortgage interest is there; the principal portion of the payment is not, because paying down principal is not an expense — it is you buying more of your own building. Depreciation is not there either, and for most hosts it is the single largest deduction on the return. It does not belong in a cash-flow view but it absolutely belongs on the tax return, and working out the depreciable basis of a property is a conversation with a tax professional, not a spreadsheet formula.

Layer 3: the number nobody quotes

Gross collected, $36,540. Real profit, $6,219. Seventeen percent of the headline number.

That gap is not a sign the property is bad — a condo generating six thousand dollars of cash profit while a tenant pays down the mortgage is a perfectly reasonable asset. The point is that the gap is thirty thousand dollars wide, and a host who only ever sees the top line cannot tell the difference between a property clearing $6,000 and one quietly losing $2,000. Both of them look like $36,540 on the app.

This is also why per-booking maths matters more than most hosts think. A single four-night stay at $180 a night looks like an $805 payout, but after direct costs and its share of the fixed costs it nets $45 — and the break-even nightly rate for that property turns out to be $168, which is uncomfortably close to what a lot of hosts discount to in the shoulder season.

Layer 4: the dashboard

Three logs are data. The dashboard is where they become decisions. Six metrics, calculated per property and for the portfolio:

Occupancy here uses 351 available nights — 365 minus the 14 the owner blocked. That choice matters and it is where most host spreadsheets go wrong: if you count nights you deliberately took off the market as vacancy, you are penalising yourself for using your own condo, and the metric stops being comparable year to year. Pick a denominator, write it down, never change it mid-year.

ADR and RevPAR are the pair that decides where your next dollar goes, and they routinely disagree. A property with a $260 ADR at 48% occupancy earns less per available night than one with a $165 ADR at 81% — $124.80 against $133.65, a difference of $3,230 a year on a single unit. If you only track ADR you will conclude the expensive property is your winner and price the other one up until it stops selling. The full arithmetic, and how to pick the right denominator, is here.

Build the dashboard with SUMIF and COUNTIF against the property name so it recalculates itself. Revenue for a property is SUMIF(BookingLog[Property], "Harbor Loft", BookingLog[GrossRevenue]); turnovers are COUNTIF; every KPI is a ratio of two of those. Nothing gets typed twice, and adding a fourth property means adding a column, not rebuilding the sheet.

Layer 5: month by month, because the annual average lies

An annual occupancy figure of 47.9% describes no month that actually happened. Harbor Loft’s July ran 27 booked nights at a $268 ADR — a RevPAR of $233. Its January ran six nights at $150, a RevPAR of $29. Eight times the earning power in one month versus the other, and the average tells you about neither.

So the P&L grid runs categories down the side and months across the top. It answers the questions the annual number cannot: which months carry the property, which months you should stop discounting into and simply block for maintenance, when the insurance and tax bills land relative to when the money arrives, and how much cash you need to hold in January to survive to April.

Once you have two years of that grid side by side, you can price the coming season off your own history instead of guessing — which is the whole basis of setting nightly rates by season without eyeballing it.

Layer 6: the operational tabs that protect the revenue

Two more logs earn their place, because both of them protect money the first four layers are only measuring.

The turnover schedule. One row per checkout: date, property, crew, fee, and the next check-in date. One calculated column — hours between checkout and the next arrival — and a conditional format that turns the cell red under four hours. Every host eventually accepts a same-day turn they should have blocked, and finds out at 3pm when the cleaner texts. A red cell on a calendar is cheaper than a one-star review about dirty towels.

The pricing log. Your rate and a competitor’s rate for the same date range, plus the occupancy you actually achieved in that window. This is the only way to build the history that makes next year’s pricing a calculation rather than a guess, and it takes about two minutes a month to maintain.

Setting it up so you still use it in November

Three rules, and they are the difference between a live sheet and an abandoned one.

Log the booking at confirmation, the expense within the week. Confirmed bookings show you a filling calendar; expenses logged while you still recognise the charge are deductions you will actually claim.

Reconcile to the payout report monthly. Total the net payout column for the month and compare it to what the platform says it sent. When they disagree it is almost always a resolution adjustment, a refunded booking or a commission rate you assumed instead of read — all things you want to find in a month, not in April.

Never let a booking exist only in the platform’s calendar. Direct bookings, a friend of a friend, the week your brother-in-law paid cash: if it is not in the log, your occupancy is understated, your ADR is wrong, and your tax return is incomplete.

Putting it together

The whole system is one loop. Log every booking and every expense against a property → let the sheet compute occupancy, ADR, RevPAR and true net profit → read the monthly grid to see which season is carrying you → price and staff next year off that instead of a feeling. The tax summary falls out of the same data for free, because you categorised as you went.

You can build all of it from the tables above — a booking log with a net payout formula, a Schedule E-aligned expense log, a SUMIF dashboard and a monthly P&L grid. If you would rather start with it already built and formula-driven, that is what our host dashboard does.