How to Calculate Cap Rate in Google Sheets (Free Template)

From Sort & Keep, the maker of the linked templates. Our current product prices update with your selected currency. Worked examples and third-party prices keep their stated currency and date.

Cap rate is the most misused metric in real estate. Estate agents quote it to make listings look attractive, investors throw it around to justify deals, and half the time the number is wrong because somebody divided gross rent by purchase price and called it a day. If you want to know how to calculate cap rate correctly, you need to understand what it actually measures -- and what it deliberately leaves out.

We've seen investors walk into six-figure purchases armed with a cap rate calculated from a listing description. No vacancy adjustment. No expense breakdown. Just "annual rent divided by asking price" scribbled on the back of a viewing sheet. That's not a cap rate. That's a gross rent yield dressed up in a suit.

This guide walks through the real calculation step by step in Google Sheets, with a worked UK property example and every expense accounted for. By the end, you'll have a formula you can reuse on any deal.

What Cap Rate Actually Measures

Cap rate -- short for capitalisation rate -- answers one question: if you bought this property outright with cash, what annual return would the net income produce?

The formula:

Cap Rate = Net Operating Income (NOI) / Property Value

That's it. Two numbers. But the simplicity is deceptive because everything hinges on getting NOI right.

NOI is not gross rent. NOI is what remains after you subtract every operating expense from rental income. Taxes, insurance, maintenance, management fees, vacancy allowance -- all of it comes out before you arrive at NOI. The mortgage does not come out. That's the entire point of cap rate: it removes financing from the equation so you can compare properties regardless of how they're funded.

A property with a 7% cap rate returns 7% on its value from operations alone, whether you paid cash, put 25% down, or used 90%. Cap rate tells you about the asset. It tells you nothing about your deal structure.

How to Calculate Cap Rate in Google Sheets: Step by Step

We'll use a realistic UK buy-to-let example throughout.

The Example Property

This is a plausible deal in a northern English city in 2026. Not a bargain, not overpriced. Exactly the kind of property where the cap rate calculation separates a good investment from an average one.

Step 1: Set Up Your Input Section

Open a new Google Sheet. In column A, enter your labels. In column B, enter your values. Keep inputs separate from calculations -- this makes the sheet reusable.

B3 should return £24,000.

Step 2: Account for Vacancy

No property stays occupied 52 weeks a year, every year. Tenants leave. There's a gap between tenancies. Sometimes it takes a month to find the right tenant rather than rushing in a bad one.

We use 5% for stable areas with strong rental demand. Use 8-10% if turnover is higher in your market.

Vacancy Loss: £1,200
Effective Gross Income: £22,800

Step 3: Calculate Operating Expenses

This is where most cap rate calculations go wrong. People skip this step entirely and divide gross rent by property value. That gives you gross yield, not cap rate. The difference can be 2-3 percentage points -- enough to turn a mediocre deal into one that looks brilliant on paper.

Here's a realistic expense breakdown for a UK rental property:

A few notes: even if you self-manage, include letting agent fees -- your time has value, and if the deal only works because you're doing the work for free, it doesn't really work. We budget maintenance at 1% of property value as a baseline; older Victorian properties need more. Gas safety and EPC are legal requirements in the UK -- budget for them.

In your Google Sheet, list each expense in rows 7-15 and sum them:

Total Operating Expenses: £7,200

Step 4: Calculate NOI

Now subtract expenses from effective gross income:

NOI = £22,800 - £7,200

NOI: £15,600/year

This is the number that matters. Not gross rent. Not "what the agent told you." NOI reflects what the property actually produces after it costs money to operate.

Step 5: Calculate Cap Rate

The final step:

Format cell B18 as a percentage.

Cap Rate = £15,600 / £300,000

Cap Rate: 5.2%

Compare that to what you'd get if you'd used gross rent: £24,000 / £300,000 = 8.0%. That 2.8 percentage point gap is the difference between a realistic assessment and a fantasy. The gross yield looks like a strong deal. The actual cap rate tells you it's decent but unremarkable for Sheffield.

What's a "Good" Cap Rate?

There is no universal answer. Cap rate is context-dependent, and anyone who gives you a single number is either oversimplifying or selling something.

General UK benchmarks:

Our Sheffield example at 5.2% sits at the lower end of its market range. That could mean the purchase price is slightly high, or that the property commands premium rent for its area.

The critical thing to understand: higher cap rate does not mean better deal. Higher cap rates usually signal higher risk -- more vacancy, tougher tenant pools, less appreciation, more maintenance. A 10% cap rate in a declining town might produce worse total returns than a 4.5% cap rate in a growing city where values appreciate 5% annually. Cap rate measures income return only. It says nothing about capital growth or neighbourhood trajectory.

How to Calculate Cap Rate vs Cash-on-Cash Return vs ROI

These three metrics answer different questions. Using the wrong one leads to wrong decisions.

Cap Rate: How Does the Asset Perform?

Cap Rate = NOI / Property Value

Ignores: Financing, down payment, mortgage structure
Use when: Comparing properties against each other, regardless of how you plan to fund them

Cash-on-Cash Return: How Does My Money Perform?

Cash-on-Cash = Annual Cash Flow / Total Cash Invested

Includes: Mortgage payments (subtracted from NOI to get cash flow), deposit, stamp duty, legal fees
Use when: Deciding whether your specific deal structure produces adequate returns on the cash you're putting in

ROI (Total Return on Investment): How Did the Whole Thing Perform?

ROI = (Total Gain) / Total Cash Invested

Includes: Cash flow, appreciation, principal paydown -- everything
Use when: Evaluating a property you've held for several years, or projecting long-term hold performance

Example using our Sheffield property:

Same property, three different stories depending on which metric you read. None of them is wrong. They just answer different questions.

Common Cap Rate Mistakes That Cost Investors Money

We see these constantly, both in spreadsheets people share with us and in property listings.

Using Gross Rent Instead of NOI

This is mistake number one, and it's everywhere. Gross rent divided by property value gives you gross yield, not cap rate. We showed the difference above: 8.0% vs 5.2% on the same property. If you're comparing a listing that quotes "cap rate" against your own properly calculated figure, you might be comparing apples to oranges without realising it.

Fix: Always calculate NOI first. If someone quotes you a cap rate, ask what expenses they deducted to get there.

Forgetting Vacancy

A property with zero vacancy allowance assumes perfection. In reality, tenants leave. The average UK tenancy lasts about 2.5 years. Every changeover means at least 2-4 weeks of lost rent plus referencing and marketing costs.

Fix: Deduct 5-8% from gross rent before anything else. If actual vacancy runs lower, you'll outperform projections. That's the right kind of surprise.

Ignoring Management Fees

"I'll manage it myself" is a plan, not a permanent condition. Your time has a cost. And if you ever want to step back -- travel, scale to more properties, or simply stop fielding midnight boiler calls -- the deal needs to work with a 10% management fee baked in.

Fix: Include 10-12% letting agent fees in your expense calculation, even if you self-manage today.

Comparing Cap Rates Across Different Markets

A 7% cap rate in Liverpool and a 7% cap rate in Bristol are not equivalent deals. Liverpool likely has higher vacancy, lower appreciation, and different tenant demographics. Bristol has stronger capital growth prospects and a different risk profile. Cap rate doesn't capture any of that.

Fix: Compare cap rates within the same market to rank properties. Use other metrics (total ROI, appreciation trends, demand indicators) to compare across markets.

Using Cap Rate to Evaluate Financing Decisions

Cap rate deliberately excludes financing. It cannot tell you whether a particular mortgage makes the deal work or not. That's what cash-on-cash return is for. Use cap rate to evaluate the asset, cash-on-cash to evaluate your deal structure.

Using Asking Price Instead of Market Value

Sellers ask for more than properties are worth. If you calculate cap rate on an inflated asking price, the metric understates the true return. Run the calculation at asking price, at your offer price, and at estimated market value. The spread tells you how much negotiation use matters to the deal.

Skip the Build: Use a Pre-Built Tracker

The cap rate sheet above works. But if you're evaluating multiple properties, you'll want sensitivity tables, side-by-side comparison, and cash-on-cash calculated alongside cap rate automatically. Our Rental Property Portfolio Tracker (Sort & Keep) does all of that -- cap rate, cash-on-cash, DSCR, and IRR with sensitivity analysis built in. It's £29.99 on Sort & Keep, one-time purchase, all formulas visible and unlocked, works in Google Sheets and Excel.

UK edition built for buy-to-let landlords. Council tax bands, EPC ratings, Gas Safety Certificate tracking, leasehold vs freehold analysis, and HMRC Self Assessment categories. US edition included with Schedule E, 1031 exchange planning, and 27.5-year depreciation schedules.

The Bottom Line

Cap rate is a useful metric when calculated correctly and interpreted honestly. It tells you what a property earns from operations relative to its value, independent of financing. It does not tell you whether the deal is "good" -- that depends on your market, your risk tolerance, your financing terms, and your investment goals.

The formula is simple: NOI / Property Value. Getting NOI right is the hard part. Account for vacancy, maintenance, management, insurance, compliance costs, and every other expense that hits your bottom line before you divide. The agents and listing portals quoting cap rates almost never do this. Now you can.

Run the numbers yourself. Every time. On every deal. A Google Sheet with the right formula protects you better than any amount of advice from someone whose commission depends on you buying.

Related Reads

• Best Rental Property Spreadsheets in 2026
• Buy-to-Let vs Flip: Spreadsheet Comparison
• Landlord Expense Tracker Template
• How to Analyze a Rental Property Deal in Google Sheets (Step-by-Step)

Get free spreadsheet templates and updates. New templates, feature updates, and practical guides delivered to your inbox. No spam, unsubscribe anytime.

Subscribe free → | Already have a template? Download the free budget tracker or free meal planner. No email required.