The Best Rental Property Cash Flow Spreadsheet (Free Download and Tips)

Struggling to keep track of your rental property’s cash flow and unsure if you’re maximizing your profits? This article will guide you through using a free rental property analysis spreadsheet to simplify tracking, calculate key metrics, and avoid costly mistakes.

The Best Rental Property Cash Flow Spreadsheet Free Download and Tips

0:00

/687.792

1×

So you need to keep tabs on your business's cash flow? Of course, you do! It's an essential part of making sure things aren't just running smoothly but that you're making a nice profit in the process.

At this stage, you ideally need to be looking beyond just breaking even and doing a more thorough rental property analysis beyond the net operating income (NOI), i.e. you need to know if your business is profitable and if it'll stay that way based on current metrics.

In this article, you'll learn how to use a rental property analysis spreadsheet to simplify the process and ensure accuracy. This will help you focus on calculating good monthly cash flow and net operating income to make more informed decisions on how you're running your business.

Estimating Key Property Metrics

We'll start off by introducing you to the most important property metrics to evaluate your performance.

Fair Market Value

The fair market value (FMV) of your rental property is the price it would sell for if both you and a buyer agree without any pressure and have a clear understanding of the property's value.

For short-term rentals, FMV typically refers to the reasonable rent you can charge by comparing similar properties in your area. Factors such as location, size, condition, and features like amenities or parking play a key role.

Knowing this helps you set a competitive price, appeal to the right guests, and keep your short-term rental profitable and marketable. You can determine the FMV of your property with a Comparative Market Analysis (CMA), which is basically when you compare it to similar properties that have recently been sold or rented in your area.

Alternatively, you can figure out your FMV with tools from Zillow, Redfin, or Realtor.com or simply get it appraised by a professional.

Gross Annual Rental Income

This is what your rental generates over the course of a year before any deductions. Gross rental income would account for regular rental payments and any fees on top of that like additional services, cleaning, or parking.

To determine this, start with what you charge guests for their stay and what that amounts to at the end of a month. That's your gross monthly rent collected. This is essentially your average monthly income. Once you have that, multiply it by the number of months rented to get the annual income.

Remember to account for any vacancies, so if you won't be renting out your property for any amount of time, factor that in. For example, if you expect it to be vacant for 1 month per year, you’ll calculate rent for 11 months instead of 12.

Example Calculation:

Monthly Rent: $2,000

Vacant Months: 1

Additional Income (e.g., pet fees): $100/month

Months Rented: 11

Gross Monthly Income = $2,000 + $100 = $2,100

Gross Annual Rental Income = $2,100 × 11 = $23,100

This gives you the total income your property generates in a year before expenses and taxes are deducted.

Beyond Monthly Rental Income

Depending on your business, rental income might not be your only source of income. As mentioned before, additional services like cleaning, laundry, or parking also need to be included in your gross rental income. If you offer anything like shuttle services or tours, add that in. That's all part of your effective gross rental income and will be taken into account when you calculate your cash flow.

Operating Expenses

This one is fairly straightforward. What does it cost you to keep this business running? Factor in any and all expenses like property management fees, insurance premiums, and maintenance costs.

Here's a list of the most common operating expenses:

• Property Taxes: Annual property taxes based on local government assessments.
• Insurance: Costs for property or landlord insurance.
• Utilities: If you cover any utilities (e.g., water, electricity, gas, or trash removal), include these.
• Maintenance and Repairs: Regular upkeep (e.g., lawn care, cleaning, or appliance repairs).
• Property Management Fees: Fees paid to a property management company, if applicable.
• Marketing Costs: Expenses for advertising your property or listing it on platforms.
• HOA Fees: If the property is part of a homeowners’ association.
• Pest Control: Recurring pest management services.
• Supplies: Items like cleaning products, light bulbs, or guest essentials for short-term rentals.
• Professional Services: Fees for accountants, legal assistance, or appraisals.
• Vacancy Costs: Lost income during periods when the property is unoccupied.
• Miscellaneous Costs: Include any other unique expenses, such as landscaping, pool maintenance, or snow removal.

Cash Flow Analysis

It's time to bring it all together with a Cash Flow Analysis. Let's take it step-by-step.

1. Calculate your total income by starting with your gross annual income and adding any other sources of income you have.
2. Now identify all of your expenses and them up, i.e. Total Operating Expenses = Sum of all recurring annual expenses.
   (Remember to add your mortgage payments if you have those.)
3. Subtract your total operating expenses (including mortgage payments) from your total income.

\[ \text{Cash Flow} = \text{Total Income} - (\text{Operating Expenses} + \text{Mortgage Payments}) \]

You'll be able to analyze your results from here to determine if you have a positive or negative cash flow.

Positive Cash Flow: Your income exceeds expenses, and the property is generating profit.

Negative Cash Flow: Your expenses are higher than your income, meaning you’re losing money on the property.

Let's hope it's not the latter, but if it is, you've identified all of your operating expenses so you can start cutting back on anything unnecessary from here.

Want to go a little bit deeper? Let's go.

Return On Investment (ROI)

Let's figure out if your property is a good investment based on operating income relative to the property's value. To do this we need to calculate a couple of key metrics:

• Capitalization Rate (Cap Rate) and Cash-on-Cash Return.

The Cap Rate shows the return on a property by comparing its operating income to its market value or purchase price. It’s an important metric for assessing income-generating properties.

To calculate the cap rate, divide your net operating income (NOI) by your property value and multiply by 100.

\[ \text{NOI} = \text{Gross Rental Income} - \text{Operating Expenses} \]

\[ \text{Cap Rate} = \left( \frac{\text{NOI}}{\text{Property Value}} \right) \times 100 \]

How to Interpret Cap Rate:

• 5-10% Cap Rate: Typical for residential properties (varies by market).
• A higher cap rate suggests better returns but may involve higher risk.
• A lower cap rate often reflects lower risk or a high-demand area.

If you’re using financing, calculate the Cash-on-Cash Return by comparing your annual cash flow to the cash you’ve invested, like the down payment, closing costs, and renovations. A property with a 6% cap rate may be attractive in a stable market.

To calculate your cash-on-cash return, take your net annual cash flow, divide it by the total cash invested, and multiply it by 100. A 13.3% return is strong for most markets.

\[ \text{Cash-on-Cash Return} = \frac{\text{Annual Cash Flow}}{\text{Total Cash Invested}} \times 100 \]

Using a Rental Property Analysis Spreadsheet

For a free and easy way to monitor your cash flow, you can download our spreadsheet or create your own based on what you've learned so far.

The main advantage of a spreadsheet is that it's very customizable. You can add or remove whatever elements you need.

The key is to be consistent in your tracking. Hop into this file regularly and track everything. Small, unexpected expenses add up over time and if you don't keep tabs on them they may completely run away with you.

Common Mistakes (and How to Avoid Them)

You've got the basics down. You just need to start populating your spreadsheet. But to avoid any major pitfalls down the road, let's talk about a few common mistakes at this stage of the game and how to avoid them.

Overestimating Income and Underestimating Costs

It’s easy to overestimate how much rent you’ll bring in, especially if you’re banking on overly optimistic numbers for rental rates or occupancy. Instead, base your expectations on realistic, market-level figures from similar properties in your area.

On the flip side, people often underestimate expenses. Big-ticket surprises like major repairs or emergency fixes can quickly shrink your profits.

💡 Pro Tip: Plan for vacancies by factoring in a 5-10% vacancy rate. Better to be cautious and end up pleasantly surprised than the other way around.

Missing Some Operating Costs

It’s not just the mortgage—other costs like HOA fees, property management, taxes, and insurance can stack up fast if you don’t account for them. Don’t forget maintenance, either: landscaping, pest control, and even things like pool cleaning can sneak up on you.

💡 Pro Tip: Make a list of all potential costs—both regular (like utilities and insurance) and unexpected (like emergency repairs). A good rule of thumb: set aside at least 1% of the property’s value each year for upkeep.

Forgetting About Financing Costs

Your mortgage payments (principal + interest) are a big part of your cash flow equation. Skipping over these numbers can leave you with a much rosier view of your property’s profitability than reality. Also, don’t forget upfront costs like closing fees or appraisals when budgeting.

💡 Pro Tip: Use a loan amortization calculator to see how monthly payments impact your cash flow over time.

Underestimating the Time It Takes

Owning a rental property is not a “set it and forget it” deal. Not without a service like iGMS taking care of your real estate investment for you by automating all your repetitive tasks. Tasks like tenant screening, rent collection, and maintenance take time—and short-term rentals are even more demanding with frequent turnovers and extra cleaning.

💡 Pro Tip: If you’re hiring a property manager, remember to budget for their fees (typically 8-12% of rent for long-term leases, higher for short-term).

Skipping a Detailed Rental Analysis

Don’t buy a property without running the numbers first! Without proper projections, you could end up with an investment that doesn’t meet your goals.

💡 Pro Tip: Use a rental analysis tool or spreadsheet to include:Gross incomeOperating expensesLoan detailsCash flow projectionsROI and cap rate
This makes it easier to compare properties thorugh a rental property analysis and spot the better deals.

Overlooking Tax Implications

Income from your rental property is taxable, and forgetting this can lead to an unpleasant surprise at tax time. On the flip side, missing out on deductions like mortgage interest or repairs means you’re leaving money on the table.

💡 Pro Tip: Work with a tax pro to understand which expenses you can deduct and how to plan for taxes. Smart planning here can boost your returns.

Final Thoughts

There you have it. As simple as it may seem a rental property analysis spreadsheet is a pretty powerful tool for evaluating the potential profitability of your rental property.

By following these tips and avoiding the common mistakes we've laid out for you, you can use a rental property analysis spreadsheet to make more informed investment decisions and start maximizing your returns.

Download our free spreadsheet to get started and sign up with iGMS to take it to the next level.