1004MC Addendum Error

[residentialguy]

I revised my spreadsheets within a week of the announced changes. As far as I know they count correctly. I've tested them, my beta testers tested them and my users have been using them without comment.

FYI, the easy way to capture all of the applicable actives is to go back further than a year for the data. If the spreadsheet is set up correctly it will ignore all the data that came off market prior to the 12-month date. From the outset I've been telling my users to go back 2 years for their data downloads.

Do you have a link for your latest Excel download?

I found that the MLS in MN has good results, but I go back at least 2 years when searching.

 

[George Hatch]

http://www.box.net/shared/it1uf1n82v

I didn't bother to update the instructional pdf for the spreadsheets when I updated the speadsheet because the setup for the download from MLS and the process for dropping the info into the worksheet remains the same.

I dunno if anyone's interested but I have another version of this spreadsheet that includes a 2nd worksheet that analyzes the closed sales (only) from the last 2 years and breaks it down into quarters. That analysis includes a median GLA and median price/sf to better keep an eye on the composition of the datasets.

 

[Noreen]

My local MLS is provided by CoreLogic/MarketLinx/INNOVIA. They have a pretty slick 1004MC addendum utility which collects the appropriate data from MLS, puts it into an Excel worksheet, and prints it out in a 1004MC format. Pretty nice feature.

As you know, FNMA changed some of the requirements of the 1004MC (regarding current listings). I monitored the program, and it never seemed to change, and kept inserting incorrect data in the MC worksheet. I complained to INNOVIA, but they will not accept input from individuals, only the Board of Realtors Rep. So I went that path. INNOVIA's response was that I was the ONLY appraiser who had issue with the results of the 1004MC worksheet, and their techs confirmed it was correct.

So I performed an in depth analysis, and proved beyond any doubt that it was incorrect. Finally, the INNOVIA tech staff acquiesced, and corrected the calculation (and a few other errors I found as well).

What AMAZES me is that NOT ONE of the literally THOUSANDS of appraisers who use this MLS nationwide, has ever taken the time to actually confirm the correctness of the calculations of the 1004MC addendum worksheet provided by INNOVIA.

Makes you wonder...

My issue with Innovia is that they want you to use a price range on the 1004 MC but won't let you refine by style. That's plain wrong..... I don't put in price ranges ( per determined value??? Bad...) I'll use age ( it does matter here in New England) GLA and whatever the feature is that impacted my "comp" search.... assuming they are truly "comparables".

 

[residentialguy]

How is everyone handling pending sales? Are you considering them settled?

How about days on market. Are you using CDOM or just DOM of that listing?

 

[Vermonter]

I was all excited when innovia announced it's MC report. That went away once I tried it.

The errors do not only appear with the actives. The search results always vary from my regular comparable search results. It misses comparables and I haven't been able to figure out why. I've contacted them multiple times and they tell me it's fixed and it never is.

Even when you could populate from your original search, it still dropped some.

 

[Mike Garrett RAA]

I do each one manually and find it acceptable and not too time consuming. As far as pendings, I count those as part of the active listings. Until it closes...it's not a sale.

One of the things I am finding is insufficient data for meaningful trend analysis. There might be 12 sales in the 7-12 month period, 6 in the 90 to 180 day period, and only one or two in the current 90 day period due to slower sales during the winter months. The raw data would indicate a declining market when that really isn't the case.

Also, my MLS won't give me the number of listings for past periods...only what is current now. Got a stip from an underwriter saying I must include that data even if it isn't available. I just laughed.

 

[Noreen]

I do each one manually and find it acceptable and not too time consuming. As far as pendings, I count those as part of the active listings. Until it closes...it's not a sale.

One of the things I am finding is insufficient data for meaningful trend analysis. There might be 12 sales in the 7-12 month period, 6 in the 90 to 180 day period, and only one or two in the current 90 day period due to slower sales during the winter months. The raw data would indicate a declining market when that really isn't the case.

Also, my MLS won't give me the number of listings for past periods...only what is current now. Got a stip from an underwriter saying I must include that data even if it isn't available. I just laughed.

Makes you wonder how they got to their positions in the first place. I wish people would "think it through".

 

[George Hatch]

I do each one manually and find it acceptable and not too time consuming. As far as pendings, I count those as part of the active listings. Until it closes...it's not a sale.

One of the things I am finding is insufficient data for meaningful trend analysis. There might be 12 sales in the 7-12 month period, 6 in the 90 to 180 day period, and only one or two in the current 90 day period due to slower sales during the winter months. The raw data would indicate a declining market when that really isn't the case.

Also, my MLS won't give me the number of listings for past periods...only what is current now. Got a stip from an underwriter saying I must include that data even if it isn't available. I just laughed.

If your MLS provides the expired and withdrawn listings as well as the closed sales those can all be counted for when they were active. I can't imagine taking the time to do it manually but with small datasets maybe it isn't very time consuming.

I don't do many SFR appraisals so I seldom have occasion to use the 1004mc worksheet I built. Even though I'm not in the groove the process for identifying those sales and listings and their respective medians only takes me maybe 2 minutes from the time I hit the "search" button on my MLS browser. I run a single search covering the market area and dominant attributes, set the listing date for 2 years prior to my effective date, and let 'er rip. I go back 2 years under the assumption that the closed sales as of 12 months ago were not listed any earlier that a year prior to that. If I had a market where 360 day exposure times were happening I'd probably go back 3 years. The worksheets don't care how far back you go - the date parameters are set up to simply ignore all the data that doesn't fit into any of the three periods being analyzed.



The spreadsheet programs are like any other computer program, once configured correctly they're very accurate - more accurate than most people would be on a day-to-day basis. Your problems will be related to the quality of the data as reported by the MLS. No spreadsheet tool can compensate for it when the agent puts the wrong date or wrong listing price in their listing.

 

[tim midgards]

We could play devil's advocate and say there haven't been complaints because appraisers have been diligent in their own research


I use the MLS generated 1004MC for cookie cutters or to "determine" trends within a zip code (i.e. loose support of what I've already stated in the report through other sources), but it is worthless for active listings due to MLS limitations, so I complete that part manually.

An alternative to the 1004MC is to independently research the market through MLS and public records, that way you can actually stay within the neighborhood boundaries, as the MLS generated 1004MC is unable to do so.

The 1004MC really only works once in a while.

 

[tim midgards]

If your MLS provides the expired and withdrawn listings as well as the closed sales those can all be counted for when they were active. I can't imagine taking the time to do it manually but with small datasets maybe it isn't very time consuming.

I've tried that before and I will never try it again!