How to Create Your Own Short-Term Rental Income Calculator
Before diving head first into buying your first short-term rental property there are a few crucial steps to investigate prior to pulling the trigger. One of those steps is to analyze the potential earnings of the property you are considering purchasing. There are several options for calculators online, however I chose to create my own in order to fully understand the flow of cash and were my expenses and income were actually coming from. In this article I will break down several of the most common expenses and income streams and how to incorporate them into a calculator that will give you a complete picture of your income producing property.
You can create a free calculator using Google sheets, which is essentially an excel spreadsheet accessible from the cloud. I will describe the process assuming you are using Google sheets. Here's a link to access Google sheets - https://www.google.com/sheets/about/
Alright, lets dive in. One of the first things to calculate is your monthly mortgage payment. We'll assume a 3.88% APR. To calculate this fixture take the total purchase amount of the property, we'll use $300,000 for this example, minus your down payment, and plug it into the following formula to calculate your monthly payments:
Step 1: Purchase amount minus down payment: $300,000 - $75,000 (or 25%) = $225,000
Step 2: Calculate monthly mortgage (payment per period): input the following formula into a cell in your spreadsheet:
=PMT("annual interest rate" / "payments per year", "total payments", "loan amount (principal)", "0")
For our example, this formula would look like this:
=PMT(3.5/12, 360, 225,000, 0)
I've structured my purchase information to look like the below example, but you can play around with any layout and find what best fits your needs.
Once we have figured our monthly payments (Payment per period), we can move on to the fun part and begin calculating the income. In order to forecast an accurate income amount you must conduct some market research on your prospective property. Some things to consider when forecasting this number are average occupancy rate and nightly rate. I'll go into more detail on researching potential properties in another post. Once you have average occupancy rates and nightly rate figured you can plug them into your spreadsheet.
Step 3: Calculate monthly income from nightly rental:
Occupied days = "occupancy rate" * "number of days" in that particular month.
Estimated income = "occupied days" * "average daily rate"
For our example, the formulas would look like this (January):
Occupied days = 0.40 * 31 = 12
Estimated income = $155 * 12.4 = $1,922
Next we'll look at another stream of income that could potentially be generated through your property, cleaning income. To calculate the "cleaning charge" you'll have to make an assumption on length of stay. For this example I plugged an average 3 night stay. You will also have to decide on if and how much to charge your guests for a cleaning fee. We have settled on $150/clean for this example.
Step 4: Calculate cleaning income:
Cleaning charge = ("occupied days" / "3") * "cleaning charge"
Cleaning cost = ("occupied days" / "3") * "cost of cleaning"
Cleaning Income = "Cleaning charge" - "Cleaning cost"
For our example, the formulas would look like this (January):
Cleaning charge = (12/3)*$150 = $600
Cleaning cost = (12/3)*$120 = $480
Cleaning income = $600 - $480 = $120
If there are other streams of income you'll want to factor them in here. Once we have all income streams figured we'll combine them into a monthly total, as shown below.
Step 5: Add together all income streams
Take the total cleaning income and any other income streams you've calculated and add them to the rental income. This will be your total income used later on after figuring your expenses into the equation.
Total Income = Rental income + Cleaning income
For our example, the formulas would look like this (January):
Total Income = $1,922 + $120 = $2,042
So far we've calculated our monthly mortgage payment and monthly income streams. Here's a look of what my spreadsheet looks like up to this point. I have another "purchase information" section that deals with rehab costs and after rehab value to investigate fix and flip potential which I won't go into here.
The next section isn't as enjoyable to put together as it deals with calculating your expenses, which draw from your bottom line. There are several specific expenses depending on where your property is located, so be sure you know what is associated with the local laws associated with each property. The expenses I have included in my spreadsheet are as follows:
- Taxes - Repairs reserve (%)
- Insurance - CapEx reserve (%)
- Utilities - Consumables
- HOA - Permits
- Property management (%) - Fees (%)
I go into more detail on what each expense is made up of and how to assign values to them in another post. Check it out here.
After inputting values for all of your expenses you can add the total annual expenses your property will incur and subtract them from the total income generated by the property. I do these calculations on a separate sheet from the above listed inputs.
Step 6: Calculate income against total expenses
Cashflow = Income - Total Expenses
For our example, the formulas would look like this (year 0):
Cashflow = $48,727 - $23,609 = $25,117
I also like to extend my projections out ten years to capture growth rates and appreciation on my properties. Typically I'll figure a 4% growth rate and 3.5% appreciation.
Here's a look at my full inputs sheet. As I mentioned earlier, feel free to customize the layout any way you want to best fit your style and needs. I also run all of my calculations through December but have cut off a portion for ease of visibility.
Inputs
Ensuring your income projections are in line with your budget is a vital step in the process before closing on a property. Maintaining a well defined calculator will help in figuring the bottom line of any potential property. I hope these steps help you build your own calculator and get you on your way to purchasing your next income producing short-term rental. As always, good luck in your endeavor and enjoy the journey!
