Ask two investors what cap rate a building trades at and you can get two different answers from the same documents. Not because anyone is lying, but because the numerator is a judgment call. Cap rate is a simple ratio sitting on top of a figure that reasonable people compute differently, which is exactly why it pays to build it in Excel where every input is visible.
This is the practical version: the formula, the Excel layout, the difference between the cap rate you buy at and the one you underwrite an exit at, and the adjustments that separate a real cap rate from a broker's cap rate.
What is a cap rate?
The capitalization rate is a property's annual net operating income expressed as a percentage of its value. A building worth $10 million that produces $550,000 of net operating income trades at a 5.5% cap rate. Read it as the unlevered yield a buyer earns in year one, before any financing, and you have the right mental model.
Because it strips out debt entirely, cap rate lets you compare two buildings with completely different capital structures. That is its whole purpose. It is a comparison tool, not a return.
How do you calculate cap rate?
Divide annual net operating income by the property's current market value or purchase price, then multiply by 100 to express it as a percentage. Net operating income is effective gross income minus operating expenses, with debt service, capital expenditures, and depreciation all excluded.
As one line: Cap Rate = Net Operating Income / Property Value.
Rearranged, the same equation is how the entire income approach to valuation works: Value = Net Operating Income / Cap Rate. Pick a market cap rate, apply it to a defensible NOI, and you have an indicated value. This is why an argument about a hundred thousand dollars of NOI is really an argument about two million dollars of value at a 5% cap.
How do you calculate cap rate in Excel?
Keep the NOI build and the ratio in separate blocks. The temptation is to type one number into a cell and divide. Resist it, because the moment someone questions the expense load you want to point at a row rather than reconstruct your reasoning from memory.
| Cell | Label | Formula or value |
|---|---|---|
| B2 | Gross rental income | 1,100,000 |
| B3 | Other income | 45,000 |
| B4 | Vacancy and credit loss | =-(B2*0.06) |
| B5 | Effective gross income | =SUM(B2:B4) |
| B6 | Total operating expenses | =-505,000 |
| B7 | Net operating income | =SUM(B5:B6) |
| B9 | Purchase price | 12,000,000 |
| B10 | Cap rate | =B7/B9 |
| B12 | Implied value at 5.25% | =B7/0.0525 |
Format B10 as a percentage with two decimals. Keep vacancy and expenses as negative numbers so every subtotal is a plain SUM, which is easier to audit than a chain of minus signs. The last row is the one people forget to build: once NOI is in a cell, testing what the property is worth at a range of market cap rates costs nothing and tells you far more than the single number at the top.
What is a good cap rate?
There is no universal answer, and anyone who gives you one is selling something. A cap rate prices risk, so a low cap rate means the market believes the income stream is safe and likely to grow, and a high cap rate means it wants to be paid for uncertainty. Class A apartments in a primary market price tighter than Class C in a tertiary one, and the spread between them is the market's opinion of risk, not an inefficiency for you to arbitrage.
Industry roundups through 2026 have put average multifamily cap rates across all classes in the mid 5% range, with primary market Class A tighter than that and tertiary market Class C well above it. Commercial investors broadly look in the 4% to 10% band depending on property type. Treat those as orientation, not as a benchmark to underwrite against. The only cap rate that matters for your deal is the one comparable properties in that specific submarket actually traded at in the last few quarters.
Is a higher or lower cap rate better?
It depends on which side of the trade you are on. As a buyer, a higher cap rate means you pay less for each dollar of income, which is good if the income is real and durable. As a seller, a lower cap rate means your building is worth more. Neither is better in the abstract.
What a high cap rate usually signals is compensation for something: a weaker submarket, deferred maintenance, short remaining lease term, a single tenant with credit risk, or an operating history that will not hold. An 8.5% cap on a building whose roof has two years left is not a bargain. The discipline is to ask what the market knows that is priced into that number, and then verify it in the documents rather than in the offering memorandum's narrative.
Does cap rate include the mortgage?
No. Net operating income sits above the debt line, so principal and interest never enter the calculation. That is deliberate. Two buyers can finance the same building at 55% and 75% leverage and both compute an identical cap rate, which is what makes the ratio comparable across deals.
Financing shows up in the returns that sit downstream. Cash on cash return divides annual cash flow after debt service by the equity invested, and it moves with leverage. Cap rate answers what the asset yields. Cash on cash answers what your equity earns. Confusing the two is how a deal that looks fine on a cap rate basis turns out not to cover its own loan payments, which is what the debt service coverage ratio is there to catch.
Going-in cap rate versus exit cap rate
The going-in cap rate uses year one NOI over the purchase price. The exit or terminal cap rate is the one you assume a future buyer will pay when you sell, applied to the NOI in the year of sale. In a hold period model the exit cap rate is often the single most sensitive assumption in the entire spreadsheet, and it is entirely an assumption.
Convention among careful underwriters is to exit at a cap rate somewhere between 25 and 50 basis points wider than the going-in rate, on the theory that the building is older when you sell it. Model it as an input cell and run the sensitivity. If the deal only clears its return hurdle when you exit at a tighter cap rate than you bought at, the returns are coming from the assumption, not from the property.
Getting a defensible NOI out of the deal documents
Every serious cap rate argument is an argument about the numerator, and the numerator comes from two PDFs: the trailing twelve month operating statement and the rent roll. Until those are in cells, you are working from a broker's summary of the numbers rather than the numbers.
Convert both. The operating statement converter puts each income and expense line on its own row with the monthly columns intact, and the rent roll converter gives you one row per unit with rents, square footage, and lease dates. Scanned broker packages are common and work fine, since OCR reads the characters off the image. Then annualize in place rent from the rent roll and compare it to gross rental income on the T12. A wide gap means concessions, bad debt, or a rent roll that has gone stale, and every one of those changes the NOI you should be capping.
Three adjustments to make before you divide. Add a market management fee if the seller self manages and reports none. Subtract a replacement reserve, since a building consumes its roof and its HVAC whether or not the accounting shows it. And strip out anything nonrecurring, such as a one time insurance reimbursement sitting in other income. All three lower NOI, all three lower the cap rate you are actually buying at, and all three will be made by the lender regardless of whether you make them.
One last verification step that is worth the ten minutes: compare reported collections against the property's actual bank deposits for the same period. When the seller provides statements, turning them into a spreadsheet lets you tie collected income to the operating statement line by line. If the deposits do not support the income, nothing built on top of that NOI is worth much, cap rate included. The broader PDF to Excel for real estate workflow covers the rest of the package.
The short version
- Cap rate is NOI divided by value. Rearranged, value is NOI divided by cap rate, which is the income approach in one line.
- It excludes financing on purpose, which is what makes it comparable across deals. It is not a return on your money.
- A high cap rate is compensation for risk. Find the risk before you call it a bargain.
- Build NOI in visible rows, not inside the formula, and test value across a range of market cap rates.
- Normalize management fee, replacement reserves, and nonrecurring items before you divide, because the lender will.
The ratio takes one cell. The credibility comes from the twenty rows above it.