Graphing Using Spreadsheet – Including Pendings
Pendings can reveal market trends, turns and current activity…
Appraisers,
How many of you use visual graphs in your reports to demonstrate sales (and Pending) trend data?
This can be a very important tool to convince your clients that you really have a good understanding of current valuation issues.
I am indebted to David Braun, Patrick Egger, Steve Smith, George Dell, Anthony Young and Joe Lynch (among others) for their presentations about this key feature we can utilize. Frankly, using spreadsheets and their built-in graphing functions is something every appraiser should learn.
I bring this up because an appraiser peer was having a struggle with a property purchase appraisal, where the sale price was bid up excessively ($45,000) with an escalation clause in the contract, and contacted me for my opinion. We both know how to use graphs from spreadsheet data. The concern was “is the contract sale price supportable?” when the list price was about $740,000 and the contract price was about $790,000. In this case, NO!
The key element of this assignment was the current Pendings were indicating a downturn in the market, even though prior sales had been chugging along at about a 1% increase per month for the prior 4 years… (determined using a separate chart and graph of sales).
Note that I said ‘Pendings.’ I’ve seen many instances where appraisers have downplayed the importance of analyzing or even using Pendings in reports. Whenever they get a client request to include Pendings in a report, they roll their eyes, and do it reluctantly. What these appraisers are forgetting or dismiss, is the Pendings represent CURRENT market activity, while the comps typically are RETROSPECTIVE. Without including a time adjustment (in increasing or decreasing markets) to the comps, those are ‘old data.’
Circling back to my intro about using graphs, here’s the one I developed for the peer’s report situation. I have more info below this:
If you are not familiar with graphing, items you need to know are these:
The data points shown on the graph bracket the GLA SIZE of the subject by 20% lower and higher, and AGE 10 years lower/higher, with resulting sales indicated. All properties are in an urban subdivision of similar homes. These were NOT chosen based on PRICE.
- The sales are the blue diamonds – over prior 2 years
- The brown diamonds are current Listings (plotted based on list date)
- The green circles are the current Pendings (plotted based on list date)
- The green line is the linear regression based on ‘least squared’ which is a built-in function of the spreadsheet software. It basically represents the ‘average’ of the sales prices along the two year time period. I don’t claim to be an expert on the math in Excel!!!
- There is a formula below the green line (y = 314.24x ). This formula (auto generated by the software) represents the DAILY dollars increase in sale prices, and can be used to develop a time adjustment to the Sales. It can also show the daily decrease in falling markets.
- The most interesting, and probably most critical, trend on this example is the dashed red line. This shows the trend of the individual Sales, Listings and Pendings over the time period, and on this graph is a 6th Order Polynomial for increased accuracy
For this appraisal problem analysis, I included the Active and Pending properties within the chart of Sales, from which this scatter graph was developed. Notice how the Pendings are mostly below the green linear line. More dramatic is the downturn of the dashed red line. We don’t know yet what the eventual sale price of those Pendings will be, but the data from this existing info is showing a downturn in the market for these similar properties.
Trying to understand the actual market activity is one reason why I always include these two trend lines on the graphs I include in my reports. Often, the trend lines are contradictory – like these are. It then becomes a head scratching moment to decide what the ‘real’ market trend is doing. If we only base our decisioning on straight line linear regression, we can miss the actual market movement close to the report effective date. I do not rely only on the MLS statistics, or any of the other property price trend promoters for this; each assignment graph is based on the comparable/competitive properties in the neighborhood or market area using MLS data.
Using a graph like this can also reveal the outlier properties, those that have sold for significant prices higher or lower than the grouping trend sales prices of most Sales demonstrates. Generally speaking, outliers have reasons why they sold at the prices they did, and probably should be discarded before trend lines are applied any final decisions about market trends are determined.
In closing, Pendings can reveal market trends, turns and current activity. Charts of sales over an extended time period, converted into an easily read graph can provide a visual representation of market activity that is much easier to understand than just ‘writing about it.’
If you are not familiar with spreadsheet use and graphing of charted Sales, Active and Pending data, I would encourage you to take classes on this subject, and then put this info in your reports. Your work will become more valuable to those who really want to know what is actually happening. Doing so will sustain you during this time of many in the industry trying to force appraisers off the ‘buffalo jump’ into the canyon below, smashed and broken on the rocks below.
Dave Towne on e-AppraisersDirectory.com
- New UAD Overhaul: What Appraisers Can Expect in 2025 & Beyond - September 19, 2024
- Cindy Chance Terminated - September 16, 2024
- Key Part of USPAP Not Available from TAF - July 19, 2024
I do use graph but we must always remember past performance does not indicate future results, lets not fall into the fallacy that the past predicts the future because it usally dosent
Dave, I SINCERELY APPRECIATE YOUR TIME on this. I definitely see the value as to what you’re communicating here and as my market begins to slow, being to call and visually watch this slow down initially is responsible in supporting appraisals. This is a valuable tool for Appraisers. If we disregard USPAP and take the approach that appraisals are just a look in the rearview mirror and neglect to understand the market in play right now, we miss trends like what your chart clearly illustrates.
Brandon Reich, with Spark and TrendsheetLite, was able to add your chart specs to Trendsheet Lite and I’m using it daily now to gauge markets. I do need to extend time periods of mls search to add more clarity to trends like you do.
Thank you in taking your time to help us SEE!
Bill Cobb
And Contingencies, especially when we know whither they are contingent on the buyer being able to close his last home OR the seller is able to replace the house he is selling. The problem with pending is the pending price, rather than the asking price.
I find excell and the downloads from MLS are the most understandable wording in the report
Nope, I hold whole stacks of MLS data to my forehead, put on a gypsy cap, and discern the information. Also looking them in the eye can be helpful. Did you know that 95% of statistics are made up? It’s true, I just proved it.
Used to use them but the 1004mc form is enough. Just a waste of time.