Importance of Graphing Sales
Scatter graph can help you “visualize” the trend of sale prices over a time period…
Appraisers,
I’ve been including ‘comparable’ price trend graphs in my reports (using Excel) since 2008. The graphs are based on a chart of MLS sales in the subject’s neighborhood, and can be from 1 to 5 years prior to the report Effective Date – based on housing density and number of sales. (I work in urban, suburban and rural areas.)
A Scatter Graph can help you “visualize” the trend of sale prices over a time period. It can also show questionable outlier sales that you might not want to include, because they can skew the results, and your decision.
Here’s one example of data for a recent report:
The Green Line is the linear regression line of ALL sales combined, and can be used to develop a time adjustment if one is needed. That’s the equation shown below the green line. When a calculation is done, it shows the rate of decline is approximately -2% for the time period.
The Dashed Red Line is an ‘adjustable by user’ polynomial trendline, which graphically represents the price trend of INDIVIDUAL sales in the time period. Notice how it drops off rather dramatically at the right end. This caused me to question that particular sale. When I looked at the MLS listing, it said the ‘pricing was done by the Lender’ and also showed the property to be a Short Sale.
Since it’s very apparent that this is an outlier sale, and not truly representative of actual ‘normal’ market activity, I deleted that sale from the chart of sales. (Had there been more like this, they would have been included.)
The resulting graph looks like this:
Notice how the trend lines have now ‘turned’ positive, although the Green Line is basically flat. However, there is still one recent sale that appears to be another outlier, not falling into a ‘normal’ price with the other sales – the one at $254,000 in mid-January. So I did MLS research on that sale also. It turns out this sale is a rental property which has had limited upkeep.
Both trendlines are reacting to that one sale. I decided to eliminate this sale from the data also.
The final graph looks like this:
The linear trendline in this final graph presents an indication that is more reflective of market activity in the subject’s neighborhood, based on my experience working there. The rate of growth is approximately +4.3% for the time period. The poly (red) trendline is also indicating an increase from mid-2017 to current. Based on this graph I am more confident about stating the Property Values are increasing. This graph also helps me decide if my Opinion of Market Value corresponds with market activity.
What does the “y” formula tell us? And how is the percentage of increase or decrease calculated?
The “y” formula is important:
The Linear trendline equation beginning with ‘y’ indicates the increase or decrease in PRICE on a DAILY basis. The yellow graph shows a negative number, with a corresponding declining line, and the purple graph shows a positive number and an increasing line. When you make graphs, you tell Excel to ‘include the formula’ for the linear line. The ‘x’ axis is the date range at the bottom, left to right. The ‘y’ axis is the price range on the left. This formula ‘number’ immediately after the ‘y’ up to the number before the ‘x’ is what is used to derive the overall trend percentage, and a time adjustment to sales, either on a strict dollar amount per day (easiest to calculate), or based on a percentage of the sale price factored by the weeks between sale and effective dates (slightly more difficult). If this number has a ‘-‘, Excel has calculated a decline.
The increase or decline calculation:
I have increased the precision of the ‘y’ axis in the graphs above from $5,000 to $2,500 between numbers so that you can see the Green Line start and end points more accurately.
On the yellow graph, the Green Line starts at $302,500 (top edge), and ends at ‘approximately’ $296,000. Subtract those to derive the difference = $6,500. Divide the difference amount by the starting number and convert the result to a percentage. In this case it’s ‘approximately’ -2.15%.
For the purple graph, the Green Line starts at $297,500 (bottom edge) and ends at ‘approximately’ $308,000. The difference is $10,500. Divide that by the starting number = ‘approximately’ +3.53%. This is a bit lower than my original msg stated (+4.3%) because the numerical precision for this example has been modified for better clarity.
These ‘overall’ percentage figures can help you report the Property Values TREND on the report form. If I were doing an appraisal and these were the numbers, I’d report “Stable” for the yellow graph, and “Increasing” for the purple graph. My decision about this is based on the historical property price appreciation numbers in the US, which is approximately +3% per year since the 1930’s, despite the variability of economic situations that have occurred. Even though the yellow graph demonstrates a decline, there is enough “noise” in the numbers to degrade the percentage precision somewhat. 2% is a tiny blip on the radar and is not high enough to warrant reporting a decline, in my opinion. Thus Stable. However, the purple graph justifiably is Increasing.
Keep in mind that ‘outliers’ will affect the trendlines, and the resulting calculations.
Several appraisers noted that there are two sales in the graphs that could be considered outliers. My original impression was that removing those two sales from the chart, and then producing a graph would not change the linear line very much. I was incorrect! Those two sales, when removed, cause the linear line to have a ‘y’ formula about ½ of what the purple graph shows. Meaning the trend would be STABLE, not increasing, when those sales are not included.
Spreadsheets and graphs are powerful tools for appraisers. With proper data, and incorporated into reports, they can enhance your professionalism and decision-making ability.
Using a graph like this in your reports can help you justify your decision about comparable market activity. And it will help demonstrate to the intended users that you have taken time to actually research relevant data. As Rich Hagar is fond of saying, you’ll become a better Tier One appraiser! And as I’m fond of saying, you’ll be less of a low echelon appraiser who puts limited effort into your reports.
By the way, in case you are wondering why I use a Dashed Red Line … it’s because when the color graph page is printed to black and white, the colored lines actually print in black or gray. Not everyone has access to a color printer. By indicating the poly is ‘dashed’ in your commentary, a reader will be able to distinguish the difference in the lines.
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
A great way to make a point. But only when you have enough sales that would be true Comps. Available.
But effective, I wonder if you do any AMC work? I’m sure they would have you remove the analysis from the report because “They couldn’t understand it”…
If you do AMC work make sure you can do all this within 48 hrs with all the other reports having to wait on your desk. Just sayin