Building a Rental Analysis Spreadsheet That Doesn't Fall Apart
I've built dozens of these over the years. The ones people download from random websites are usually useless because they don't account for how properties actually behave. Here is how I build mine and what to watch out for. Start with a clean input section. Put everything that could change on its own sheet, separate from your calculation logic. Purchase price, closing costs, loan terms, rental income per unit, vacancy assumptions, and expense line items. Then your formulas pull from those inputs. When you need to run 20 different scenarios, you only ever touch the input sheet. This usually cuts your revision time from hours down to minutes.
Real Estate Rental Analysis Excel Spreadsheet
The structure I use has six logical sections. Inputs on the first tab, a rent roll on the second, a monthly operating year on the third, annual summaries on the fourth, sensitivity analysis on the fifth, and a one-page output dashboard. People skip the rent roll and try to build everything in one sheet. It works fine for a single unit. It falls apart fast with four units or more. On the rent roll, list each unit with its monthly rent, lease start date, lease end date, deposit, and any lease escalation terms. This lets you model turnover timing without guessing. Vacancy is not a flat percentage applied uniformly across the year. If Unit 3 turns over in October, it is vacant for October through January, not 5% of every month. For income, calculate gross scheduled rent first. Then subtract vacancy and credit loss. Credit loss matters more than people admit. Late payments, partial payments, and collections add up. I use 3 percent of gross rent for credit loss in stable markets and 5 percent in tighter ones. Property tax reassessments after a sale are another place where these spreadsheets usually fail. Your model will look great until the first reassessment hits and you did not build in a mechanism to adjust the tax line. I added a manual override field for the tax assessment value and tied it to the purchase price with an assessment ratio input. That small addition prevented me from presenting investors a false picture on three deals in one year.
Operating Expense Structure
Operating expenses need their own detail. Separate property management fees, insurance, utilities, repairs and maintenance, property taxes, HOA fees, and capital expenditures. Property management is typically 8 to 10 percent of collected rent, not gross rent. Insurance varies wildly by location and building age. Repairs and maintenance should be a reserve per unit per month, not a guessed annual total. $100 to $150 per unit per year is a rough baseline for older properties. Capital expenditures are different and should be tracked separately. Roof replacement, HVAC, water heater, flooring, appliances, parking lot resurfacing, siding. These are not monthly expenses. They are periodic large costs that can destroy your cash flow if ignored. I build a CapEx schedule on its own tab. Each asset gets an expected lifespan and a replacement cost. The spreadsheet then allocates an annual reserve based on those assumptions. It is more accurate than tossing a round number into your operating expenses. Most beginners either ignore CapEx entirely or fold it into maintenance. Both approaches give you misleading numbers.
Get the Full Details
Financing and Key Metrics
The loan section needs purchase price, down payment, loan amount, interest rate, amortization period, and whether the rate is fixed or adjustable. If it is adjustable, add an ARM adjustment schedule. Debt service calculation uses the standard PMT function. Make sure you distinguish between the actual monthly payment and the principal and interest breakdown. Some lenders include escrow in the payment. If you are modeling cash flow, escrow goes into your tax and insurance line, not the debt service line. Mixing the two corrupts your DSCR. Gross operating income minus total operating expenses equals net operating income. Net operating income divided by purchase price gives you the cap rate. Do not use a cap rate from Zillow or Redfin as your underwriting number. Those are often based on outdated tax assessments or inflated comparable sales. Run your own comp analysis and use that. Net operating income minus debt service gives you annual pre-tax cash flow. Cash flow divided by your total cash invested gives you cash-on-cash return. Annual pre-tax cash flow divided by net operating income gives you the debt service coverage ratio. Anything below 1.25 is risky for conventional lending in most markets today.
Sensitivity and Stress Testing
A static model is dangerous. Add data tables for vacancy rate, rental income, and operating expense growth. Run scenarios at 0, 5, 10, and 15 percent vacancy. Run them at -5, 0, +5, and +10 percent rent changes. Operating expenses typically escalate 3 to 5 percent annually. If your model assumes flat expenses for five years, it is not useful for a hold strategy. Build an annual escalation assumption for each major expense category. Property taxes and insurance escalate faster than maintenance in most areas. I also add a waterfall analysis for syndication deals if equity split matters. Preferred return, catch-up, promoter split. Getting that wrong means your returns to partners are incorrect and investors notice quickly.
Practical Limitations
Excel is not the right tool for every situation. If you are analyzing a 200-unit apartment complex, you need ARGUS or a dedicated pro forma platform. Excel will become unmanageable with that level of detail and you will introduce errors no amount of checking will catch. For single-family rentals, small multi-family up to about 10 units, and simple commercial leases with triple net structures, this spreadsheet approach works fine. Mixed-use properties with retail on the bottom and residential above require separate income and expense categories for each use type. You can still do it in Excel but the model gets complex enough that you should consider a specialized tool or a more modular Excel design with separate tabs per use type. Another limitation people ignore is lease structure variation. A property with month-to-month tenants behaves very differently from one with three-year leases in place. Your model should let you switch between stabilized income and lease-by-lease income depending on the deal stage. I keep a toggle for this. During acquisition, I model based on current leases. After purchase, I switch to stabilized assumptions to reflect the run rate. The spreadsheet file I use is available for download. It includes the rent roll, the CapEx schedule, the financing inputs, and the sensitivity data tables. I do not provide advice on whether a specific deal is good. This is a modeling tool. The numbers it produces are only as reliable as the inputs you put into it.
