Facebook
Twitter
Pinterest
Copy Link
Sometimes I have a great idea for a new statistical chart after a conversation with a colleague. After a conversation with John Hampshire at my office, I decided to see if I could chart the Sales Price to List Price ratio for real estate sales in Boulder County over the last 6 years. The expectation is that during the spring when the market is most frenzied, that this ratio would show a much higher percentage that would gradually decrease throughout the year to the slowest time in the late Fall. This chart could then be used to show sellers the best time to get the highest price and buyers the best time to get the best deal.
Like many great ideas, this idea didn’t survive first contact with reality. I was able to pull the data without too much effort but that was the last easy step. A quick look showed SP/LP ratios that ranged from 19.79% to 559%, not what I expected. A quick look at some of the outliers to figure out my next action. The 19.79% sale was for a single family home that went under contract pre 2013 flood but closed as a piece of vacant land post 2013 flood. Many of the other very low ratio sales were for small mountain acreages that took multiple years to sell. First massage of the data, take out all of the Vacant Land sales.
Next, moving to the opposite end of the spectrum, how did something sell for five and a half times the asking price? Some more research determined this was a vacant land parcel that was sold in conjunction with the $2M+ dollar home next door and the combined price was reported for both the home and the vacant land. Where do you stop though? I can’t comb through 27,949 sales in my dataset and make corrections to each one. Also, some New Construction Homes really do sell for almost 200% over asking once you add in options, lot premiums, appliance packages, etc. A couple more massages to the data to correct the most egregious outliers but most get left in.
Next dilemma, what timescale do I use? Charting the sales by actual date seems like it would give too much movement. Charting by month seems like too little movement. I finally ended on Week #’s of the year, weeks 1 through 52. Still some issues with this approach too. Week 1 of 2016 didn’t contain a single business day, so no sales possible that week but still seemed to be the best approach.
Next dilemma, do I use the Sold date or the date the property went Under Contract. Contract date would more accurately reflect the market strength or weakness at the time the contract was negotiated, let’s go with that. “Shoot” (or something unprintable) Contract date isn’t a data field I can download out of IRES, Sold date it is. Sold date however is probably offset 30-60 days from contract date and the market conditions that existed then. I decided not to try and chase down that source of noise in the data.
So, after all of that, below is what my efforts came to, a metric that doesn’t really tell you anything. There isn’t a clear pattern to the SP/LP ratio throughout a year. Too many spikes and drops from outlier data. No clear trends in the shapes of each year. Only clear trend I can see is that the most recent years have had a higher SP/LP ratio than the years closer to the Great Recession. A shocking new insight to everyone I’m sure. I guess the lack of a clear pattern is an insight in some ways. There isn’t a consistent pattern to our market where a certain time will always produce a better or worse SP/LP ratio than another time. There will always be some sellers and some buyers more flexible than others.
Luckily not all was lost. For those of you who have ever downloaded large numbers of sales out of IRES, you know that there is a maximum cap of 2,000 records that can be exported at any one time. Since every year 2011-2016 has had more sales than this limit, I had to break my exports out into less than 2,000 sale chunks. The easiest way I have found to do this is by sales price. First chunk 0-250K sales, second chunk 250K-550K sales, etc. What became apparent as I made these downloads was that these chunks were changing dramatically over the years. This isn’t a dramatic insight, we all know our prices have been appreciating, but I was really struck by how we have lost the entry level of our market.
This insight was much easier to translate into a chart (next page). Each year on a 3D graph showing the sub-400K market in Boulder County for both attached and detached sales. You can clearly see the market shifting to the right towards higher sales prices. 2011 at the back of the chart to 2016 at the front of the chart. A stark visual reminder of how our market is evaporating for entry level buyers.
Hope everyone has a great August!
