Build a Self-Updating Comp Sheet and Cap Rate Calculator with Copilot in Excel

What This Does

Comp data pulled from a listing site lands in a spreadsheet as a scratch list of addresses, prices, and square footage, with no structure and no math. Copilot in Excel turns that list into a working comp sheet, writing the price-per-square-foot and estimated cap rate formulas for you, so adding a new comp next week just means dropping in a row instead of rebuilding the calculation from scratch.

Before You Start

• You have Excel open (desktop, web, or mobile) with your raw comp list pasted in, one row per property
• Copilot appears in your version of Excel. Whether it's included depends on your specific Microsoft 365 subscription and your organization's settings, so if you don't see it, check with whoever manages your Microsoft 365 account, or look for a trial you can start to test it before committing
• Your comp list has at least address, list or sale price, and square footage in separate columns

Steps

1. Find the AI feature

Select the Copilot icon in the lower-right corner of the Excel window. A chat panel opens on the right. Copilot opens in edit mode by default, meaning it can make changes directly in your workbook; a mode switch in the panel lets you swap to plan mode, which proposes a plan first so you can confirm before it touches anything, or chat mode, which answers questions without changing the sheet.

2. Tell it what you need

Describe the columns you want added. For example: "Add a column that calculates price per square foot using the Price and Square Feet columns, and add a column that estimates cap rate using an assumed annual rent I'll fill in, formatted as a percentage." Copilot will ask or assume where rent isn't provided yet; go back and fill in your own rent estimate per property once the formula is in place.

3. Review and use the result

In edit mode, Copilot builds the formulas directly into new columns and shows its reasoning in the chat pane as it works. Check a couple of rows by hand, price divided by square feet, and net operating income divided by price for cap rate, to confirm the formulas match what you'd calculate yourself. Because the columns are live formulas, not pasted values, adding a new comp row next week recalculates everything automatically.

Real Example

Scenario: An investor pulls eight comparable duplex listings into Excel: address, list price, square footage, and estimated annual rent for each.

What you type: "Add a Price per Sq Ft column and a Cap Rate column. Assume operating expenses run about 40 percent of gross rent for cap rate, and show cap rate as a percentage."

What you get: Two new formula columns filled in for all eight rows, letting you sort the sheet by cap rate to see which duplex is the strongest deal by that measure, and any new comp added below the last row picks up the same formulas without you touching anything.

Tips

• Ask Copilot to explain any formula it wrote: "Walk me through how you calculated the cap rate in this column." Understanding the formula matters more than trusting the number.
• If a formula assumption doesn't match how you actually underwrite (a different expense ratio, a different rent figure), tell Copilot the correction directly rather than editing the formula by hand. It's faster and it keeps the logic consistent across every row.
• Keep this as a running comp sheet per market instead of a one-off file. The formulas and formatting carry over, so the next deal in the same neighborhood starts from a sheet that already works.

Tool interfaces change. If a button has moved, look for a Copilot icon in the lower-right corner of the Excel window or a similar AI icon nearby.