How to Calculate Cap Rate in Excel

Most newcomers believe a cap rate is the total return on a property. It is not. The cap rate is a snapshot of current yield only, net operating income divided by price, and it ignores financing, taxes, appreciation, and capital expenditures. A 6% cap rate can produce a 0% or 12% total return depending on rent growth and the exit cap rate. The formula itself is a mathematical identity that will not change. To set up a reliable cap rate excel model, you need the right sheet layout, the correct NOI calculation, a way to work backward from a target cap rate, and a sensitivity table that shows how value moves when assumptions shift. Each piece is walked through cell by cell.

Four Blocks for a Clean Model

A clean cap rate spreadsheet starts with four blocks: income inputs, expense inputs, NOI summary, and cap rate calculation. Label row 1 as a header row. Use column A for labels, column B for values. Reserve column C for notes or source references.

Rows 1 through 10 hold the property identifier and assumptions. Row 1: Property Name. Row 2: Date of Analysis. Row 3: Purchase Price or Current Market Value. Row 4: Pro-Forma vs. Trailing indicator. This layout keeps the google sheets model identical; the formulas transfer with no changes.

The income block starts at row 6: Potential Gross Income (PGI), row 7: Vacancy and Collection Loss (as a percentage), row 8: Other Income (parking, laundry), row 9: Effective Gross Income (EGI) calculated as PGI minus vacancy loss plus other income. The expense block begins at row 11: Property Taxes, row 12: Insurance, row 13: Utilities, row 14: Repairs and Maintenance, row 15: Management Fees, row 16: Replacement Reserves, row 17: Total Operating Expenses. Row 19 calculates Net Operating Income (NOI) as EGI minus total expenses. Row 21 shows the cap rate: NOI divided by price.

The formulas are the same in both applications. In the layout above, cell B9 contains the EGI formula: =B6-(B6*B7)+B8. Cell B17 sums expenses: =SUM(B11:B16). Cell B19 is the NOI: =B9-B17. Cell B21 is the cap rate: =B19/B3. Format B21 as a percentage with two decimal places.

A worked example from the fact sheet confirms the math. If NOI is $74,000 and the purchase price is $1,000,000, the cap rate is 7.4%. If NOI drops to $71,500, the cap rate falls to 7.15%. The cap rate is the inverse of a price-to-income multiple: a price/NOI multiple of 10 implies a 10% cap rate. Check your spreadsheet against these figures before using it on a real property.

The failure case: forgetting to include a vacancy allowance. A 0% vacancy assumption inflates NOI and produces a misleadingly low cap rate. The Appraisal Institute's The Appraisal of Real Estate (15th ed., 2020) defines NOI as gross potential income minus vacancy and collection loss minus operating expenses, excluding debt service and capital expenditures. Replacement reserves for capital items like roof and HVAC are deducted as an operating expense line; actual capital expenditures are not.

Work Backward From Your Target

When you know the NOI and you want to find the price that achieves a target cap rate, the formula is backwards: Price = NOI / Target Cap Rate. In the spreadsheet, enter the target cap rate in cell B23. In cell B24, enter =B19/B23. The result is the property value at that target rate.

Suppose you have a stabilized NOI of $74,000 and you want a 7.0% cap rate. The target value is $74,000 / 0.07 = $1,057,143. If the seller is asking $1,100,000, the property yields a 6.73% cap rate, below your target. Negotiate or walk.

This calculation is the core of direct capitalization, the standard valuation method from the Appraisal Institute. But it only works if the NOI is stabilized. Using trailing NOI, the actual income from the last 12 months, without adjusting for a one-time repair or a non-market rent will overstate or understate the true value. Sellers and brokers often quote a pro-forma stabilized NOI that assumes full occupancy and market rents, which can be 10-20% higher than trailing NOI, inflating the apparent cap rate by 50 to 150 basis points. Source: comparison of broker offering memoranda to audited operating statements.

Build a Two-Variable Data Table

A sensitivity table shows how the property value changes when the cap rate and NOI vary. The tool for this is Excel's Data Table (What-If Analysis). In Google Sheets, the equivalent is Data > Data cleanup > Data table, rolled out to all accounts by March 2022. The logic is identical, but the menu path differs.

Set up a two-variable data table. In cell E2, link to the value formula: =B24. In column E, starting at E3, list cap rates from 5.0% to 9.0% in 0.5% increments. In row 2, starting at F2, list NOI values from $60,000 to $90,000 in $5,000 increments. Select the range E2:K10. In Excel on Windows, press Alt, A, W, T (Data tab > What-If Analysis > Data Table). In the dialog, set the Row input cell to B19 (the NOI cell) and the Column input cell to B23 (the target cap rate cell). Excel fills the matrix with values. In Google Sheets, use Data > Data cleanup > Data table and enter the same input cells.

The result is a grid: at a 6.0% cap rate and $80,000 NOI, the value is $1,333,333. At a 7.0% cap rate and $70,000 NOI, the value is $1,000,000. The table reveals how a 100-basis-point change in cap rate or a $10,000 change in NOI swings the value by $100,000 to $200,000. This is the number that matters for a negotiation.

Limitations: The data table cannot be edited cell by cell; you must delete and recreate it to change the layout. It recalculates on workbook recalculation (F9) unless you set it to Manual. In Google Sheets, it recalculates automatically on change.

Build or Verify Your Template

Build the template yourself using the cell-by-cell instructions above, or download a pre-built version from a trusted source. The template should include the four layout blocks, the NOI formula, the cap rate formula, the target value calculator, and the two-variable sensitivity table. It must include a vacancy allowance line set to at least 5% and a replacement reserves line set to $0.10 to $0.20 per square foot depending on property age.

Before you use any template, verify it against the worked examples. Enter $74,000 NOI and $1,000,000 price. The cap rate must show 7.40%. Change NOI to $71,500. The cap rate must show 7.15%. If the template's results differ by more than 0.01%, the formulas are wrong. Delete the file and rebuild from scratch.

The honest caveat: a spreadsheet is only as good as the assumptions you type into it. The cap rate formula is mechanical. The hard part is the NOI, getting the vacancy rate right, the operating expense ratio right, and the reserves right for that specific property in that specific market. Published survey cap rates from CBRE or RCA/MSCI are starting points, not targets. The 2024 CBRE survey showed multifamily cap rates between 4.5% and 6.0%, but a class C building in a secondary market with deferred maintenance will trade far outside that band. Your spreadsheet does not know that. You do.