AirBnB Income Calculation Spreadsheet

[William M.]

AirBnB Income Calculations

Starting out in 2018 I rented out rooms in my house. I got continuous traffic, albeit small potatoes, but besides the joy of meeting interesting people and hosting, the greatest benefit was 5 months of data and good reviews (thanks to my Tito’s Margarita recipe!). In 2019 I decided to only rent my house while I was out of town, sometimes working around my guests stay (i.e. sleeping in my truck or taking leave to get out of town and go camping) I even considered getting a trailer and living on base part time, but I instead opted to buy another house to do a live and flip. I will be doing full time AirBnB on this house starting the end of the month and expect to gross $2000/mo, if I average between 15-20 days booked. I will continue to clean the house when I am able, but I will try to hand over the cleaning responsibility to a company I trust and can rely on to get in and out on an atypical schedule. If you decide to read any further, you’ll see that my calculations are pretty simple, but they will lead you to a product that can give you a quick synopsis of your short term rental situation, and whether the best route to go is AirBnB or traditional Long-term rental.

First rule of any spreadsheet calculation is: ‘garbage in = garbage out.’ Identifying your independent variables and making sure that they are not garbage is step numero uno. For my AirBnB Income Calculation spreadsheet, I take the average of the area (no filter) through a quick AirBnB search without dates, then I take a filtered average of the area (specific to my rental: i.e. number of rooms, bathrooms, beds, pets allowed, etc.) and use the average of both minus 10% of the Max of the averages (because I want to be competitive in the market and to provide conservative estimates). This will give you the expected average value of one night in your house, the AirBnB Avg Nightly Price.

Table 1. Calculated AirBnB Avg Nightly Price.

Now I have my nightly income. The second step in my spreadsheet is to calculate the total costs of my house: Mortgage, Insurance, Taxes, Utilities, Cleaning Services, Lawn Care Services, and add them up for a Monthly Property Cost.

Table 2. Calculated Monthly Property Costs.

Here’s where the magic happens. I create a Day Count from 1 to 30 in a Row, or Array, corresponding with each numbered cell I calculate the Monthly ROI (Return on Investment) showing you that under, or next to, cell 15 that for 15 nights I have an ROI of $293.93 and cell 12 that for 12 nights I have an ROI of -$84.67.

Table 3. Array of days occupied to see AirBnB ROI.

This is extremely valuable for estimating AirBnB income in any area and determining if the juice is worth the squeeze. It can be used to find a breakeven point and even compare that to the Long-term Rental Averages in the area. Using the same formulas and procedures above create another array from 1 to 30 of for a Long-term Rental Monthly ROI broken down by night. Assuming the property will not need the utilities and cleaning services, your Monthly Property Costs will be lower for Long-term Rentals. This is huge! If you cannot beat the Long-term Rental Monthly ROI after 15 days, it is probably not worth putting on AirBnB in an average market.

However, you can see your potential AirBnB earnings can reach 2x or 3x your earnings for a long-term rental with a total of just 20 days booked. To visualize this, take the AirBnB Monthly ROI Data (and Annual ROI Data) and the Long-term Monthly ROI (and Annual ROI Data) and plot them versus your day count array on a line graph.

Graph 1. ROI for Days Occupied. Line Graph of AirBnB Annual and Monthly Income and Long-term Rental Annual and Monthly Income.

You can see points of interest, where the Annual data lines overlap and Monthly data lines overlap, this is the break-even point for AirBnB and Long-term rentals. If you're in a "hot market" you can conservatively assume you will hit an average of 20 nights a month booked on AirBnB. However, in a standard market, the AirBnB calculator on their website will spit out a "What you could make" estimate based on 15 nights rented out. This might be a good estimate off the bat, but leaves something to be desired. It doesn't tell you that you might need to pay HOA fees, or that your mortgage is too high, or that your neighborhood is sketchy and no one wants to rent out your house because you're in the sticks. As you test the waters, modify the calculations, or better yet, compare your actuals to the predicted data. If you find you're not making what you thought you would, change up your AirBnB tactics (i.e. find a cheaper cleaning service, get professional photos taken, have a minimum of 2-nights per stay, make your cancellation policy more stringent/less stringent, minimize pricing, spruce up your house, etc.) or cut-rope and rent out with long-term leases.

I'm a huge spreadsheet geek. I graduated from the University of Texas (Hook’em!) with an Aerospace Engineering degree and I can’t imagine getting through college without Excel! My works or art may not have the same elegance as other spreadsheets, but they are simple and I have confidence in them to show me an accurate story of my investments. Below is my actual data from AirBnB that shows my gross income for 2018 and 2019, as you can see I made more money in 2019 even though my nights booked were nearly a third the amount the previous year. My average nightly price crept up as I got better reviews, but it wasn’t until I made this spreadsheet that I realized I was charging way less than what I should. My last month of 2019 I grossed $1600 for 13 nights booked ($123/night) – a number which I got to with my handy-dandy AirBnB Income Calculator. In April I took a break from AirBnB and rented out my rooms to a couple roommates, $1200/mo total, and was able to justify the price based on the AirBnB income. This break has allowed me to spruce up my place with a DIY patio and a planter/screen in the backyard to provide more privacy and outdoor seating area. It also gave me time to recharge and revector so that I can optimize my life. But now I’m ready and raring to hit the market again! More data to come, hopefully verifying my predictions!

Table 4. Actual AirBnB data for 2018 and 2019.

None of this is Rocket Science, I’m sure plenty of you out there use similar tools, probably prettier and fancier, please make suggestions and critiques if you have them! This is my first post, please let me know if you have questions, comments, or suggestions for the future! 

P.S. I know my tables are janky, if you would like the spreadsheet, just reach out to me on BP.