Cash Flow For Rental Property Spreadsheet

2022 Rental Property Analysis Spreadsheet [Free Template]

woman using spreadsheet
by Jeff Rohde, posted in INVESTMENT STRATEGY

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 rental property 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. A good free online tool to use is the Roofstock Cloudhouse Calculator. By entering the address of any single-family home in the U.S., you can instantly receive a forecast of potential return and operating costs, even if the house has never been used as a rental.

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 turnkey 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

Roofstock Academy has put together a simple rental property analysis spreadsheet using Google Sheets:

roofstock rental property spreadsheet

Click here to download the complete rental property analysis spreadsheet

 

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.

 

man calculating numbers

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.

Calculating Returns for a Rental Property

Have you ever thought you could use more money?  My guess is, “yes”.

A common way to increase income is to purchase real estate, which you can rent out.  The first question that’s asked when participating in such an endeavor is:

“Is this property worth buying?”

Two ways to answer this question are by calculating:

  • Cash-On-Cash Return, and
  • Return on Investment (ROI)

This article will show you how you can use Excel to set up a simple template that you can use to help analyze all sorts of investment decisions.

A completed version of the template is available from the workbook link at the end of the post, but if you take the time to go through the process yourself, you will come away with a greater understanding of what the numbers mean and how they impact the overall decision.

Let’s set the stage.  You receive a call from your real estate agent and informs you of a property that has become available.  It’s a home in your area that the owners are moving out of and need to sell.

The owners have an asking price of $200,000 for the home.

It is expected that you could safely ask $1,400 as monthly rent.

Is this a good deal?

We will run the numbers and see if becoming a property plutocrat is in your future.

Does Such an Investment Make Sense?

If you’re new to the real estate game, it would be easy to think, “WOW! $1,400 additional income would be great!”  With a few more purchases like these, you could quit your job and pursue your dream of becoming a full-time Excel YouTuber.

Hold tight, Mr./Mrs. Budding Tycoon.  If you remember from our Basic Accounting post, we know that income (i.e. profit) is defined as revenue minus expenses.

Income = Revenue – Expenses

The rental income is just the revenue part of the equation.  We can’t forget the expenses.  Unless you happen to have $200,000 just sitting in the bank, you will likely need to explore some form of external financing.

Time to Crunch Some Numbers

We need to analyze the following components:

  • Investment
  • Monthly Debt Service
  • Monthly Revenue
  • Monthly Expenses
  • Monthly Cash Flow
  • Cash on Cash Return (full mortgage)
  • ROI (without principal pay down)

Follow along using the workbook template.

Investment

We will begin by entering the purchase price of the property (cell D3) set at $200,000.

Next, we will account for taxes and fees (0.5%), closing costs (1%), and realtor fees (1%) (cells C4C5, and C6).

NOTE: These categories are more in line with property purchases.  If you use this template for other purchase types, like heavy equipment, your categories will be different (ex: transportation, installation, etc.)

To calculate these categories, we multiply each of the categories by the purchase price.

Newly acquired properties are rarely ready for rental (say THAT 3-times fast).  We need to account for rehab costs with account for repairs and modifications to the property.  For our example, we need to paint the interior and modernize the kitchen.  For our property, this costs $5,000 (cell D7).

Any other costs can be recorded in the Other category (cell D8).

The Total Investment is the sum of all cells from D3 to D8.  This will be the total cash outflow for the investment.

If you plan on deferring any of these costs with your own money (equity), we need to record this to be deferred from the Total Investment.  Our example will have an equity value of $40,000 (cell D11).

Our final answer for the Investment category is achieved by deducting the Equity (D11) from the Total Investment (D9).  This calculation gives us the Resulting Financing Requirement (cell D12).

Monthly Debt Service

Because we don’t have $170,000 tucked in our bed cushions, we visit our bank and negotiate the following terms:

  • Annual interest rate of 3.0%
  • Term of Loan as a 25-year loan

We enter the Annual Interest Rate of 3.0% (cell D16) and the Term of Loan in Years as 25 (cell D17).

The Resulting Monthly Loan Payment is calculated using the PMT (payment) function.

The PMT function has the following structure:

=PMT(rate, nper, pv, [fv], [type])
  • Rate – is the interest rate. For us, we want to calculate the interest rate for a single, monthly payment (D16/12).
  • Nper – is the number of payments. This is the number of years times the number of months per year (D17*12).
  • Pv – is the “present value”. This is the amount of money we are requesting for our loan, $170,000 (D12).
  • [fv] – is the “Future Value”. This is an optional argument that denotes the amount of money left at the end of the period.  We don’t expect to have any money left at the end of the payment, so we will calculate this at 0 (zero).  You can also leave this blank, just don’t forget to enter the comma separator in the formula as a placeholder.
  • [Type] – defines when a payment is made. This is an optional argument that has two choices: 0 = end of period payment, and 1 = beginning of period payment.  We will make our payments at the beginning of the month.

The formula for our PMT function appears as follows:

=PMT(D16/12, D17*12, D12, 0, 1)

We will be obligated to pay to the bank $804 at the beginning of each month.  This value includes interest and principal.

Monthly Revenue

The monthly revenue will be the cash received each month by renting out the property.  We will assume a value of $1,400 per month on this property (cell D22).

If you are charging for any other amenities, like parking, cleaning services, landscaping fees, etc., we will catalog that value as “Other Income” (cell D23).  We will leave this at 0 (zero) for our example.

There will likely be months where the property remains vacant due to tenants moving out and new tenants not taking possession.  This risk will be listed as “Less revenue for vacancy” (cell C24).

We will work with a percent of 8% as a safe guess on the amount of vacant time for each year.  This would work out to .96 months per year of vacancy.  That is just under a single month of $1,400.

This calculation for loss (cell D24) is as follows:

=C24 * -D22

Because the value in cell D22 is a risk, it needs to be declared as a negative value.

The result is $112 for each month ($1,344 per year).

The Monthly Revenue is the sum of all cells from D22 to D24.

We are presented with a value of $1,288 per month for revenue.

Monthly Expenses

This section will deduct all the monthly costs for the investment.  We will make the following estimates:

  • Maintenance & Repairs (cell C29) – 3.0%
  • Property Management (cell C30) – 8.0%
  • Real Estate Taxes (cell C31) – 1.5%

The formulas to calculate these costs are as follows:

Maintenance & Repairs (cell D29)

=C29 * -D22

Property Management (cell D30)

=C30 * -D22

Real Estate Taxes (cell D31)

=C31 * -D3/12

For Insurance (cell D32), we will estimate $30 per month.  Other Expenses (cell D33) will be estimated at 0 (zero) for this example.

The Total Expenses is the sum of all cells from D29 to D33.

We are presented with a value of -$434 per month for Total Expenses.

REMEMBER: All these values are estimates purely for the sake of this tutorial.  In real life, you want to make realistic assumptions for all these values.  Many investment plans fail due to people using overly optimistic assumptions.

Monthly Cash Flow

Monthly Cash Flow is the sum of our debt service, revenue, and expenses.  Debt service and expenses will be negative values.

Monthly Debt Service (cell D38)

=D18

Monthly Revenue (cell D39)

=D25

Monthly Expenses (cell D40)

=D34

The Monthly Cash Flow calculation (cell D41) is the sum of all cells from D38 to D40.

We are presented with a value of $50 per month for Monthly Cash Flow.  Looks like our dreams of Excel YouTuber stardom will have to wait a bit longer.

Leave a Comment

Your email address will not be published.