Excel for Real Estate: Listings, Prices and Commission
By ExcelReady TeamUpdated 6 min read
The short answer
Real estate work in Excel centres on listings: price per square foot, average, lowest and highest prices with AVERAGE, MIN and MAX, averages by area with AVERAGEIFS, percent change between list and sale price, commission and split calculations, and monthly mortgage payments with PMT. A clean listings table makes all of these quick.
Key takeaways
- Price per square foot is price divided by square feet, and it makes homes of different sizes comparable.
- AVERAGEIFS gives the average price for one area, or one area and bedroom count, in one formula.
- Percent change between list and sale price is (sale - list) / list, formatted as a percentage.
- PMT returns a monthly mortgage payment from a rate, a term and a loan amount.
On this page+
Excel for real estate work starts with a listings table: price, area, size and bedrooms. From there, a few formulas answer most of the questions clients and managers ask: what's the price per square foot, what's typical in this area, how far did the sale price move from the list price, and what's the commission? This guide is for agents, assistants, property analysts and anyone applying to real estate roles.
What real estate teams use Excel for#
Listing services and property systems can usually export to a spreadsheet, and that's where comparisons happen. Common files include:
- Listings and comparables: price, area, square footage, bedrooms and days on market.
- Sales trackers: list price, sale price, closing date and commission.
- Rental and property management sheets: rent, expenses and occupancy.
- Marketing reports: enquiries and viewings by listing. Excel skills for marketing jobs covers campaign data in more depth.
The examples use these six listings in A1:E7:
| Listing | Area | Price | SqFt | Beds |
|---|---|---|---|---|
| L-01 | Riverside | 420,000 | 1,400 | 3 |
| L-02 | Hillcrest | 615,000 | 2,050 | 4 |
| L-03 | Riverside | 389,000 | 1,300 | 2 |
| L-04 | Downtown | 510,000 | 1,200 | 2 |
| L-05 | Hillcrest | 560,000 | 1,750 | 3 |
| L-06 | Downtown | 475,000 | 1,150 | 2 |
Price per square foot#
Price per square foot divides the price by the size, so you can compare homes of different sizes fairly. In F2:
=C2/D2returns 300 for L-01. Fill it down (Windows: Ctrl+D; Mac: ⌘D):
| Listing | Price per sq ft |
|---|---|
| L-01 | 300.00 |
| L-02 | 300.00 |
| L-03 | 299.23 |
| L-04 | 425.00 |
| L-05 | 320.00 |
| L-06 | 413.04 |
L-02 costs far more than L-01 in total, but they're priced the same per square foot. The two Downtown homes are the most expensive by size. Format the column with two decimals or round it with =ROUND(C2/D2,2) if the exact cents matter.
Average, lowest and highest prices#
AVERAGE, MIN and MAX summarise a column in one formula each:
=AVERAGE(C2:C7)returns about 494,833.=MIN(C2:C7)returns 389,000 (L-03).=MAX(C2:C7)returns 615,000 (L-02).
AVERAGE ignores empty cells but counts zeros. If unsold listings have a 0 in the Sale Price column, they drag the average down. The guide to averaging without zeros shows how to leave them out with AVERAGEIF. See the AVERAGE function guide for more.
Average prices by area with AVERAGEIFS#
AVERAGEIFS averages only the rows that meet your conditions. The range to average comes first:
=AVERAGEIFS(C2:C7,B2:B7,"Hillcrest")returns 587,500 ((615,000 + 560,000) ÷ 2). Riverside gives 404,500 and Downtown 492,500. Add more pairs to narrow it: =AVERAGEIFS(C2:C7,B2:B7,"Riverside",E2:E7,">=3") returns 420,000, since only L-01 is a Riverside home with three or more bedrooms. See the AVERAGEIFS function guide.
To make a small area summary, list the areas in H2:H4 and use =AVERAGEIFS($C$2:$C$7,$B$2:$B$7,H2), filled down. The $ signs keep the data ranges fixed.
Percent change: list price to sale price#
Percent change shows how far a price moved. The formula is (new − old) ÷ old:
=(sale-list)/listIf L-04 was listed at 510,000 and sold for 495,000, =(495000-510000)/510000 returns about -2.9%: it sold just under 3% below the list price. The same formula shows price growth over time. A home bought for 450,000 and now valued at 475,000 has risen by =(475000-450000)/450000, about 5.6%. Format results with Ctrl+Shift+% (Mac: Control+Shift+%).
For rental property, gross rental yield is a similar ratio: yearly rent divided by the price. A home bought for 389,000 that rents for 1,950 a month earns 23,400 a year, so =1950*12/389000 returns about 6.0%. Net yield subtracts yearly costs such as maintenance and insurance from the rent before dividing. Keep rent, costs and price in separate cells so you can test different figures.
Commission and splits#
Commission is the sale price times the rate. Keep the rate in its own cell so you can change it once. With the sale price in B2 and a 3% rate in H1:
=B2*$H$1On a 510,000 sale, that's 15,300. If the commission is split 50/50 between the buyer's and seller's agents, each side gets =B2*$H$1*50%, or 7,650. A further split between agent and brokerage works the same way, one multiplication at a time. Commission rates and splits vary by market and agreement, so always use the figures in the actual contract.
Mortgage payments with PMT#
PMT returns the regular payment on a loan with a fixed rate. It takes the rate per period, the number of periods and the loan amount:
=PMT(6%/12,360,-400000)returns about 2,398.20 a month for a 400,000 loan at 6% a year over 30 years. Divide the yearly rate by 12 and multiply the years by 12 so both are monthly. The minus sign on the loan makes the payment show as a positive number. PMT covers principal and interest only, not taxes or insurance.
To compare options, put the rate, the term in years and the loan amount in their own cells, say H2, H3 and H4, and use =PMT(H2/12,H3*12,-H4). Now a client can see the payment for a 25-year term or a lower rate by changing one cell, with no need to edit the formula.
How these skills show up in interviews#
Real estate analyst and assistant interviews may include a listings task: price per square foot, an average by area and a percent change. The same building blocks appear in Excel skills for sales operations, and property managers will find rota and maintenance tracking in Excel for operations managers. Teachers use the same AVERAGE, MIN and MAX on grades, as Excel for teachers shows.
Practise it#
Work the full scenario in the real estate listings case study: price per square foot, averages and counts by area, a filtered list and the priciest home, checked as you type. To strengthen the basics, practise AVERAGE, MIN and MAX and percentages and % change in a live spreadsheet.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the average listing price in Hillcrest? Click G2 and use AVERAGEIFS on the Price column (C2:C7) where the Area (B2:B7) is Hillcrest.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Beds | |||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 |
Tip: AVERAGEIFS puts the range to average first, then the Area range and the area name in quotes.
Practise this in a lesson
Frequently asked questions
How is Excel used in real estate?
Agents, analysts and property managers use Excel to compare listings, calculate price per square foot, average prices by area, track list-to-sale price changes, work out commissions, estimate mortgage payments and track rent and expenses.
How do I calculate price per square foot in Excel?
Divide the price by the square footage: =C2/D2. A home listed at 420000 with 1400 square feet comes to 300 per square foot. Fill the formula down to compare every listing.
How do I calculate a mortgage payment in Excel?
Use PMT with a monthly rate, the number of monthly payments and the loan amount: =PMT(6%/12,360,-400000) returns about 2398.20 for a 400000 loan at 6% over 30 years. The minus sign makes the payment show as a positive number.
How do I average prices for one area in Excel?
Use AVERAGEIFS with the price range first, then the area range and the area name: =AVERAGEIFS(C2:C7,B2:B7,"Hillcrest"). Add more range and condition pairs for bedrooms or property type.