The Rental Analysis Spreadsheet: Build It Right or Skip It

Building your own rental property analysis spreadsheet is worth doing exactly once. The build forces you to decide what vacancy is a percentage of, whether capital reserves are an expense, and what the payment formula wants as inputs. Those decisions are the difference between an investor who understands a pro forma and one who types numbers into a box.

The risk is that a homemade sheet is unaudited software. On the example deal in this post, one common bug, leaving out capital reserves, moves cash-on-cash return from 3.58% to 5.42% without changing a single fact about the property. This guide gives you the four blocks, the formulas in plain terms, and the five bugs to hunt for before you trust the output.

The case for building your own spreadsheet once

A calculator gives you answers. A spreadsheet you built gives you the structure behind them: income flows down to effective gross income, expenses come off to reach net operating income, debt service comes off to reach cash flow, and the return metrics are ratios of those lines to price or cash invested. Once you have wired that yourself, you can read any broker's pro forma and see what they left out.

Three design rules keep the sheet honest.

1. Separate inputs from formulas. Put every assumption in one input area, shade those cells, and let every other cell be a formula. A number typed into the middle of a formula is a bug waiting for the next deal.
2. Pick one time unit. Make the whole model annual and convert monthly inputs once, at the input area. Show monthly cash flow only as a final output, annual divided by 12.
3. Label units in the row name. "Insurance (annual $)" and "Management (% of collected rent)" remove most of the errors in section five.

The example used throughout is a labeled illustration: a $240,000 single family house, $2,200 monthly rent, 25% down, and an example rate of 6.5% on a 30-year loan. The rate is an assumption for the math, not a quote.

The income and expense blocks: formulas included

Income block

Vacancy is a percentage of gross potential rent, and it is applied once, here. Everything below this line works from effective gross income (EGI) or from its own input.

Expense block

Check the arithmetic: $2,006 plus $2,880 plus $1,320 plus $1,320 plus $1,320 is $8,846, and $25,080 minus $8,846 is $16,234.

Two choices deserve a comment. Management is a percentage of collected rent because that is how managers bill. Maintenance and reserves are tied to gross potential rent because a furnace does not age more slowly when the house is empty. The percentages are rules of thumb. Replace them with real quotes and a component-by-component reserve estimate when you have them, and read garbage in, garbage out on calculator inputs before trusting any default.

A purist will point out that appraisers and lenders often exclude capital reserves from net operating income. If you want a cap rate that matches broker math, compute NOI both ways and label them. What you cannot do is leave reserves out of cash flow.

The financing block: payment math that is actually right

Inputs: purchase price, down payment percentage, annual interest rate, loan term in years.

Loan amount = Price x (1 - down payment %) gives $240,000 x 0.75, or $180,000.

The payment formula is where most sheets break. The standard amortizing payment is:

M = P x r x (1 + r)^n / ((1 + r)^n - 1)

P is the loan amount, r is the monthly rate, and n is the number of monthly payments. The spreadsheet function does the same thing:

Monthly payment = PMT(annual rate / 12, years x 12, -loan amount)

All three arguments need care. The rate must be monthly, so divide the annual rate by 12. The number of periods must be in months, so multiply years by 12. The loan amount is entered as a negative because the function treats money received and money paid as opposite signs; without the minus sign the payment comes back negative and flips every line that depends on it.

For the example: r is 0.065 divided by 12, n is 360, and the payment is $1,138 a month. Annual debt service, from the unrounded payment, is $13,653.

Add two more rows that people skip:

• Loan balance at year N, using the balance formula or the cumulative principal function, so you can compute equity later.
• Mortgage insurance, if the down payment is under 20% on a conventional loan. It is a monthly cost that belongs in debt service until it drops off.

Then build the cash-to-close row, because every return metric depends on it:

The output block: cash flow, cap rate, cash-on-cash

Every output is one line of arithmetic on the blocks above.

Read them together. The loan costs about 7.6% of its balance a year in payments ($13,653 divided by $180,000), more than the 6.76% the property yields, so borrowing pulls the cash return down to 3.58%. A DSCR of 1.19 sits just under the 1.20 to 1.25 that lenders commonly want on income-qualified loans. Break-even occupancy of 85.2% means the property needs to collect a little more than ten months of rent a year to cover its bills.

Note that cap rate ignores financing entirely and cash-on-cash ignores appreciation and principal paydown. Neither is wrong. They answer different questions, and the sheet should show both.

The five formula bugs in most homemade sheets

These are the errors that show up again and again when people share their sheets. Each one is tested against the example so you can see the size of the damage.

1. Payment formula errors. The classic is feeding the annual rate and the term in years into the payment function and dividing the result by 12. For the example that returns $1,149 instead of $1,138. An $11 monthly error sounds trivial, which is exactly why it survives for years. The louder version, a monthly rate with 30 periods instead of 360, returns $6,517 and gets caught. Test your sheet against a known case: $100,000 at 6% for 30 years is $600 a month, rounded to the dollar. If your sheet says anything else, the payment block is wrong.

2. Vacancy applied in the wrong place. Some sheets compute every expense from gross rent and then haircut the bottom line: cash flow x 95%. In the example that produces $3,605 instead of $2,581, a $1,024 overstatement, because a 5% cut to a small number is far less than a 5% cut to rent. Others subtract vacancy at the top and again as an expense line, which understates. Vacancy comes off gross potential rent, once.

3. Capital reserves missing. Roofs, furnaces and water heaters are certain costs with uncertain timing. Leave out the $1,320 reserve and cash flow reads $3,901, or $325 a month, and cash-on-cash jumps from 3.58% to 5.42%. Nothing about the house improved. The sheet just stopped counting a cost the owner will pay.

4. Annual and monthly confusion. Insurance typed as $110 in a column the sheet treats as annual understates expenses by $1,210 and lifts cash flow to $3,791. The reverse error, a $2,880 tax bill read as monthly, is large enough to notice. The quiet direction is the dangerous one. Rule two in the first section, one time unit with conversions at the input area, prevents this entire class.

5. Circular references. These appear when two cells depend on each other. Typical causes: loan points included in closing costs while closing costs are financed into the loan, a management fee computed from cash flow that already has the fee subtracted, or a down payment solved from a target cash-on-cash. The spreadsheet either throws a warning or, with iterative calculation switched on, quietly settles on a value you cannot audit. Break the loop by making one side a typed input.

Notice the direction. Almost every common bug makes the deal look better, and nobody goes hunting for an error in a sheet that says yes.

When the spreadsheet stops being worth it

A one-year, one-loan sheet like this takes an afternoon and teaches you the structure. The cost rises fast when you ask it for more:

• Multi-year projections. Rent growth, expense growth, amortization and a sale in year N mean a 30-column model where one misplaced anchor corrupts everything to its right.
• Loan variety. FHA mortgage insurance, PMI that drops at 80% loan-to-value, interest-only periods and adjustable rates each need their own logic.
• IRR. It needs a correctly signed cash flow series with net sale proceeds in the final year.
• Sensitivity. A two-variable data table is fragile, recalculates slowly, and breaks when you move an input cell. The reasons to do it anyway are in our piece on sensitivity analysis.
• Version drift. By the tenth deal you have ten copies with ten slightly different formulas, and you no longer know which one is right.

That is the point where maintaining software stops being a good use of an investor's time. The single family calculator is the same four blocks described here, with the formulas audited, edge cases such as mortgage insurance drop-off and interest-only loans handled, and sensitivity grids and break-even figures built in. It is free to try on your next deal.

Practical next steps: build the one-year sheet using the tables above, run the $100,000 payment test, and enter the example deal to confirm you get $2,581. Then run one live deal through both your sheet and the calculator. If the numbers match, you understand the model. If they differ, the difference is one of the five bugs, and finding it is the most useful hour you will spend on underwriting this year.