Maximize returns.

Get Started For Free

A free and simple short-term rental spreadsheet template

woman looking over spreadsheet
by Jeff Rohde, posted in Investment Strategy

There are a tremendous amount of transactions to keep track of with a short-term rental (STR) property. Guests come and go every few days, cleaning and repairs need to be quickly done to get ready for the next arrival, and supplies need to be restocked.

With all of these moving parts, it can be easy to lose track of income or valuable expense deductions. An STR spreadsheet can help you keep a real estate business on track by having key data  in one place.

In this article, we look at the key benefits of using an STR spreadsheet, 11 metrics for monitoring an STR, and how you can download a free STR spreadsheet and sign up for free STR software.

(Want to jump straight to the free template? Click here)

 

What is an STR spreadsheet used for?

An STR spreadsheet is used to keep track of income and expenses and the bottom line. Using a spreadsheet can save you time and money and make reporting easier when tax times rolls around.

There are a lot of guests coming and going in an STR, and manual accounting can quickly become overwhelming. 

Based on an average length of stay (ALOS) of 6 nights, an STR property with an occupancy rate of 60% could have nearly 40 income entries and hundreds of expense entries over the entire year. That’s a lot of transactions to keep track of, and data could easily fall through the cracks. 

 

woman in office working on computer spreadsheets

Benefits of using a spreadsheet

A spreadsheet for an STR property can easily be created using a Google or Excel template, or income and expenses can automatically be tracked using free software from Stessa, a Roofstock company.

There are a variety of benefits to using a spreadsheet to keep transactions in one place:

  • Minimize the risk of overlooking a valuable deduction that could reduce taxable net income.
  • Save time by using spreadsheet formulas to instantly calculate cash flow each month.
  • See rental income, expenses, and earnings at a glance to better understand whether the STR is turning a profit.
  • Understand STR trends such as seasonality and pricing strategies.
  • Get a better idea of the big picture to know when and if the time is right to reinvest and add another STR to a real estate portfolio.
  • File tax returns faster and easier by setting up a spreadsheet following the same format of Schedule E (Form 1040).

 

11 key metrics to monitor

There are 11 key metrics to include on a spreadsheet to help you stay on top of property performance:

  1. Gross revenue

Total amount of income generated each month, including nightly rate plus extra services such as guided tours, child care or pet sitting, and extra cleaning or other services paid for by a guest.

  1. Occupancy rate

Keeping an eye on the occupancy rate can reveal if the nightly rate on an STRis priced correctly. If occupancy is lower than average for other STRs in the area, pricing may need to be marked down or amenities added to justify a higher rate. 

To calculate the occupancy rate, divide the number of nights rented by the number of available nights. If an STR is booked for a total of 18 nights and there are 30 available nights in the month, the occupancy rate would be 60%.

  1. ADR

Average daily rate (ADR) is calculated by dividing gross revenue by the number of nights booked. If the gross revenue in a month is $4,500 and 18 nights were booked, the ADR would be $250. 

  1. RevPAR

Revenue per available room (RevPAR) is calculated by dividing gross revenue by the number of nights a room was available, whether or not it was booked. For example, if gross revenue is $4,500 in a month with 30 days/nights, the RevPAR would be $150.

Note that occupancy rate, ADR, and RevPAR all interact with one another. In this example, by multiplying the occupancy rate of 60% by the ADR of $250, we end up with a RevPAR of $150. As the occupancy rate increases, RevPAR comes closer and closer to the ADR figure.

  1. ALOS

Average length of stay (ALOS) is calculated by dividing the number of nights booked in a period by the number of bookings. If an STR is booked for a total of 18 nights in a month and there were 3 unique bookings, the ALOS would be 6.0 nights.

  1. Expenses

Both long-term and short-term rental properties have similar expenses, such as repairs and maintenance, property taxes and insurance, homeowner association fees, and the monthly mortgage if the home is financed.

However, there also are operating expenses unique to an STR property, including:

  • Utilities (electric, internet, water, sewer, gas, cable TV)
  • Kitchen condiments
  • Bath supplies
  • Towels and linens
  • Cleaning service (paid by owner)
  • Welcome gift

An STR property may have higher repair, maintenance, and cleaning costs because the average length of stay may be around 6 nights. Each time a guest departs, a home needs to be inspected for needed repairs and items to replace, thoroughly cleaned, and restocked with supplies for the next guest. 

  1. Management fee

Fees charged by a property manager of an STR property vary based on a variety of factors such as the real estate market the STR is in, type and size of property, the number of guest bookings each month, and services provided by the property manager such as personally greeting each arriving and departing guest.

According to Lodgify, STR property management fees can range from 12% to 50% of the gross revenue collected. For example, if the gross revenue in a month is $4,500, the property management fee could range from $540 to $2,250.

  1. Booking fee

Vacation rental platforms such as Airbnb and Vrbo charge a host booking fee, generally between 3% and 8% of the total amount charged to a guest. 

To illustrate, assume the nightly rate is $250 and a guest stays for 6 nights, and also pays a $100 cleaning fee. The total amount charged to the guest in this example would be $1,600 and the booking fee paid could range from $48 to $96.

Some short-term and vacation rental software services also charge a monthly subscription or booking fee. For example, pricing plans from Lodgify begin at $17 per month + a 1.9% booking fee, although the fee is waived for higher-priced monthly plans.

  1. Net income

Net operating income (NOI) is calculated by subtracting expenses (excluding the mortgage payment and capital expenses, such as replacing furniture or a TV) from the gross revenue collected. For example, if the gross revenue is $4,500 in a month and the expenses are $2,000, the NOI would be $2,500. By dividing NOI by the price or market value of an STR property, an investor can calculate the percentage rate of return or cap rate. If the annual NOI is $30,000 and a property costs $600,000, the cap rate would be 5.0%.

  1. Cash flow

Cash flow is calculated by subtracting all expenses, including any mortgage payment, from the gross revenue collected. If an STR generates gross revenue of $54,000 per year and total expenses are $42,000, the cash flow would be $12,000 per year.

  1. Projected revenue

Comparing actual and projected or estimated revenue is one good way to monitor how an STR property is performing versus budget. Projected revenue can be based on gross revenue, ADR, RevPAR, or a combination of all 3.

 

How to create an STR spreadsheet

Here’s an excerpt from an Excel spreadsheet template for an STR property, using estimated figures for illustration purposes only. The STR spreadsheet can be downloaded for free in an Excel or Google Sheets format and easily customized (sheets are in View Only mode – make a copy or download them to edit).

str spreadsheet

Want to ditch the spreadsheet entirely for something better and easier? Check out Stessa – a platform used by over 200,000 property owners to maximize their returns and spend less time on busywork. After signing up for a free account, enter the property address and link business and mortgage accounts to begin automatically tracking income and expenses.

This post on Stessa Support explains in detail how to use the software for an STR. 

Investors who have past transaction data from a site like Airbnb also can download the transaction history, then upload it to Stessa. Most, if not all, of the transactions will be automatically categorized. 

Stessa works with an unlimited number of STRs, single-family rentals, and small multifamily properties. Stessa is free and also offers premium services, such as rent analysis, mortgage financing, and market research.

With Stessa you can:

  • Track STRs, single-family homes, and small multifamily property.
  • Automatically track income and expenses, property value, and owner’s equity.
  • Monitor property performance in real time at both the portfolio and property level via the owner’s dashboard.
  • Generate financial reports and export tax-ready statements to save time and money.
  • Organize and store STR real estate documents safely and securely online.
  • Use iOS and Android mobile apps to track expenses on the go.

 

Find this content useful? Share it with your friends!