It’s important for real estate investors to keep an eye on the bottom line. That’s why a rental property analysis spreadsheet is one of the most important tools you can use when analyzing the current and potential performance of income-producing real estate.
A good spreadsheet keeps all of the property income and expense data in one place and helps estimate the potential profitability of each real estate investment.
In this article, we’ll show you how to make your own rental property analysis spreadsheet, explain why cash flow is so critical to real estate investors, and give you a free rental property analysis spreadsheet that you can download and customize for your own real estate business.
How to Create a Rental Property Analysis Spreadsheet
Begin by choosing a spreadsheet software program such as Google Sheets, Microsoft Excel, LibreOffice Calc, or Open Office Calc. Doing a rental property analysis on a spreadsheet makes updating much easier when you are analyzing multiple options before choosing the best property to invest in.
Next, set up your rental property analysis spreadsheet by following these four steps:
1. Estimate fair market value
There are a number of methods for estimating the fair market value of a rental property. It’s a good idea to use different techniques. That way you can compare the values and create a value range of low, middle, and maximum value.
One way of estimating the value of a rental property is to do what an appraiser does. Create a comparative market analysis (CMA) of homes similar to the one you are looking at that have recently sold, are active listings for sale, or have gone off of the market unsold.
Real estate agents with access to the MLS will run comps for free. You can also research listings on Zillow and Realtor.com, and also see what investor-owned rental properties listed on Roofstock are going for.
Once the market value is determined, use financial calculations such as cap rate and GRM (gross rent multiplier) to see if the potential returns meet your investment goals. Cap rate tells you the potential rate of return based on net operating income (NOI), while compares the property fair market value to the gross rental income.
If a home isn’t rented, you can use the 1% Rule to estimate what the monthly rent should be by multiplying the property asking price or market value by 1%. For example, if the estimated market value of the property is $150,000 the rent should be at least $1,500 per month.
2. Forecast operating expenses
Now that you know what it will cost to buy a rental property, the next step is to forecast the cost of owning and operating the property. Typical operating expenses for single-family rental houses and smaller multifamily buildings may include:
- Leasing fee
- Property management
- Repairs and maintenance
- Landscaping
- Utilities
- Capital expense (CapEx) reserve contributions
- Property taxes
- Insurance
- HOA fees
- Mortgage payment (principal and interest)
If the property is currently rented, ask the seller for the income and expense statements for the current and prior years. But, always be sure to verify the seller’s information by speaking with a local property manager or other real estate investors.
That’s because sellers sometimes omit key information such as management fees or the costs of funding a CapEx account to make the property’s operating expenses look lower than they really are.
There are two ways to forecast operating expenses if the property is vacant.
Some investors “guesstimate” operating costs by using the 50% Rule, which states that about half of the monthly rental income is used to pay for operating expenses, excluding the mortgage. That means if a house is renting for $1,200 per month the operating expenses should run about $600 each month, based on the 50% Rule.
However, guessing the cost of owning a rental property isn’t the best thing to do when you invest in real estate. This simple spreadsheet by Roofstock provides an easy way to view the potential financial performance of a given property. You can use it to forecast the potential return of a property. Simply enter some information to view projected key return on investment (ROI) metrics, including cash flow, cash-on-cash return, net operating income, and cap rate.
3. Determine market rents
It’s easy to be overly optimistic about potential rental income, especially when the demand for good homes to rent is so strong in most markets. But as a general rule, it’s usually better to conservatively determine market rents when doing an initial rental property analysis.
If the estimated conservative rental income pays for the operating expenses, you can run different scenarios to see what the financial performance of the property is when rents are set closer to market. On the other hand, if you’re barely breaking even when the rent is at the top of the market, you may want to think twice about the potential level of risk
One good way to determine market rents is to pretend you’re a tenant and call around to see what other available properties are renting for. You can find information like this online using sources such as RENTCafé, Zillow, and Rentometer. But by speaking directly with the landlord or leasing agent you’ll get a better feel for how motivated (or unmotivated) the owner is to rent the property to a good tenant.
4. Project repair, rehab, and updating costs
If you’re buying a property that requires immediate repairs or updating, ask your local property manager to provide you with three quotes from different contractors so that you can compare the bids. Also, ask your manager to verify whether the local building codes will require you to use a licensed contractor or a more affordable handyman for the work.
A good way to reduce the potential risk of underestimating repair and rehab costs is to invest in a rental property from the Roofstock Marketplace. Homes have been pre-inspected and the odds that you’ll need to make any repairs right away are much lower.
During the time you own the property, you may choose to do updating to add value and increase the rents. Be selective about the projects you choose to do, to ensure you’re getting the best potential return on your updating investments.
Free Rental Property Analysis Spreadsheet
Stessa has put together a simple rental property analysis spreadsheet using Excel/Google Sheets:
Click here to download the complete rental property analysis spreadsheet (Excel)
Click here to download the complete rental property analysis spreadsheet (Google Sheet)
Have existing properties that you want to track the performance of? Check out Stessa. Get 24/7 visibility into your portfolio’s performance with reports that are automatically generated and updated in real time. Create income statements, balance sheets, cash flow reports, collect rent, screen tenants and more, in just a couple clicks. Best of all, it’s free to get started.
Why It’s Important to Focus on Some Cash Flow
The two ways the buy-and-hold investors make money in real estate are through recurring cash flow from rental income over the entire holding period and potential appreciation in property value over the long term.
As the Federal Reserve reports, since the 1960s the median sales price of houses sold have historically gone up. For example, between the last recession of 2008/2009 and today (Q1 2021), median sales prices have increased by about 66%. However, as the recession began to take hold, home prices declined by around 20% between Q1 2007 and Q1 2009.
Because real estate markets historically move in cycles, prudent investors focus primarily on cash flow when analyzing potential real estate investments instead of trying to time the market.
Homes that have similar characteristics generally have the same future appreciation potential. Possible equity gains are also largest near the end of the loan term because a larger percentage of the mortgage payment is applied to the principal in the final years of the loan.
So, when the potential equity gain between two homes is more or less the same, the property with the greater cash flow is generally the better investment.
How to Calculate Cash Flow
Cash flow is the difference between income and expenses, before taking into account depreciation expense (which is a non-cash deduction) and personal income tax.
When you calculate the potential cash flow of a rental property, it can be easy to overestimate income and underestimate expenses. After creating your cash flow statement, it’s a good idea to run various ‘what-if’ scenarios to see how the cash flow is affected in different situations.
For example, is the property still cash flow positive if rental income goes down? How do different vacancy rates affect property cash flow? Also, how long does it take to become cash flow positive again if you need to make a major capital repair such as a heating and cooling system or roof replacement?
Here are the Items investors typically include in a cash flow analysis:
- Gross annual rental income is the amount that could be collected based on 100% occupancy.
- Vacancy/credit loss is the amount of potential rental income when the property is vacant (normally estimated at 5% of the gross annual rental income).
- Effective gross rental income is the difference between the gross annual rent and vacancy/credit loss.
- Leasing fees are charged each time the property is rented to a new tenant and may be equal to one month of rent.
- Property management fees generally run between 8% – 12% of the gross monthly rent collected.
- Repair and maintenance costs such as repairing an electrical outlet or fixing a plumbing leak usually run around 10% of the monthly rent collected.
- Utilities such as water, sewer, and gas in small multifamily buildings may be paid by the landlord with the cost included in the rent charged to the tenants.
- Property taxes vary from state to state and can run from around 0.5% of the property value to over 2% depending on where the rental property is located.
- Insurance premiums include homeowners insurance plus additional landlord coverage when property is used as a rental.
- HOA fees can include monthly dues and special assessments if the homeowners association does not have enough money in reserve to pay for major repairs such as street repaving or resurfacing a swimming pool.
- Other expenses include landscaping, pest control, snow removal, business and travel expenses.
- Net annual cash flow is calculated by subtracting all of the operating expenses from the effective gross rental income.
Other Financial Metrics to Analyze
Successful real estate investors always compare the results of their rental property analysis by cross-checking with other financial metrics to compare the pros and cons of various investment options:
- Cap rate = NOI (net operating income before mortgage debt service) / Property value
- Cash-on-cash return = Net annual cash flow / Cash invested
- Gross rent multiplier = Market value / Gross annual rents
- Gross rental yield = Gross annual rent / Market value
