Rental Property Analysis in Excel: Cash Flow and Cap Rate

A rental property analysis in Excel answers two questions about a property: how much income it earns after running costs, and how much cash is left after the mortgage. You enter the price, rent and costs once, and a few formulas give you net operating income, cap rate, yearly cash flow and cash-on-cash return. This guide builds the sheet step by step on example figures. It explains the calculations only; it is not investment advice.

What a rental property analysis in Excel includes#

A rental analysis follows the money from rent down to what the owner keeps. These are the terms you'll use:

The key idea: NOI ignores the mortgage, so it describes the property itself. Cash flow includes the mortgage, so it describes your deal.

Step 1: Set up the inputs#

Put every assumption in its own cell, with a label in column A and the value in column B. All formulas then point at these cells, so changing one number updates the whole sheet. These are example figures:

Type percentages with the % sign (20%), and Excel stores them as decimals (0.2). Colour the input cells differently, for example light yellow, so nobody types over a formula by mistake.

Step 2: Work out net operating income (NOI)#

NOI is the rent you actually collect minus the cost of running the property. Build it in rows 14 to 19:

The SUM function accepts more than one range, separated by commas, so SUM(B9:B11,B17) adds the three yearly costs and the management fee in one go. The management fee is a percentage of rent collected, so it is calculated from row 16, not from gross rent.

Add any other running costs your property has, such as utilities you pay, HOA or service charges, or a reserve for big repairs like a roof. Each one is a new input row and goes inside the SUM.

Step 3: Calculate the cap rate#

The cap rate (capitalisation rate) is NOI divided by the purchase price. In B20:

=B19/B2

The result is 0.0633. Format it as a percentage (Windows: Ctrl+Shift+%; Mac: ⌃⇧%) to show 6.33%. The cap rate tells you what the property earns each year as if you paid all cash. That makes it useful for comparing two properties with different prices, because the mortgage doesn't affect it.

Step 4: Add the mortgage with PMT#

The PMT function returns the monthly payment on a loan. First work out the loan amount, then the payment:

PMT needs a monthly rate, so the yearly rate is divided by 12, and the number of payments is the term in years × 12. The minus sign before B21 makes the payment show as a positive number. The guide to the PMT function in Excel explains each argument and the sign rules.

The payment includes both interest and principal. Principal paid down builds your equity but still leaves your bank account, so cash flow counts the whole payment. To see how each payment splits, build a loan amortization schedule.

Step 5: Cash flow and cash-on-cash return#

Cash flow is what is left after the mortgage. Cash-on-cash return compares it with the cash you put in:

The example shows why both measures matter. The cap rate of 6.33% looks reasonable, but after the mortgage the property leaves only about 655 a year, or roughly 55 a month. Debt service coverage (NOI ÷ debt service) of 1.04 means NOI covers the mortgage with only 4% to spare. A small repair bill or a longer vacancy would push cash flow below zero.

Test what-if changes#

Because every formula points at the input cells, you can test a different deal by changing one number. These results come from the same sheet, with all other inputs unchanged:

Notice that the down payment and interest rate change cash flow but not NOI, because NOI ignores financing. To compare several inputs at once, use a what-if data table or save named cases with Scenario Manager. To find the rent that gives exactly zero cash flow, use Goal Seek.

This sheet looks at one typical year. For several years with rising rents and costs, lay the years out in columns as in a cash flow forecast in Excel, and measure the return over the whole holding period with the IRR function. Taxes, depreciation and changes in the property's value are left out here on purpose; add them only with figures you have checked.

Common mistakes in a rental analysis#

These slips make a property look better than it is:

1. Counting the mortgage as an operating expense. It belongs below NOI. Mixing it in makes the cap rate meaningless.
2. Forgetting vacancy. Few rentals are let 12 months a year, every year.
3. Using a yearly rate in PMT. Divide the rate by 12 and multiply the years by 12 for monthly payments.
4. Hard-typing numbers into formulas. =B16*0.08 hides the management fee. Keep it in an input cell instead.
5. Leaving out closing costs. They are part of the cash you invest, so they lower cash-on-cash return.

For the other spreadsheet tasks in property jobs, such as price per square foot and commission, see Excel skills for real estate.

Practise it#

The exercise on this page asks you to calculate a cap rate from a small sheet. Then work through the real estate case study in a live spreadsheet: you'll answer listing questions with formulas and get instant feedback. To practise totals, percentages and input cells, build the department budget practice sheet next.