How to Build a Real Estate Investment Spreadsheet That Actually Works

A real estate investment spreadsheet is the most important tool in your deal analysis toolkit. It takes a property from a listing you found online and converts it into a set of numbers that tell you whether to pursue the deal, pass on it, or make an offer at a lower price. Professional investors analyze dozens of deals for every one they purchase, and a well-built spreadsheet allows you to evaluate a property in fifteen minutes with enough accuracy to make an informed go or no-go decision. The alternative — running numbers by hand, guessing at expenses, or relying on the seller stated returns — leads to bad acquisitions that drain your portfolio.

The spreadsheet you build does not need to be complicated. Complexity creates false precision — a 47-tab model with Monte Carlo simulations and sensitivity analyses looks impressive but does not make better decisions than a clean, well-structured single-sheet model with accurate assumptions. What matters is that the spreadsheet captures the key inputs, calculates the metrics that drive your investment decisions, and is structured so you can update the inputs quickly as you analyze new properties. Here is how to build one from scratch.

Section 1: Property Information and Purchase Inputs

The top of your spreadsheet should capture the basic property information and purchase parameters. Create input cells for the property address, purchase price, closing costs (estimate 2 to 4 percent of purchase price for investor purchases), renovation or repair budget, and the financing terms — down payment percentage, loan amount, interest rate, loan term, and monthly mortgage payment (principal and interest). The mortgage payment can be calculated automatically using the PMT function in Excel or Google Sheets: PMT(monthly rate, total payments, loan amount). For a $200,000 loan at 7 percent over 30 years, the formula is PMT(0.07/12, 360, -200000), which returns approximately $1,331 per month.

Also include the total cash required to close, which is the sum of the down payment, closing costs, and renovation budget. This is the denominator in your cash-on-cash return calculation and represents the total amount of cash you must have available to complete the acquisition. For a $250,000 property with 25 percent down, 3 percent closing costs, and a $15,000 renovation budget, your total cash required is $62,500 plus $7,500 plus $15,000, totaling $85,000.

Section 2: Income

Create a line item for each unit rental income. If you are analyzing a single-family rental, this is one line. For a fourplex, you need four lines. Include a line for other income sources — application fees, late fees, pet fees, laundry income, parking fees, and storage fees. Below the individual lines, calculate the gross potential income (all units rented at market rate for 12 months) and then apply a vacancy and credit loss factor. Use 5 to 8 percent for strong markets with low vacancy and 8 to 12 percent for softer markets or properties that are not yet stabilized. The result is your effective gross income.

Research actual market rents, not the rents the seller claims the property can achieve. Use Zillow, Rentometer, and Craigslist to find comparable rentals in the neighborhood. If the property is occupied, request copies of the current leases. If the seller claims the property can rent for $1,500 per unit but comparable properties in the area rent for $1,200, use $1,200 in your analysis. You can always adjust your offer if you believe the property can achieve higher rents after renovations, but your base case should reflect current market conditions. Our rental property analysis guide covers income estimation in detail.

Section 3: Operating Expenses

Operating expenses are where most beginning investors make their biggest analytical mistakes — they dramatically underestimate expenses, which inflates the projected returns and leads to purchases that underperform. Your expense section should include separate line items for property taxes (verify with the county assessor — do not use the seller tax bill, which may reflect a lower assessed value), insurance (get a quote for your specific property and coverage level), property management (8 to 12 percent of effective gross income, even if you self-manage — this accounts for the value of your time and allows you to hire a manager later without destroying your cash flow), maintenance and repairs (budget 8 to 15 percent of gross income depending on the property age and condition), capital expenditure reserves (budget 5 to 10 percent of gross income for major replacement items like roof, HVAC, water heater, and appliances), water and sewer (if landlord-paid), trash collection, landscaping, snow removal, HOA fees (if applicable), and any other recurring expenses.

A common rule of thumb is that total operating expenses will consume 45 to 55 percent of gross income for residential rental properties, excluding debt service. If your expense projection shows 30 percent of gross income, you are probably missing something. If it shows 60 percent, you may be in a high-tax jurisdiction or the property has unusually high insurance costs. The 50 percent rule is a useful sanity check, not a substitute for detailed expense analysis.

Section 4: Key Metrics

With your income and expense sections complete, you can calculate the metrics that drive your investment decision. Net Operating Income (NOI) equals effective gross income minus total operating expenses. The cap rate equals NOI divided by the purchase price — this tells you the property yield independent of financing. Annual cash flow equals NOI minus annual debt service (the total of 12 monthly mortgage payments). Cash-on-cash return equals annual cash flow divided by total cash invested. These four numbers — NOI, cap rate, cash flow, and cash-on-cash return — form the foundation of every rental property analysis.

Add a debt service coverage ratio (DSCR) calculation: NOI divided by annual debt service. A DSCR of 1.25 means the property income covers the mortgage payment by 125 percent, leaving a 25 percent cushion for unexpected expenses or vacancies. Most lenders require a minimum DSCR of 1.20 to 1.25. A DSCR below 1.0 means the property does not generate enough income to cover the mortgage — a red flag that the deal does not work at the proposed purchase price and financing terms. For a deeper understanding of these metrics, read our comparison of cash-on-cash return, ROI, and IRR.

Section 5: Multi-Year Projections

A single-year snapshot tells you whether a property works today. A multi-year projection tells you how the investment performs over your planned holding period. Create a five or ten-year projection that includes annual rent increases (use 2 to 3 percent per year as a conservative assumption), annual expense increases (also 2 to 3 percent — expenses generally rise in line with or slightly faster than rents), principal paydown on the mortgage (your loan amortization schedule shows how much principal is paid each year), and projected property appreciation (use 2 to 4 percent annually, consistent with long-term national averages, adjusted for your specific market).

The multi-year projection allows you to calculate your total return at various exit points. If you sell at the end of year five, your total return includes five years of cash flow, five years of principal paydown, and the difference between your sale price and purchase price minus selling costs. Express this as both a total ROI and an IRR to get the complete picture. The IRR calculation in Excel uses the XIRR function, which takes an array of cash flows and an array of dates and returns the annualized internal rate of return.

Section 6: Sensitivity Analysis

The most useful addition to any investment spreadsheet is a simple sensitivity analysis that shows how your returns change under different assumptions. Create a table that varies two inputs — typically the purchase price and the vacancy rate — and shows the resulting cash-on-cash return for each combination. This allows you to quickly see your downside — if vacancy runs at 15 percent instead of 8 percent, does the property still generate positive cash flow? If you pay $10,000 more than your initial offer, how much does that reduce your return?

You can build a sensitivity table in Excel using a two-variable data table (Data > What-If Analysis > Data Table). In Google Sheets, you will need to create the table manually with formulas that reference your input cells. Either way, the sensitivity table transforms your spreadsheet from a point estimate (the deal returns 9.5 percent) into a range of outcomes (the deal returns between 6 percent and 12 percent depending on vacancy and rent growth), which is a much more honest and useful way to evaluate a deal.

Common Spreadsheet Mistakes

The most common mistake is using the seller provided expense numbers without verification. Sellers have every incentive to understate expenses — they may exclude management fees because they self-manage, defer maintenance to inflate cash flow, or use an insurance policy with inadequate coverage. Always build your expense budget from scratch using your own research and quotes. The second most common mistake is ignoring capital expenditure reserves. A property that shows positive cash flow but has a 20-year-old roof, a 15-year-old HVAC system, and original water heater is not really cash flowing — the deferred maintenance is a liability that will come due and will consume several years of cash flow when it does.

The third mistake is analyzing a deal at list price only. Your spreadsheet should tell you the maximum price you can pay and still meet your minimum return thresholds. Work backward from your target cash-on-cash return to determine your maximum purchase price. If the seller is asking $300,000 but your spreadsheet shows that the deal only works at $275,000, your offer is $275,000. The spreadsheet removes emotion from the negotiation.

Putting It All Together

Build your spreadsheet once, refine it over your first five deals, and use it for every deal you evaluate going forward. The disciplined use of a standardized analysis tool is what separates successful investors from those who buy on gut instinct and regret it later. Your spreadsheet forces you to research actual rents, actual expenses, and actual financing terms for every property before making an offer. It calculates the metrics that matter — NOI, cash flow, cash-on-cash return, cap rate, DSCR, and IRR — and presents them in a format that supports a clear go or no-go decision.

Start with Google Sheets so your analysis is accessible from anywhere and shareable with partners, lenders, and mentors. Save a blank template version and create a copy for each property you analyze. Over time, your collection of completed analyses becomes a database of market knowledge — you will be able to look back at deals you analyzed, compare them to deals you purchased, and refine your assumptions based on actual performance data. Combine your spreadsheet with our online calculators for quick initial screening, then use the full spreadsheet for deals that pass your preliminary criteria. This two-step process lets you screen dozens of deals efficiently and perform deep analysis only on the ones that merit serious consideration.