 |
“One of the best courses that I have had in 17 years!” -Amy H.
|
A Spreadsheet Solution for Estimating GLA Adjustments
by Richard H. Price
As an appraiser trainee, I was taught to calculate GLA adjustments by averaging the comparables’ sales price per square foot and multiplying it by 50%, up to a maximum of about $80/sq. ft. For the most part, it seemed to produce reasonable results.
However, the production of reasonable results does not satisfy the requirement to provide support for adjustment factors. Over the past several years, I have reviewed dozens of appraisals and I can’t recall one instance where the appraiser has included such support. This is not to be critical. Most of the appraisals I have prepared have not included support, either. I have, however, done a certain amount of research on methods of arriving at GLA adjustments based on data analysis as opposed to rules of thumb.
Matched Pair
Matched pair analysis is one acceptable method. In an ideal situation, it makes sense. Given two properties with different GLAs, but otherwise identical, in the same neighborhood on equivalent lots and with the same date of sale, the adjustment factor is calculated as the difference in sales prices is divided by the difference in GLAs. However, this is an ideal situation that is rarely encountered. There are almost always differences in lot size, condition and other features that can compromise the validity of this approach.
Multiple Regression
Multiple regression analysis is another method that should be the gold standard as it uses statistical analysis to produce adjustment factors for a whole range of parameters. For example, date of sale, lot size, GLA, stories, age, bath count, garage spaces, fireplace, pool & spa etc. But in my experience, it isn’t. In most cases when I’ve used this method, multiple regression produces one or more adjustment factors (regression coefficients) that are negative when they should be positive. This happens most often with low impact parameters (independent variables) such as bath count and garage spaces. The reason is that they are overwhelmed by the high impact parameters (GLA, date of sale, lot size differences) and/or unquantified parameters such as condition and quality.
The fix for this is to re-run the analysis without the offending parameters. The re-run results can then be used as the adjustment factors in the sales comparison grid, along with the appraiser’s opinions for the omitted adjustment factors. When this happens, it seems to me that the validity of the results is also compromised.
(story continues below)
(story continues)
Linear Regression
Another approach to arriving at a supportable GLA adjustment factor is linear regression. Unlike matched pair, a reasonably-sized population of comparables is analyzed. The process is much less complicated than multiple regression and can be done using a simple spreadsheet. Basically, statistical analysis is used to determine the relationship between price and GLA and takes the form of y = mx + b or m = (y – b)/x where “m” is the slope or GLA adjustment factor, “y” is the bare price and “b” is a constant. The high impact variable relationships are calculated independently and the low impact adjustments are assigned based upon experience and/or prior matched pair analysis. Essentially, for each potential comparable, the sales price is modified by applying the high impact and low impact adjustments to the sales price to arrive at a bare price that is a function of GLA only. Then linear regression is used to calculate the relationship between GLA and bare price, producing the “m” GLA adjustment factor.
For the high impact variables, the first step is to calculate the impact of Date of Sale on the sales prices of the considered comparables. This is simply the difference between effective date and the comparable’s date of sale multiplied by the determined price trend for the market area. The second step is to calculate the comparables’ lot values based on their size. I normally start this process by using comparable lot sales comparison to estimate the Subject’s lot value, a process that is normally part of the appraisal. Once this is determined, the value of each potential comparable’s lot can be calculated using a formula that relates price per square foot to the lot square footage in the format of y = ax^b or a = y/(x^b). The constant “a” is calculated using the Subject’s lot value per square foot and the assigned “b” constant. I normally develop this constant as part of the appraisal lot value process that produces adjustment for differences in lot size in addition to the estimate of the Subject’s lot value. It is typically about -0.7.
(story continues below)
(story continues)
Condition can be a high impact variable. I normally review the MLS sheet for each potential comparable and assign a condition rating (C1 thru C6) based on the description and photos. The condition impact for each comparable is calculated as the difference between the benchmark condition rating of 2 and the comparable’s rating multiplied times the sales price times a percentage determined through experience and/or matched pair analysis. A typical percentage is 5% of the sales price for one rating step.
I prepared an example spreadsheet for Working RE readers (Visit WorkingRE.com/spreadsheet for more). This spreadsheet is included to illustrate the application of the linear regression approach. In this example, I included the 17 potential comparable sales used in the appraisal process. They are market area sales taking place within one year of the effective date and are reasonably close to the Subject’s age, size and lot size. In the appraisal process, five of the most recent and most similar sales were selected for use in the sales comparison grid. The information in the spreadsheet shaded in blue was imported from MLS. The Subject lot value and parameters used in the calculations are manual inputs and are shaded in yellow.
The remaining unshaded cells contain the calculated values used to determine the GLA adjustment factor which is the slope (m) of the Bare $ vs. GLA regression line. In this example the slope is $80.64/sqft. which is 47.1% of the average comparable value/sqft. This estimate rounded to $80/sq. ft. is intended to estimate the value impact of square footage differences between the Subject and each comparable. To be consistent, the adjustment factors used for the high and low impact parameters used in the GLA analysis should also be used in the appraisal sales comparison grid.
About the Author
Richard Price is a Certified Residential Appraiser and broker and has been doing appraisals in the Austin, TX area for the past eight years. Prior to his career in real estate, Richard worked as a senior project manager and director of engineering for a design/build engineering company that specialized in bio-fuel plants. He has BSCE and MSCE degrees from Purdue University.
CE Online – 7 Hours (AQB Approved)
Identifying and Correcting Persistent Appraisal Failures
Richard Hagar, SRA, is an educator, author and owner of a busy appraisal office in the state of Washington. Hagar now offers his legendary adjustments course for CE credit in over
40 states through OREPEducation.org. The new 7-hour online CE course Identifying and Correcting Persistent Appraisal Failures shows appraisers how to avoid CU’s red flags, minimize callbacks, save time, and earn more! Learn how to improve the quality of your reports and build defensible reports! OREP insureds save on this approved coursework. Sign up today at
www.OREPEducation.org.
Sign Up Now! $119 (7 Hrs)
OREP
Insured’s Price: $99
>Opt-In to Working RE Newsletters
>Shop Appraiser Insurance
>Shop Real Estate Agent
Insurance
Send your story submission/idea to the Editor:
isaac@orep.org
by Richard
I could not get the link to the spreadsheet to work.
-by admin
Hi Richard,
Please go to: https://www.workingre.com/spreadsheet and it should automatically download.
Thanks!
-by Steven Smith
Richard, many of us were Told what to do, in my case I was given a List of Adjustments.
-Our industry has never, at the residential level as far as lender work, charged enough to document and support adjustments.
Using a List or method as mentioned by you, to make adjustments has been common place.
Most residential appraisers do not use spreadsheets, do pairings, regression or linear regressions. Some use vendor provided software but do not do anything themselves.
Meaning, most are controlled by their forms software provider and whatever package they get with it.
The processes of supporting/documenting adjustments are unchanged over the decades. There is nothing particularly new or revolutionary to learn. Just learning the basics of supporting adjustments would be a great step forward for the industry, IF they want to be able to be paid for their time.