A rental property analysis spreadsheet: what it has to contain

A spreadsheet earns its place over a calculator when you need to keep the numbers, change one and see everything else move, or put several properties next to each other. Whether you build one or download one, the useful ones are made of the same parts.

Here is what those parts are, the one-row version you can try below, and how to tell a spreadsheet you can trust from one that only looks finished.

Nothing here is sent anywhere and nothing is stored. This is one row of the free Deal Screener, for one property, with the same six inputs and the same arithmetic: Maple Street Duplex comes out at $214 a month, 3.21% cash-on-cash and $79,800 to close, exactly as in the file.

Six numbers get a first pass going: price, rent, down payment, interest rate, loan term and closing costs. That is what the calculator above uses and what the Deal Screener asks for on each of its five properties.

A full analysis needs the rest of the picture, line by line: property taxes, insurance, utilities you pay, HOA, a vacancy rate, management, repairs, a CapEx reserve kept apart from repairs, leasing costs, rehab, reserves and, if the property needs work, an after-repair value. That is the difference between screening a property and analyzing one.

Either works, and a good file works in both. The trap is functions that behave differently in the two programs. The Platwright catalog found one the hard way: a function Excel reads only for the first value of a range made a yearly total come out wrong in Excel while it was right everywhere else. The whole catalog now runs on sixteen functions, each one checked in every program the files open in.

Can you read every formula? A protected sheet is fine; a hidden or locked formula you cannot inspect is not. Does it say what it leaves out? Does it come with a worked example you can check by hand? And does whoever made it say how it was tested? How We Test is that answer for these files: 2,973 cell-by-cell comparisons, 90 destructive tests and 1,677 printed texts across the five templates.

The Deal Screener is the free version of this page for five properties at once, in Excel and Google Sheets. When one of them is worth a closer look, the Rental Property Analyzer takes every expense line, three scenarios side by side, a thirty-year projection and the full amortization schedule.