Cap Rate Calculator in Excel: Solve for Value, NOI, or the Rate
The capitalization rate is one equation with three variables — and depending on the day, you need to solve for any of them. Pricing a deal, you know NOI and the asking price and want the rate. Valuing a property, you know NOI and a market cap rate and want the value. Screening a target, you know what you can pay and the market rate, and want the NOI the deal must produce.
The free cap rate calculator on this page solves all three directions from any two inputs. This guide covers the formula, the Excel logic behind the calculator so you can wire it into your own workbook, and the comps discipline that makes the output mean something. All figures are illustrative examples, not market data.
One Formula, Three Solves
The relationship: Cap Rate = NOI ÷ Value
Rearranged, it answers all three questions:
| You know | You want | Formula |
|---|---|---|
| NOI and price/value | Cap rate | Cap = NOI ÷ Value |
| NOI and market cap rate | Value | Value = NOI ÷ Cap |
| Value and market cap rate | Required NOI | NOI = Value × Cap |
Worked example, all three ways (illustrative): a property producing $210,000 of NOI, priced at $3,500,000.
- Solve for the rate: $210,000 ÷ $3,500,000 = 6.0%
- Solve for value: if comparable sales support a 6.0% cap, a property producing $210,000 of NOI is worth $210,000 ÷ 0.06 = $3,500,000
- Solve for NOI: to justify a $3,500,000 price at a 6.0% market cap, the property must produce $3,500,000 × 0.06 = $210,000
The third solve is the underused one: it converts an asking price into an income requirement you can test against the rent roll and expenses — often the fastest way to expose an aggressive price.
The offer-pricing walk, in practice: a listing asks $3,850,000 in a segment your comps grid supports at 6.0%. Required NOI = $3,850,000 × 0.06 = $231,000. You rebuild NOI from the rent roll and T-12 and get $205,000 — the property, as it operates, supports $205,000 ÷ 0.06 = $3,417,000 at the market rate (illustrative). The gap is now specific and negotiable: either the seller's price assumes income the property does not yet produce, or it assumes a cap rate the comps do not support, and the calculator has told you which conversation to have. Run in this direction, a cap rate calculator is an offer-pricing tool, not just a metric.
The Input That Decides Everything: Which NOI?
The formula is trivial; the inputs are not. Cap rate is only as honest as the NOI you feed it, and three conventions matter:
- Use true NOI — effective gross income minus operating expenses, before debt service, income taxes, and capital expenditures, with a market management fee included. Seller materials that omit management or use gross income produce flattered cap rates. Our NOI calculator guide covers exactly what belongs in and out.
- State the vintage. In-place NOI (what the property does today) and pro forma NOI (what the buyer hopes it will do) produce two different cap rates on the same price. A "6.5% cap" computed on the seller's pro forma may be a 5.2% cap on in-place income — and the difference is the whole negotiation.
- Match the convention to the comp. When you compare your deal's cap rate to comparable sales, the NOI conventions must match. A comp reported on trailing NOI is not comparable to your deal computed on stabilized pro forma.
Building It in Excel
The calculator logic is three cells deep, and building it yourself is worth ten minutes:
- Inputs:
B2= NOI,B3= Value,B4= Cap Rate — formatted as visually distinct input cells - Solve for rate:
=B2/B3, formatted as a percentage to one decimal - Solve for value:
=B2/B4 - Solve for NOI:
=B3*B4
Two Excel-specific disciplines: format cap rates as percentages to one decimal (6.0%, not 6% and not 6.04%) — that is the market's quoting convention — and never chain the three formulas into a circular reference by making all three cells formulas at once. Two inputs, one output, always.
The more useful build is the value sensitivity row: hold NOI fixed and compute value across a range of cap rates in 25-basis-point steps. At $210,000 of NOI (illustrative):
| Cap rate | 5.50% | 5.75% | 6.00% | 6.25% | 6.50% |
|---|---|---|---|---|---|
| Implied value | $3.82M | $3.65M | $3.50M | $3.36M | $3.23M |
Read what that row is telling you: a quarter-point of cap rate moves this property's value by roughly $140,000–$150,000, and the half-point spread from 5.75% to 6.25% is a swing of about $290,000 — on identical income. Nothing about the building changed; only the market's pricing of the income stream did. This is why arguing over the cap rate is arguing over the price, and why serious underwriting stresses the exit cap rather than assuming it.
The Comps Grid: Where the Rate Comes From
A cap rate calculator tells you the arithmetic; a comps grid tells you whether the rate you used is defensible. The working format is a small table of recent comparable sales — property, sale date, price, NOI, computed cap rate, and a notes column for the adjustments — from which you derive the range your subject property belongs in.
The discipline is in the notes column. Comps need adjusting for vintage and condition, location quality, occupancy at sale, whether the reported NOI was trailing or pro forma, and any distress or special circumstances around the trade. An unadjusted average of five headline cap rates is pseudo-precision; a reasoned range from adjusted comps is a valuation opinion you can defend. When cap rates for your market segment appear in published surveys, treat them as context for your grid, not a substitute for it.
What the Calculator Cannot Tell You
The cap rate is a snapshot: one year of income against a price, with no view of growth, capital needs, financing, or exit. Two properties at identical 6.0% caps can be wildly different investments — one with rents 15% under market and a value-add path, one fully stabilized with major capital expenditures looming. The snapshot prices today's income; it says nothing about the return you will earn over a hold. That is the job of the IRR and the full 10-year model, and the trade-offs between the two metrics are covered in cap rate vs. IRR. For the deeper treatment of the rate itself — going-in versus terminal, and the situations where the number actively misleads — see how to calculate cap rate.
Frequently Asked Questions
What is the cap rate formula in Excel?
=NOI/Value, formatted as a percentage to one decimal. The rearrangements are =NOI/CapRate for value and =Value*CapRate for required NOI.
What is a good cap rate? There is no universal good number — cap rates price risk and growth expectations, so they vary by asset class, market, and property quality. Lower caps mean the market pays more per dollar of income (typically safer or higher-growth assets); higher caps mean cheaper income with more risk attached. The honest benchmark is adjusted comparable sales in your specific segment.
Does cap rate include mortgage payments? No. Cap rate is an unlevered metric — NOI sits above debt service, so the ratio describes the property independent of how it is financed. That is precisely what makes it comparable across buyers with different capital structures.
Should I use purchase price or appraised value? For a going-in cap rate on an acquisition, use the total purchase price (and be consistent about whether closing costs are in). For valuation work, the formula runs the other way — you apply a market cap rate to NOI to produce the value.
Why do small cap rate changes move value so much? Because value is NOI divided by the rate, and dividing by a small number amplifies changes to it. At a 6% cap, a 25-basis-point move shifts value by roughly 4% — which on institutional price tags is real money.
From Calculator to Toolkit
The free calculator on this page runs the three solves. The Cap Rate & Property Valuation Toolkit is the full Excel implementation: the three-way solver, a structured comps-adjustment grid, direct capitalization valuation, and the cap-rate sensitivity table — fully unlocked, formula-transparent, with a documented methodology PDF.
Prefer a guided web app over a spreadsheet? The YieldSheets underwriting platform is in development — join the waitlist for access when it opens.
This article is for educational purposes only and does not constitute investment, legal, or tax advice. All figures are illustrative examples, not market data. Consult qualified professionals before making investment decisions.
Get the next breakdown in your inbox
New CRE modeling walkthroughs 3× per week. No spam, unsubscribe anytime.
