Monday, March 12, 2012

Excel Workbook with Details on $40.2 Billion in GSE Single Family Loans Made to 243,000 Investors in CY 2010.

I have put together a first ever Excel workbook with loan level detail for $40.254 Billion in GSE acquired loans for 243,018 investors during CY 2010.  

Note: $40 Billion is only $4.6 billion less than the TOTAL HUD proposed budget for FY 2013, while the 243,000+ investor loans represent 5.1% of the 4.8+ million single family loans purchased by the GSE's in CY 2010.

How to Download These GSE Excel Workbooks
  1. All of these GSE related Excel workbooks are included in a folder I have created on a cloud data sharing service, SpiderOak.  A link to that folder is HERE and it has also been added to the right pane as GSE CY 2010 Excel Files. [Ask me sometime about what a pain it was to find a web site that can host large file sizes].
  2. The link above will open a web page.
  3. From that web page you will see a “download” radio file on the right side that is supposed to allow you to download all Excel files in this folder as a single compressed file-I DO NOT recommend this method as I encounter errors in the size of the downloaded compressed file and in trying to open the file.
  4. INSTEAD you should A. Left mouse click on the underlined folder name on the LEFT side of the page [GSE 2010 Public Shared Excel]  to open the folder AND THEN B. Click the “download” radio button on the right side for that file to download each file individually in an uncompressed format.
  5. After downloading the file(s), navigate to the directory where you downloaded the file and double click to open.
GSE Loans Excel Workbook 2: GSE Loans to Investors [95 MB]
The data in this Excel workbook consists of data for 243,018 /$40.2 billion in single family loans purchased by the GSE’s during CY 2010 where the borrower was an investor (Code value of "2" denoting "investment property" in data field 35, "Occupancy Code"). The GSE CY 2010 data dictionary with all 39 field names and values can be found HERE (and has also been included in the SpiderOak Excel folder).

In addition, to make the workbook easier to use,  I created lookup formulas to add fields with NAMES for 8 of the 39 data elements; those additional columns begin at column “AN” of the Oregon CY 2010 GSE Data worksheet. These include columns with the county and MSA names, names for race and ethnicity, a name for the purpose of the loan, and a column that places the ratio of borrower income to median area income in one of 5 categories /“bins”.  

This workbook also includes a pivot table, allowing users to focus on geographies or demographics of interest; the default view is for a count of all loans by state showing by GSE the number of loans to investors by income groupings. Users can change the fields displayed in the pivot table to retrieve any combination of data using the data fields available. NOTE again that loans in this workbook are limited to ONLY loans made to investors.

This workbook also includes a state level summary of the count of loans to investors, the unpaid principal amount of loans at time of GSE acquisition, and columns showing the share of all GSE loans and loan $$ that went to investors, as well as state high to low rankings using the share of loans and $$ that went to investors.  (This state level investor loan summary also appears as Table 8 in the PDF file with my series of GSE CY 2010 tables, Picture of GSE Assisted Households, CY 2010). Finally, another summary table is a list of the top 10 states based on the % of all loans in that state that went to investors

Originally created and posted on the Oregon Housing Blog.

1 comment:

  1. Hello from the Urban Institute—we just posted a blog about the latest step in the ongoing effort to transform neighborhoods looped into concentrated poverty.

    I think the post might pique your curiosity!

    A plan called the Housing Opportunities and Services Together (HOST) Demonstration is testing strategies that deliberately use housing as a platform to improve the life chances of youth and adults—a new concept in the field. This two-generation model has the chance to turn these neighborhoods around in a meaningful way.