How to Calculate Cap Rate in Excel: Formula, Setup and Mistakes

How to calculate cap rate in Excel: put net operating income in one cell, the purchase price in another, and divide. If NOI sits in B11 and the price sits in B1, the formula is =B11/B1, formatted as a percentage. That is the whole calculation.

Everything hard happens in the rows above that cell. Excel divides whatever you hand it, so the answer is only as good as the rent, vacancy and expense lines feeding it. If you want the idea behind the number first, what cap rate measures and why it prices property covers it. This page is about the sheet.

The formulas below work in any version of Microsoft Excel, and in Google Sheets unchanged.

The two cell version

Type 30000 in B1, 500000 in B2, then =B1/B2 in B3. Format B3 as a percentage and it reads 6%.

That is a real cap rate: $30,000 of net operating income on a $500,000 price. Use this version when a listing already gives you an NOI figure and you only want to check the division. If you need the arithmetic spelled out, see the short version of the cap rate math.

Use the full layout when you are building NOI yourself, which is most of the time.

The layout that builds NOI

Keep labels in column A and numbers in column B. Type only the raw inputs. Everything else is a formula, so one change flows through the sheet.

Cell	Label	Value or formula
B1	Purchase price	500000
B2	Monthly rent	4000
B3	Annual gross rent	=B2*12
B4	Vacancy rate	5%
B5	Effective gross income	=B3*(1-B4)
B6	Property taxes	6000
B7	Insurance	2500
B8	Maintenance, reserves and management rate	18%
B9	Maintenance, reserves and management	=B5*B8
B10	Total operating expenses	=B6+B7+B9
B11	Net operating income	=B5-B10
B12	Cap rate	=B11/B1

Six cells hold typed inputs: B1, B2, B4, B6, B7 and B8. The rest calculate themselves.

Row 5 is effective gross income, the rent you actually collect. Rows 6 through 9 are the operating expenses. The 18% in B8 bundles maintenance, capital reserves and management into one share of collected rent, which is how most investors budget at the screening stage.

The worked example

Run a $500,000 house renting for $4,000 a month through the layout.

• B3 annual gross rent: $48,000
• B5 effective gross income after 5% vacancy: $45,600
• B9 maintenance, reserves and management at 18% of $45,600: $8,208
• B10 operating expenses, $6,000 of taxes plus $2,500 of insurance plus $8,208: $16,708
• B11 net operating income: $28,892
• B12 cap rate: 5.78%

Expenses came to 37% of collected income. No loan appears anywhere in the sheet.

Format the result instead of faking it

B12 returns 0.0578. Select it, apply percentage format, set two decimals, and it shows 5.78%. Cap rate is short for capitalization rate, and it is a ratio, so a decimal is the correct raw value.

Do not write =B11/B1*100 and put a percent sign in the label. Any later formula that reads B12, such as a value calculation, then has to be divided by 100 to work, and that is where sheets start lying.

To round for a report, wrap it: =ROUND(B11/B1,4).

Screening several properties at once

Give each property a column. Labels stay in A, the first property in B, the second in C. Write the formulas once down column B, select B3:B12, and fill right.

Lock anything shared with absolute references. If every property uses the same vacancy and expense rates, point the formulas at $B$4 and $B$8 so copying across does not shift them.

Wrap the cap rate so empty columns stay quiet: =IFERROR(B11/B1,"").

Then sort the row. A dozen listings rank in seconds, which is the whole reason to build the sheet. A column showing 7.5% next to one showing 4% is also telling you something about risk, so read what a 7.5% cap rate means before you act on the order.

Input mistakes that wreck the output

Monthly rent against an annual price. If B3 ends up at $4,000 instead of $48,000, the sheet returns 0.48%. A result under 1% or over 20% is almost always a units error.

The mortgage in the expense rows. Principal and interest are not operating costs. Put debt service in its own block below row 12 and leave NOI alone.

Typing 5 where the sheet wants 5%. A vacancy cell holding 5 erases the rent line. Format assumption cells as percentages so a bad entry looks wrong on sight.

Hard-coding NOI. Typing 28892 into B11 instead of =B5-B10 means a rent or tax change never reaches the cap rate.

Leaving out reserves. A sheet with taxes and insurance and nothing else turns this same house into a 7.42% cap rate, and the roof still fails. Reserves are the line people skip, and it is one of the mistakes people make with cap rate most often.

Using the asking price for a property you already own. Cap rate on an owned asset uses current market value.

Flip the formula for value or target NOI

Add B13 for a market cap rate and B14 for value. Put 6% in B13 and =B11/B13 in B14. The $28,892 of NOI prices out at about $481,533 at a 6% market cap rate.

To go the other way, ask what income a price needs. =B1*B13 returns $30,000, the NOI the house would need to hit 6% at $500,000.

Test the assumptions, not just the formula

Change one input and watch B12 move. Raise taxes in B6 from $6,000 to $7,500 and NOI falls to $27,392, a 5.48% cap rate. Drop the price in B1 to $460,000 with the original expenses and the same income reads 6.28%.

That is what cell references buy you. Taxes, insurance and market rents all vary by city and by property, so the honest version of this sheet is one where you can swap in local figures and see the answer change.

Or skip the spreadsheet

The Cap Rate Calculator on the home page runs this exact layout, including vacancy and the expense percentage, without the setup. Use it to check a sheet you built, or to price a single property before you commit a column to it.