How to calculate cap rate in Excel

Step 1: Net operating income from rent, vacancy and expenses

Put one row per property: price, monthly rent, a vacancy % and monthly operating expenses. Vacancy goes in its own column because no rental is full every month. 5% means you expect about 18 empty days a year, plus rent you never collect. Use your own estimate for the area; the sheet only does the arithmetic.

Operating expenses are the costs of running the property: property tax, insurance, repairs and maintenance, property management, HOA fees and any utilities the owner pays. For the Oak St duplex they come to $980 a month.

NOI is yearly rent after vacancy minus yearly expenses. $2,900 x 12 is $34,800 of rent; 5% vacancy leaves $33,060; take away $11,760 of expenses and NOI is $21,300. Keep everything yearly: a monthly NOI divided by a price gives a cap rate twelve times too small.

Step 2: Cap rate, and comparing listings

Divide NOI by price and format the cell as a percentage. With NOI in column F, =F2/B2 copied down gives a cap rate for every listing: Oak St 6.87%, Pine Rd 6.48%, Cedar Ln 6.35%, Birch Ct 6.18% and Elm Ave 5.77%.

In Excel 365, =SORTBY(A2:A6,G2:G6,-1) lists the properties from highest to lowest cap rate, and =XLOOKUP(MAX(G2:G6),G2:G6,A2:A6) names the top one. A higher cap rate means more income per dollar of price. It often comes with older buildings, weaker areas or more work, so it's a way to compare listings, not a verdict on which to buy.

Use the price you'd actually pay. Add closing costs and any repairs you must do on day one, and Oak St's cap rate drops: with $9,000 of costs on top, $21,300 / $319,000 is 6.68%.

Why the mortgage payment stays out of NOI

NOI leaves out the loan on purpose. Cap rate describes the property as if you paid cash, so two buyers with different down payments and interest rates get the same cap rate for the same building. That's what makes it useful for comparing listings.

What the loan does to your own pocket is cash flow, a separate number: NOI / 12 minus the monthly mortgage. Oak St's $1,775 of monthly NOI less a $1,650 payment leaves about $125 a month. Elm Ave comes out at -$135 and Birch Ct at -$36: positive cap rates with negative cash flow, because the mortgage takes all of the NOI and more.

If you subtract the mortgage before dividing, Oak St's "cap rate" becomes 0.48%. That number means nothing to anyone else, because it depends on your loan. Keep the two calculations in separate columns. The real-estate hub's rental template also works out cash-on-cash return, the number that does include your financing.

The price a target cap rate implies

Flip the formula to ask a different question: what price gives the cap rate I want? Price = NOI / target cap rate. Put your target in an input cell (K2 holds 6.5% here; your number goes there) and divide each NOI by it.

At 6.5%, Oak St's $21,300 of NOI supports $327,692, about $17,700 above the $310,000 asking price. Elm Ave supports only $235,385, about $29,600 below its asking price. =F2:F6/$K$2-B2:B6 in Excel 365 shows the gap for every listing at once.

The result is only as good as the inputs. A target cap rate is a choice you make, not a fact about the market, and the NOI rests on your vacancy and expense estimates. Treat the implied price as one number to weigh, not as what the property is worth.

Test the vacancy and expense estimates

Two estimates drive the answer: vacancy and expenses. Change them and watch the cap rate move. With 10% vacancy instead of 5%, Oak St falls from 6.87% to 6.31%. Leave vacancy and expenses out altogether and rent / price gives 11.2%. That's the gross yield some listings advertise as the "cap rate", and it's not one.

Keep the estimates in their own cells, never typed into the formula, so you can see and change them. Rental listings often quote last year's expenses from the seller; check the property tax bill and insurance quote yourself, because both can change when the property sells.