Short-Term and Vacation Rental Discussion
62K Posts | 5.1K Discussions
AirBnB Income Calculation Spreadsheet

Table 1.</strong> Calculated AirBnB Avg Nightly Price.</p><p>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 <em>Monthly Property Cost</em>. </p><p><strong></strong></p><p><img src="">

Table 2.</strong> Calculated Monthly Property Costs.</p><p> Here’s where the magic happens. I create a <em>Day Count </em>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. </p><p><img src="">

Table 3.</strong> Array of days occupied to see AirBnB ROI.</p><p>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. </p><p>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. </p><p><img src="">

Graph 1.</strong> ROI for Days Occupied. Line Graph of AirBnB Annual and Monthly Income and Long-term Rental Annual and Monthly Income.</p><p>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.</p><p>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&nbsp;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!</p><p><img src="">

Table 4.</strong> Actual AirBnB data for 2018 and 2019.</p><p>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!&nbsp;</p><p>P.S. I know my tables are janky, if you would like the spreadsheet, just reach out to me on BP.</p>">AirBnB Income CalculationsStarting 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... View more