Non-linear Market Conditions Adjustments
In the dynamic world of real estate, it’s crucial to adapt to changing market conditions, especially when it comes to property valuation. One effective method for this is utilizing adjustments to non-linear market conditions. In this post, I’ll walk you through a step-by-step process for using Excel to analyze sales data and derive meaningful insights about property value trends over time.
Step 1: Gather Your Data
Download sales information from MLS. For this 2080sf property, I used MLS Areas 1, 2, and 3 (Louisville), 2,000-3,200sf GLA, sold in the last year.
Below is a snip of the data. I had 144 total sales. The two columns you need are shaded green.
The sold price/square foot is calculated, MLS did not export that.
Step 2: Create a chart in Excel. (Insert, Scatter)
Next, it’s time to visualize your sales data using Excel. Your X column is the sale dates and the Y column is the price/square foot. Here is what my series information looks like.
You will get this chart. This chart will provide a clear visual of how prices have changed over time.
Step 3: Add a Trendline 
Choose Add Trendline, More Options, then choose Polynomial with “2” order. (I tried 3 and 4 but the results were very skewed. I imagine this would require thousands of sales.
Sometimes Excel will put in a standard linear trendline, so delete that in the chart if it does. The chart looks like this (I changed the color and thickness of the trendline so it shows better. This looks flat, but it is a curve. 
I formatted my chart quickly to match the fonts I use in my Total Forms reports. I added Axis Titles as well.
The curved (non-linear) trendline is shown, as well as the formula.
Step 4: Extract Coefficients Using the LINEST Function
Use the LINEST function in Excel to show the coefficients from the sales data. Pick any cell to put this in. N2:N145 represents the Y values (price/square foot) and R2:R145 represents the X values (sale dates). We are trying to predict the price per square foot on any given date along the curve so we can see if there are changes over time.
The formula is as follows: =LINEST(N2:N145,R2:R145^{1,2},TRUE,FALSE)
This is a quadratic formula. If you decide to use 3 orders, then you would have {1,2,3}
The chart looks like this:
Then, I add the effective date, as well as the comparable contract dates, which looks like this: 
The ‘change’ column gives you the market conditions adjustment to make to each sale.
Step 5: Incorporate Effective and Comparable Dates
Then, plot your 4 dates on the chart (orange) to put in your report and make the adjustments.
Conclusion
By following these steps, you can achieve a nuanced understanding of how market conditions impact property valuations over time. This analytical approach not only helps in adjusting current sale prices accordingly but also equips you with the data-driven insights needed for informed decision-making in the real estate market.
Embracing data analysis in your real estate evaluations can provide a significant advantage in today’s competitive landscape. Try it out with your sales data and see the trends unfold!
