Author Archives: josephlynchadmin

Composition Effects for Appraisers

Economist Timothy Taylor posted a discussion about hourly wages recently that had a section that sounded very familiar. Here’s the quote that caught my eye:

“You will sometimes hear statistics people talk about a ‘composition effect,’ which just means that if you are comparing a group over time, you need to beware of the possibility that the composition of the group is changing.” Why would a residential real estate appraiser care about composition effects?

Much of my time as a residential appraiser is spent determining trends in real estate markets. Every day I create charts like the one below to describe the markets in my reports.

Simple linear regression trendline showing an increasing submarket

These trendlines assume that the group of homes sold do not differ significantly over time. In most cases, this assumption is reasonable. Sometimes this assumption is false.

For example, the global pandemic has affected buyer tastes. My friend Ryan Lundquist wrote about this recently. After six months of being cooped up from the Covid-19 pandemic, buyers want a larger home. Here’s his chart showing the trends in the Sacramento region:

Change in Home Size during the Covid-19 Pandemic in the Sacramento Region from Ryan Lundquist

The average size of a home sold in the Sacramento region has increased 100 sf over the past six months. House size is the primary driver for value, so if all other factors are the same, the average price for that market will increase.

BUT IT’S AN ARTIFICIAL GAIN BECAUSE ANY GIVEN HOME OF THE SAME SIZE WOULD SELL FOR THE SAME PRICE!

Similarly, if homes decrease in size over time with no other changes, that would cause the average price to decline with no impact on individual house prices.

Here’s what I do to have a better understanding of market trends:

I trend sale price of homes sold over time,
I trend price per square foot of homes sold over time, and
I trend home gross living area over time.

Why trend price per square foot? This trick takes into account some variation in size and in conforming areas, can increase the reliability of the trend analysis. However, significant changes in size will influence the PSF trendline.

I see this frequently in Winters, California. Winters is a relatively small city of about 10,000 people located on the western edge of the Sacramento Valley not far from Davis. Below are the three graphs for Winters sales from 1/1/18 to 10/1/19 (all data from Metrolist MLS).

Sale Price trendline showing an increasing market

Prices are clearly increasing in the top graph.

Prices are essentially stable when trending price per square foot for the same sales. Why?

Here’s the third chart showing size of homes over time:

The size of a home sold in Winters during this time period increased almost 1 sf per day.

Because of the math,

an increase in the average size of homes sold will push down the price per square foot trendline but will push up the sale price trendline, and

a decrease in the average size of homes sold will push up the price per square foot trendline but will push down the sale price trendline.

Here are some other examples that may cause a market to appear to change over time without a real change in market conditions:

Average lot size changes, especially for small acreage residential properties
Average age of homes changes, especially when new construction ramps up or ramps down
Better quality homes come to market
An outlier, such as a tear down or the biggest home in the county, pulls the trendline out of true
A small, heterogeneous market susceptible to change from the latest sale

A good habit is to take a look at your data. Do you see changes in your data or is it relatively similar over time?

When I do run into composition effects, such as sale price going up and price per square foot going down, I graph both together on the same graph and explain why there is a difference. I then reconcile.

“The sale price trendline is increasing while the sale price per square foot trendline is decreasing. The average size of homes have increased during this time period, skewing the sale price trendline up and the price per square foot trendline down. I conclude that this market has been relatively stable during this time.”

Hope this adds to your understanding of residential real estate markets.

p.s. This website has instructions on how to show two time series on the same graph like the example above.

Davis, Woodland, and Winters Market Update September 2020

4 Replies

Six months after the Covid-19 stay-at-home order hit Yolo County, what’s happened to residential real estate in local markets?

Sales activity in Davis was low at the start of 2020 before the pandemic hit and continued into the spring with a massive drop in May. As shown below, Davis is way behind in sales compared to last year but we may make up ground in the fall.

2020 vs. 2019 single family residential sales in Davis per Metrolist

12 Month change in Davis single family residential sales per Metrolist

In contrast, Woodland started 2020 with strong year-over-year sales activity, putting on the brakes in April and May. Some of the missing activity shifted into the summer but Woodland is still behind last year’s numbers.

Woodland closed sales 2020 vs. 2019 per Metrolist

*12 Month change in Woodland single family residential sales per Metrolist*

While demand (sales) fell over the past six months, supply fell even further in both Davis and Woodland. We have seen an increase this summer in homes listed in Davis, hopefully a sign of the traditional summer market spilling into the fall.

Davis single family residential new listings per Metrolist.

Woodland saw a sharp drop in new listings in April and May and is continuing to track lower.

Woodland new single family residential listings per Metrolist

Net effect on both Davis and Woodland is a supply imbalance leading to rising prices. Davis is showing year-over-year increases in five of the past six months.

Prices have increased on a year-over-year basis in five of the past six months in Davis per Metrolist

Woodland prices are rising too as shown on the scatter graph of all sales below.

The Woodland SFR market has increased over the past 12 months per Metrolist

Below is a quick summary of both markets:

The standout statistic above is the incredibly low inventory in Woodland.

Winters is a much smaller market than Davis or Woodland. As the graph below shows, sales are increasing at present. Also note the lack of sales in April and May in Winters, similar to other Yolo County markets.

Prices have increased in Winters over the past 12 months too

Takeaways for Davis and Woodland

Sales volume is down
Inventory has declined more leading to a supply imbalance
Prices are increasing

Pay Attention To

Interest rates. The historically low rates are jet fuel for the residential market. When rates go up, pay attention
The local economy. We’re still in a recession with massive job losses and a large percentage of mortgage forbearances. So far, impacts to local housing have been minimal but that may change in a hurry

Are you seeing the same things in your markets?

Easy Fix for Rappatoni Data Export Issue

4 Replies

Update: Well, this helps but doesn’t completely solve the problem. Tab characters still stay in the fields not converted to numbers or dates. If this doesn’t work, I’ve added a couple of steps that will work involving using a robust text editor, Notepad++.

Oh no! What broken my 1004MC calculator?

A recent Rappatoni MLS update broke data export. It appears that a hidden tab character is placed between fields as a delimiter, changing numeric fields into character fields. This causes issues when you try to use a workbook like Don Machholz’s 1004MC because the 1004MC workbook relies on the data types to be correct. The calculator chokes whenever it finds text in a numeric field like list price (or sale price, or DOM, etc.). This only affects Rappatoni system MLSes. Metrolist was not affected (yet) because they run their own code and do not appear to have run the update that broke stuff.

Here’s a quick way to know if your MLS has been affected. Take a look at the Selling Price column. If it lines up on the left, your MLS has the issue. If it lines up on the right, you’re good to go.

Here’s an example with the problem:

Here’s how it should look:

Here’s an easy workaround for the latest version of Excel.

Download your data out of MLS and save it to a csv file.
Open up an Excel workbook.
Click on the cell you want the downloaded data to appear in.
Click on the Data Tab and select From Text/CSV

Select your csv file containing the data you need and hit the Import button

Excel will open a data import wizard. This should show your data with the correct data formats and aligned correctly (numbers aligned to the right, text to the left, dates looking like dates). Click the Transform Data button and you should be good to go.

Older versions may require more work to find the data import wizard. This link shows the process using a slightly older version of Excel. Brings me back to my days as an EDI analyst….

I’m learning from others that this fix doesn’t work universally. If you see this in your Excel file, you still have problems.

Here are additional steps that you can do to fix it. The simple way is to place your cursor in the edit bar to the immediate left of the data you want to keep, hit <Backspace>, then enter. This deletes the hidden Tab character but needs to be repeated throughout the file. Another option is to simply not copy text fields into the 1004MC since they are not required to run the calculator.

If you’re interested in a better solution, download a robust text editor like Notepad++ (link). Use Notepad++ to open the csv file.

Go to View, Show Symbol, Show All Characters

Notepad++ will then show the Tab characters

Select one of the now visible tab characters and type ctl + c to copy the Tab character. In Notepad++, select Search then Replace

Click on the Find what: box and paste the Tab you selected previously. Leave the Replace with: box blank. Click on the Replace All button and then save changes.

Now you should be good to go.

(There are many ways to skin a cat. Another way would be to import your files into an Access database, delete the tabs, then output as an Excel file. If you know how to do this, you don’t need my instructions)

Good luck and contact me directly at joemlynch2112@gmail.com with questions. Hopefully Rappatoni fixes this soon.

Early Pandemic Effects on Davis and Woodland, California Residential Real Estate

7 Replies

Monthly year-over-year change in homes sold, mean sale price, and mean psf for homes sold in Davis, CA

As I type this, the 100,000th person in the US has died from the novel coronavirus 2019. The country has shut down to bend the curve. Shelter in place started in Yolo County on March 19, forcing most people to stay home. Unemployment exploded nationwide, going from 3% to nearly 20% in a month while mortgage forbearance levels jumped to 2008 levels. How has the economic crash affected residential real estate in Davis and Woodland?

Shelter in place stopped in-person interior inspections for buyers and real estate agents while the stock market crash and jump in unemployment shook consumer confidence. However, interest rates dropped into the low 3s, increasing affordability.

The graph above shows all single family residences sold in Davis by month for 2019 and 2020. Year-over-year change in January, February, and March was somewhat negative, indicative of a slowing market. April 2020 was lower still and probably represents sales that went into contract before or at the beginning of the shut down. May sales are probably the first period to reflect the post-shut down period. I was so surprised at how few sales in May to date that I ran the search multiple times to make sure I wasn’t making a mistake….

The Davis residential market ground to a halt. Current listing volume is still low but homes in contract is starting to recover (35 in contract in May 2020 to date compared to 41 in 2019). Prices have held up surprisingly well on a year-over-year, price per square foot basis as shown below.

Davis prices have held up surprisingly well

After a period of decline in the fall, prices shifted to stable to slightly increasing. Early days and less reliable than typical because of the sales volume decline.

Woodland, in contrast, was poised for a strong 2020 before the pandemic. Sales volume was up 38% in January and 25% in February from 2019. March sales this year slowed to the 2019 rate and declined steeply in April and May to date.

Woodland sales stated 2020 strong but declined as the pandemic hit

Sales volume declined sooner in Woodland but not as steeply as Davis. Once again, prices in Woodland have been relatively stable overall. Homes currently in contract are low and point to continued sales volume decline in at least the short run.

Woodland prices have been relatively stable in 2020

We’ve seen a significant slowdown in activity that has yet to affect prices significantly. Inventory is slightly higher but not yet affecting prices. Historically low interest rates have certainly helped prop up the market. Buyers and agents have adjusted their protocols to stay healthy while shopping for homes.

I’m concerned at the trickle of sales in Davis. Davis is a really hard place to value properties because of differences in location and the high degree of seasonality from the university. Reduce sales volume significantly and sales comparison is going to be difficult. Here’s hoping my Davis Realtor friends have a busy quarter…..

Multiple Windows in Excel

5 Replies

I might be late to the show but the latest version of Excel offers multiple windows open in the same workbook. For example, say you want to enter results from one worksheet into another. Or you’re looking at a large worksheet and want to see results in the lower corner and upper corner at the same time.

Here’s how:

Select View, New Window. You can repeat as needed from the original window.

Here’s a screenshot:

Here’s the result spread over two monitors:

And why this is so useful:

Wish I’d known this 10 years ago. Maybe I will buy that 4th monitor after all.

Add Histograms to Your Appraisal Tool Kit

14 Replies

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.

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.

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:

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:

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!

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%.

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.

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

Here’s bin width equal to 500:

Here’s bin width equal to 200:

Which one appears most useful to you?

Context

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.

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.

Who needs an appraisal more than someone selling to an iBuyer?

4 Replies

Opendoor wants to buy my house!

Have you heard about iBuyers? This is a relatively new business model in residential real estate where companies offer to buy your home for cash with a very short turnaround. These companies, such as Opendoor and Offerpad, make a preliminary offer, do a property inspection to determine needed repairs, and quickly offer a price for the home. The iBuyer then prepares the home for market, cleaning and making any necessary repairs, and lists the home for sale. Zillow, Redfin, and national real estate brokerages are starting to offer this model, too. Here’s a quick primer from Housing Wire that explores variations on this basic model.

Some clear advantages to the seller include fast turnaround and simplicity. Accept an offer, receive your cash, bid on the house of your dreams. This is a compelling story in our short attention span society. But what is the cost?

This story discusses the only study to date showing that sellers receive, on average, 11% less than on the open market when all costs are included.

That’s $45,000 in my neighborhood.

The iBuyer model works only if there is sufficient profit between buying the home and selling it. This creates an obvious incentive for the iBuyer-make the lowest offer to buy and sell the home at the highest price possible. What supposedly separates the iBuyer from the traditional flipper is advanced analytics to determine the market value of a home. The iBuyer model relies on a seller not knowing the market value of their home and/or a seller willing to accept a below market price. Sellers are trading money for speed and convenience.

There’s variation in the data. Some transactions were closer to market value, some were further. The key to making an informed decision is to understand what current market value is for your home before you accept an offer.

Before entertaining an offer from an iBuyer, learn the market value of your home from a local, independent appraiser.

Why does North Dakota want to waive appraisals for 5 years?

2 Replies

Once again, the fine state of North Dakota has requested regulatory relief from mandatory federal appraisal requirements. Current law requires lenders to obtain independent appraisals when loan limits are above certain levels for federally related transactions. This law, passed in the aftermath of the savings and loan crisis in the 1980s, supports prudent risk management for a lending industry that has shown time and again an inability to manage itself.

This time, Governor Doug Burgum has requested a five year exemption on the argument that appraisers are hard to find in North Dakota. His argument for the waiver is that there is a shortage of appraisers in North Dakota. But is that really true?

I decided to test this. I downloaded a list of all active appraisers in the US from the Appraisal Subcommittee website and compared the number of active appraisers in each state to that state’s population. If North Dakota has a shortage of active appraisers, the population to appraiser ratio would be higher than in California, my state, where there is an oversupply in Southern California, right? I prepared the two graphs below to answer this for 1) residential clients (residential appraisals can be completed by any licensed appraiser); and 2) commercial lending clients (certified general appraisers only). So where does North Dakota fit in?

As of yesterday, North Dakota had 2545 people per appraiser. California, in contrast, has 4,194 people per appraiser. The US overall has 3,490 people per appraiser. North Dakota is in the top 15 for coverage for all appraisers.

North Dakota has even better coverage for commercial with 4,069 people per active certified general appraiser (US Coverage: 8,371 people per appraiser). It is top 5 for coverage in the US.

Do 35 states have a shortage of residential appraisers? Not that I’ve heard. Do 45 states have a shortage of commercial appraisers? No other state is asking for relief.

So why does North Dakota want undermine prudent financial safeguards?

I hope everyone who reads this will comment on the Federal Registry. Use this link. Comments close on 7/1/19.

Find My Appraiser

4 Replies

I’m excited to join the nationwide network of appraisers Find My Appraiser.

The residential lending industry is moving away from appraisals after seven years of rapid appreciation when many markets in Northern California are showing signs of slowdown and stability. I joined FindMyAppraiser.com because of their strong advocacy for appraisers and consumer protection.

From the FindMyAppraiser.com website:

“FINDMYAPPRAISER.COM IS A NATIONAL REAL ESTATE APPRAISER DIRECTORY AND JOINT MARKETING CAMPAIGN

FindMyAppraiser.com serves as the link between local property appraisers and the public that needs these services.

Let the buyer beware! Now more than ever American consumers must protect themselves when purchasing a home, buying rental property or investing in a business. These decisions are “life changing” and can effect consumers for many years to come. Buying a home is the biggest financial investment one will make and getting an accurate property value from a qualified local appraiser is best way to make sure you are making a wise decision.

Many banks don’t order appraisals! That’s right. Many home buyers believe banks will order an appraisal when they apply for a mortgage but more and more banks are using AVMs (Automated Valuation Modules) or out-of-the-area “valuers” in the mortgage process. These valuations are not performed for your benefit, they are only used by the bank. You don’t own them and you should not rely on them to make your purchase decision. You need a properly trained market expert. You need an Appraiser.

FindMyAppraiser.com is dedicated to supporting professional appraisers and promoting consumer protection.”

Thanks to Phil Crawford and Lori Noble for putting this together.

Portland, OR this week

2 Replies

I’m in Portland this Thursday to give a two hour talk on tips for appraising complex properties at the ASA-IFA Portland/Rose City Chapter Monthly Luncheon.

https://www.prccnaifa.org/

Hope to see you there.

Yolo Solano Appraisal Blog

Appraisal Blog by Joseph Lynch

Author Archives: josephlynchadmin

Composition Effects for Appraisers

Davis, Woodland, and Winters Market Update September 2020

Easy Fix for Rappatoni Data Export Issue

Early Pandemic Effects on Davis and Woodland, California Residential Real Estate

Multiple Windows in Excel