Tag Archives: #realestateappraisal

Add Histograms to Your Appraisal Tool Kit

It was my pleasure to speak at the Sacramento chapter of the Real Estate Appraisers Association last night at the Story of Value class with my good friend Ryan Lundquist. I discussed ways to explain markets in residential real estate appraisals and focused on using graphs and was surprised to see that maybe half of the crowd didn’t include graphs in their reports. This post is the first to offer advice and instructions on how to create meaningful graphs for residential appraisers.

Gross Living Area Histogram

Four appraisers out of 50 in the room reported using histograms. The histogram is a great tool for analyzing residential real estate markets that all appraisers should use.

What is a histogram?

For our needs, a histogram is a graph that shows the distribution of one continuous variable. The histogram splits the variable into equal-sized bins and counts the number of occurrences. It works well for important residential real estate variables like gross living area, lot size, age, and sale price.

Bin size is key to creating a useful histogram. Bins too wide loses meaning as your data is clumped together. Bins too narrow spreads the histogram out too much.

The graph above shows the sales of homes in a market area with homes of certain sizes. There is one sale less than 1000 sf and one more than 3500 sf. The most frequent size of home sold recently is around 2000 sf with the bulk of the homes in the 1400 sf to 2000 sf range.

Every report prepared for a lender asks the question “Is the subject conforming?” At a glance, any home sold in this neighborhood with between 1300 sf and 2600 sf is reasonably conforming in size. There are no sales in the 4000 sf to 5000 sf range so any homes in the neighborhood of that size are likely non-conforming. The two extreme sales, at 800 sf and 5000 sf, are unusual and likely non-conforming.

Sale Price Histogram

This next histogram examines the frequency of sale price in the market area. The most frequent sale price is in the $240,000 to $280,000 range with $360,000 to $400,000 the second most frequent range. A home in contract at $375,000 is fairly typical. A contract price of $700,000 is very unusual and is indicative of a non-conforming home.

The first two graphs were generated using ggplot2 in RStudio. Here’s an example from Excel 365 showing the year built for homes of sales in a market:

Year Built Histogram

Most homes sold in this market area (Placerville, CA small residential acreage) were built in 1970s and 1980s. A couple of homes were built in the 1800s and there are a couple of newly built homes.

The lender forms used by appraisers ask for similar information in a table format:

Table or Histogram, which is better?

Which describes the market better, the two histograms or the table?

Make A Histogram in Excel

Here’s how to use the latest version of Excel to make a histogram. This page has instructions for the latest version and older versions.

Start with your data in an Excel workbook with the top row field names and rows below sales data.

Starting with the field name, select the data to generate the histogram (ctl + shift + down arrow will select all consecutive data down)

Press F11 to insert a graph. Then chose Change Chart Type

Select Histogram then press Ok

You’ve made your first histogram!

First Histogram

However, it’s really ugly. Standard formatting for histograms is to have no space between the bins (columns). To fix that, double left click on one of the bins to activate the Data Series editor. Select the bars to active Series Option

Change the Gap Width to 0%. Notice how the columns come together. If you like having gaps between the columns, set the Gap Width to 6%.

First Not Ugly Histogram

To change the bin width, double left click on the x-axis labels (GLA in this case). Using the Format Axis Axis options, select the Bin Width control and type in what you want. Play with it until you’re happy with the shape of your histogram.

Default Bin Width

Excel defaulted to a bin width of 370 sf. Below is what the histogram looks like with bin width equal to 100 sf:

100 sf Bin Width

Here’s bin width equal to 500:

500 sf Bin Width

Here’s bin width equal to 200:

200 sf Bin Width

Which one appears most useful to you?


I use histograms to understand some aspect of a market. How big are the homes? When were the homes built? How big are the lots in the neighborhood? What do homes sell for in the market area?

Then consider where the subject fits in the market. Is it bigger than typical? If so, you have support for concluding market value is higher than typical. Is it smaller? Well, now you can show a reason why the price is lower.

The Subject’s Position in a Market Area

Let’s consider the histogram above. The subject is one of the larger homes in the neighborhood but still relatively common in size. I would expect, without knowing anything else, that the subject’s market value is on the higher side for the neighborhood but with a reasonable number of homes larger than the subject. Take a look at the graph below.

The Subject is one of the bigger homes….

What if the subject was one of the largest homes in the neighborhood? The subject’s market value is likely on the upper end of the neighborhood range. Also, there are fewer directly competitive sales, implying market value may be less reliable in this market area than for a smaller home. Now let’s look at an extreme case.

Charge big bucks if you get this subject

I pity the appraiser asked to appraise a 6500 sf house in this market. However, you do have sales either smaller or larger. Here’s the time to really open your eyes to what is a competitive sale. Throw this graph in your report and your client will immediately see your data difficulty.

After you arrive at market value and as part of your reconciliation, consider using a histogram to support your market value.

Support for your market value conclusion

“The subject is newer than typical, above average quality custom home on a larger than typical lot. As shown above, the subject’s market value is on the higher side for the greater market area, as expected based on its superior characteristics.”

I hope you agree that histograms can be a powerful tool for appraisers.

Ways to use histograms:

  • Exploratory analysis to understand characteristics of a market area
  • Assist in determining reasonable search criteria for sales comparison
  • Visual representation of the subject’s position in a market area
  • Support for market value conclusions

I learned about histograms from George Dell. Thanks George. Get smart by taking his Stats, Graphs, and Data Science classes or at the very least, sign up for his blog. More info on George’s website.

Postscript: I am working towards moving away from Excel to using R, the data analysis package. I’ll link to the R code used for the two graphs as a separate post/update soon.

The Role of the Appraisal in Residential Real Estate Lending

I had the opportunity to write an article for my local paper, the Woodland Daily Democrat. Here’s what I came up with:

The Role of the Appraisal in Residential Real Estate Lending

Most of the time when you buy a house or refinance your existing residential loan, the lender will require an appraisal of your house. What is an appraisal? An appraisal is an independent opinion of value about real estate. In this context, the appraisal is a report that describes the subject, the subject’s neighborhood, includes at least one of the three approaches to value used by us appraisers, and includes the market value of the subject home on a given date. The client for an appraisal, even if the borrower pays for the appraisal, is the lender. I write my residential appraisals for lenders, not buyers or borrowers.

Residential real estate lending appraisals use a standard definition of market value from FNMA (https://www.fanniemae.com/content/guide/selling/b4/1.1/01.html for reference).

Key points from the FNMA market value definition:

  • “Most probable price”-My values are not the highest value possible, not the contract price, not the amount you need to complete your refinance. It’s the value supported by evidence in the subject’s competitive market.
  • “Buyer and seller are typically motivated”-Market value assumes no unusual motivations like short sale, foreclosure sale, sale to a relative, etc.

When deciding whether to loan hundreds of thousands of dollars to someone, the lender will evaluate the borrower’s credit history, income, and expenses. My appraisal will be included as part of the lender’s risk assessment. If the lender were to take back the subject home today, how much would the subject be worth? Is the subject worth more than the loan? Are there any issues that would make it difficult to resell? Are there any obvious repairs that might reduce the value of the subject long term? Are there any obvious safety issues that might open the lender up to liability? My appraisal helps the lender with these questions.

Us appraisers serve as a check for over-exuberance in the residential real estate market. The real estate agents and loan officer get commissions only if the loan closes. The lender makes money only if it makes a loan. The seller gets paid only if the home sells. The buyer gets a house only if the loan closes. Since I get paid whether the loan funds or not, the underwriter and I are the only truly independent parties in the typical residential transaction. The lender relies on me to report any issues with the home and to honestly arrive at my opinion of value. If my appraised value is above the amount needed for the loan amount, and there are no other issues, the lender can move forward with the loan with confidence. In those cases where my opinion of market value is lower than needed to fund the loan, my report warns the lender that the loan may be risky.

The independence of the residential real estate appraiser is vital with the housing market crash of the 2000s fresh in mind. We don’t want to go through that again.

Anything to add? What did I miss?

(I’ll add a link to the article once it’s published)