How to Analyze a Rental Property Deal with a Spreadsheet (Step-by-Step)

Why Spreadsheet Analysis Beats Gut Feeling

New real estate investors make the same mistake repeatedly: they find a property that "feels" like a good deal, run a back-of-napkin calculation, and buy it. Six months later, they discover that property taxes, insurance, and maintenance eat up most of the rental income, leaving them with $100 per month of cash flow or worse, a negative cash flow situation where they are paying out of pocket every month to own the property.

A spreadsheet forces you to confront every cost before you sign anything. It replaces optimism with arithmetic. And the best part is that once you build your analysis template, every subsequent deal takes 15 minutes to evaluate instead of hours.

Our Rental Property Analyzer template includes all the formulas and projections covered in this guide, ready to use.

The Worked Example: A Duplex in Indianapolis

To make this concrete, we will analyze a real-world scenario. The numbers are based on actual market data for the Indianapolis metro area, one of the most popular markets for out-of-state rental investors due to its favorable price-to-rent ratio.

The property: A duplex listed at $210,000. Each unit rents for $1,050 per month, giving us $2,100 in gross monthly rent. The property was built in 1985, has a newer roof (5 years old), and needs about $8,000 in cosmetic updates.

The financing: Conventional investment property loan at 7.25% interest, 25% down payment ($52,500), 30-year amortization. Estimated closing costs: $5,500. Total cash required: $52,500 + $5,500 + $8,000 renovations = $66,000.

The loan: $157,500 mortgage at 7.25% over 30 years = $1,074 per month in principal and interest.

Step 1: Calculate Gross Rental Income

Start with annual gross rental income. Both units at $1,050 per month gives us $2,100 monthly or $25,200 annually. But no property stays occupied 100% of the time. Apply a vacancy factor of 6% (about 3 weeks of vacancy per unit per year, which is conservative for a duplex in a decent Indianapolis neighborhood).

Effective gross income: $25,200 x 0.94 = $23,688 per year.

Step 2: Itemize Operating Expenses

This is where most beginners go wrong. They subtract the mortgage from rent and call the difference "profit." Real operating expenses are substantial and must be accounted for individually.

Property taxes: $2,940 per year (Indianapolis assessment on a $210,000 property at approximately 1.4%).

Insurance: $1,680 per year (landlord policy for a duplex, including liability coverage).

Property management: $2,369 per year (10% of effective gross income). Even if you plan to self-manage, include this. Your time is worth money, and you may eventually want to hand off management. If the deal only works because you manage it yourself for free, it is a job, not an investment.

Maintenance and repairs: $2,100 per year (1% of property value). Older properties may need more; newer properties less. This covers the water heater that fails, the garbage disposal that breaks, the exterior painting touch-ups.

Capital expenditure reserve: $1,260 per year (approximately $105 per month). This is the sinking fund for big-ticket replacements: roof ($8,000-$12,000 every 20-25 years), HVAC ($4,000-$6,000 every 15-20 years), water heaters ($800-$1,200 every 10-12 years), flooring, appliances.

Lawn care and snow removal: $720 per year ($60 per month, typical for a duplex lot in Indianapolis).

Total operating expenses: $11,069 per year.

Step 3: Calculate Net Operating Income (NOI)

Net Operating Income is your effective gross income minus operating expenses, before debt service. This is the number used to calculate cap rate.

NOI: $23,688 - $11,069 = $12,619 per year.

Step 4: Cap Rate

Cap rate (capitalization rate) measures the property's return as if you paid all cash. It is useful for comparing properties regardless of financing.

Formula: NOI / Purchase Price = Cap Rate

$12,619 / $210,000 = 6.01%

A 6% cap rate in Indianapolis is reasonable. It is not a home run, but it is in the range where deals can work with leverage. In coastal markets, you might see 3-4% cap rates; in the Midwest, 5-8% is typical.

Step 5: Cash Flow and Cash-on-Cash Return

Now we subtract debt service (the mortgage payment) to find actual cash flow.

Annual debt service: $1,074 x 12 = $12,888 per year.

Annual cash flow: $12,619 NOI - $12,888 debt service = -$269 per year.

This is a negative cash flow. The property costs you about $22 per month out of pocket. For many investors, this would be a deal breaker. But let us look at the full picture before rejecting it.

Cash-on-cash return: -$269 / $66,000 = -0.4%. Not good by any measure.

Step 6: The 1% Rule Quick Check

Monthly rent / purchase price = $2,100 / $210,000 = 1.0%. This property barely meets the 1% rule. In practice, any property right at 1% in a market with property taxes above 1% and insurance costs typical for the Midwest will struggle to cash flow with today's interest rates.

The 1% rule was more reliable when mortgage rates were 3-4%. At 7%+ rates, you need closer to 1.2% or higher for positive cash flow.

Step 7: Debt Service Coverage Ratio (DSCR)

Lenders use DSCR to determine if a property generates enough income to cover its debt. The formula is NOI divided by annual debt service.

DSCR: $12,619 / $12,888 = 0.98x

A DSCR below 1.0 means the property does not cover its debt from operations alone. Most DSCR lenders require a minimum of 1.2x to 1.25x. This deal would not qualify for a DSCR loan at the asking price.

Step 8: Making the Deal Work

The analysis shows this deal does not work at $210,000 with current financing. But this is exactly why you run the numbers: the spreadsheet tells you what price would work.

If we target a 1.25x DSCR, we need an NOI of at least $16,110 ($12,888 x 1.25). Our current NOI is $12,619, so we need to either increase income or decrease the purchase price.

Option A: Negotiate the price down. At $180,000, your mortgage drops to $900 per month ($10,800 annually), property taxes drop to about $2,520, and the cap rate improves to 6.7%. Cash flow becomes $12,250 NOI - $10,800 debt = $1,450 per year, or about $121 per month. Not life-changing, but positive.

Option B: Increase rents. If each unit can rent for $1,200 per month after the $8,000 in renovations, gross rent jumps to $28,800. After vacancy and revised expenses, cash flow improves significantly.

Option C: Larger down payment. Putting 30% down instead of 25% reduces your mortgage and improves cash flow, but lowers your cash-on-cash return because you have more capital tied up.

Your spreadsheet should include a scenario section where you can toggle these variables and instantly see the impact on every metric. The Rental Property Analyzer template includes built-in scenario comparison for exactly this purpose.

Step 9: The 10-Year Projection

A single year snapshot misses the long-term picture. Build a 10-year projection that accounts for rent growth (2-3% annually in stable markets), expense inflation (2-3%), property appreciation (3-4% in the Midwest), and mortgage paydown.

For our Indianapolis duplex at the negotiated price of $180,000, a 10-year projection assuming 3% rent growth, 2.5% expense inflation, and 3% appreciation shows:

Year 1: $121/month cash flow, property value $180,000.
Year 5: $287/month cash flow (rents have grown while the mortgage stays fixed), property value $208,745.
Year 10: $498/month cash flow, property value $241,906. Mortgage balance paid down to approximately $117,000, giving you $124,906 in equity.

Your total return includes cash flow ($32,000 cumulative over 10 years), equity buildup via mortgage paydown ($28,500), and appreciation ($61,906). On a $58,000 initial investment, that is a total return of approximately $122,000, or about 210% over 10 years. The annualized return is roughly 12%, which is competitive with stock market returns and comes with tax advantages (depreciation, mortgage interest deduction).

Common Mistakes That Destroy Rental Property Returns

Underestimating vacancy. Using 0% vacancy in your analysis is a fantasy. Even in tight rental markets, you will have turnover. Budget 5-8%.

Ignoring capital expenditure reserves. A $10,000 roof replacement in year 7 can wipe out three years of cash flow if you have not been setting aside reserves.

Using current rents for future projections without adjusting expenses. Rents may grow 3% per year, but so do insurance, taxes, and maintenance costs. Model both sides.

Falling in love with the property. The numbers either work or they do not. A beautiful property with bad fundamentals is still a bad investment. Your spreadsheet is the antidote to emotional decision-making.

Analyzing too few deals. Experienced investors analyze 50 to 100 deals for every one they buy. A good spreadsheet template makes this possible by reducing analysis time from hours to minutes per property.

Building Your Analysis Process

The fastest way to start is with a proven template. Our Rental Property Analyzer includes all the formulas from this walkthrough, plus built-in scenario modeling, 10-year projections, and portfolio tracking for multiple properties. Fill in the highlighted cells, and the spreadsheet does the rest.

But whether you build your own or use a template, the discipline of running every deal through a standardized analysis is what separates successful real estate investors from the ones who end up on forums asking why their "cash flowing" property keeps costing them money.