Celebrating Women’s Contributions by the Numbers
Because we’re a data company after all. RiskSpan commemorates International Women’s Day by taking note of the remarkable people behind these numbers.
Connect with us at SFVegas 2024
RiskSpan is delighted to be sponsoring SFVegas 2024!
Connect with our team there to learn how we can help you move off your legacy systems, streamline workflows and transform your data.
Don’t miss these RiskSpan presenters at SFVegas 2024
Snowflake Tutorial Series: Episode 3
Using External Tables Inside Snowflake to work with Freddie Mac public data (13 million loans across 116 fields)
Using Freddie Mac public loan data as an example, this five-minute tutorial succinctly demonstrates how to:
- Create a storage integration
- Create an external stage
- Grant access to stage to other roles in Snowflake
- List objects in a stage
- Create a format file
- Read/Query data from external stage without having to create a table
- Create and use an external table in Snowflake
This is the third in a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.
Episode 1, Setting Up a Database and Uploading 28 Million Mortgage Loans, is available here.
Episode 2, Using Python User-Defined Functions in Snowflake SQL, is available here.
Future topics will include:
- OLAP vs OLTP and hybrid tables in Snowflake
- Time Travel functionality, clone and data replication
- Normalizing data and creating a single materialized view
- Dynamic tables data concepts in Snowflake
- Data share
- Data masking
- Snowpark: Data analysis (pandas) functionality in Snowflake
RiskSpan’s Snowflake Tutorial Series: Ep. 2
Learn how to use Python User-Defined Functions in Snowflake SQL
Using CPR computation for a pool of mortgage loans as an example, this six-minute tutorial succinctly demonstrates how to:
- Query Snowflake data using SQL
- Write and execute Python user-defined functions inside Snowflake
- Compute CDR using Python UDF inside Snowflake SQL
This is this second in a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.
Episode 1, Setting Up a Database and Uploading 28 Million Mortgage Loans, is available here.
Future topics will include:
- External Tables (accessing data without a database)
- OLAP vs OLTP and hybrid tables in Snowflake
- Time Travel functionality, clone and data replication
- Normalizing data and creating a single materialized view
- Dynamic tables data concepts in Snowflake
- Data share
- Data masking
- Snowpark: Data analysis (pandas) functionality in Snowflake
Prepayment Modeling: Today’s Housing Turnover Conundrum
Alex Fishbein
Director, TD Securities
Divas Sanwal
Head of Modeling, RiskSpan
Raj Dosaj
Chief Revenue Officer, RiskSpan
RiskSpan’s Snowflake Tutorial Series: Ep. 1
Learn how to create a new Snowflake database and upload large loan-level datasets
The first episode of RiskSpan’s Snowflake Tutorial Series has dropped!
This six-minute tutorial succinctly demonstrates how to:
- Set up a new Snowflake #database
- Use SnowSQL to load large datasets (28 million #mortgage loans in this example)
- Use internal staging (without a #cloud provider)
This is this first in what is expected to be a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.
Future topics will include:
- Executing complex queries using python functions in Snowflake’s SQL
- External Tables (accessing data without a database)
- OLAP vs OLTP and hybrid tables in Snowflake
- Time Travel functionality, clone and data replication
- Normalizing data and creating a single materialized view
- Dynamic tables data concepts in Snowflake
- Data share
- Data masking
- Snowpark: Data analysis (pandas) functionality in Snowflake
Agency Social Indices & Prepay Speeds
Do borrowers in “socially rich” pools respond to refinance incentives differently than other borrowers?
The decision by Fannie and Freddie to release social index disclosure data in November 2022 makes it possible for investors to direct their capital in support of first-time homebuyers, historically underserved borrowers, and people who purchase homes in traditionally underserved areas. Because socially conscious investors likely also have interest in understanding how these social pools are likely to perform, we were curious to examine and learn whether mortgage pools with higher social ratings behaved differently than pools with lower social ratings (and if a difference existed, how significant it was). To the extent that pools rich in social factors perform better (i.e., prepay more slowly) than pools generally, we expect investors to put an even higher premium on them. This in turn should result in lower rates for the borrowers whose loans contribute to pools with higher social scores.
The data is new and we are still learning things, but we are beginning to discern some differences in prepay speeds.
Definitions
First, a quick refresher on Fannie’s and Freddie’s social index terminology:
- Social Criteria Share (SCS): The percentage of loans in a given pool that meet at least one of the “social” criteria. The criteria are low-income, minority, and first-time homebuyers; homes in low-income areas, minority tracts, high-needs rural areas; homes in designated disaster areas and manufactured housing. As of December 2022, 42.12 percent of loans in the average pool satisfy at least one of these criteria.
- Social Density Score (SDS): A measure of how many criteria the average loan in a given pool satisfies. For simplicity, the index consolidates the criteria into three categories – those pertaining to income, those pertaining to the borrower, and those pertaining to the property. A pool’s SDS can be zero, 1, 2, or 3 depending on the number of categories within which the loan satisfies at least one criterion. The average SDS as of December 2022 is 0.62 (out of 3).
Do social index scores impact prepay speeds?
While it remains too early to answer this question with a great deal of certainty, historical performance data appears to show that pools with below-average social index scores prepay faster than more “social” bonds.
We first looked at a high-level, simplistic relationship between prepayments and Social Density Score. In Figure 1, below, pools with below-average Social Density Scores (blue line) prepay faster than both pools with above-average SDS (black line) and pools with the very highest SDS (green line) when they are incentivized by interest rates to do so. (Note that very little difference exists among the curves when borrowers are out of the money to refi.)
Fig. 1: Speeds by Prepay Incentive and Social Density Score
See how easy RiskSpan’s Edge Platform makes it for you to do these analyses yourself.
We note a similar trend when it comes to Social Criteria Share (see Fig. 2, below).
Fig. 2: Speeds by Prepay Incentive and Social Criteria Share
Social Pool Performance Relative to Spec Pools
Investors pay up for mortgage pools with specified characteristics. We thought it worthwhile to compare how certain types of spec pools perform relative to socially rich pools with no other specified characteristics.
Figure 3, below, compares the performance of non-spec pools with above-average Social Criteria Share (orange line) vs. spec pools for low-FICO (blue line), high-LTV (black line) and max $250k (green line) loans.
Note that, notwithstanding a lack of any other specific characteristics that investors pay up for, the high-SCS pools exhibit a somewhat better convexity profile than the max-700 FICO and min-95 LTV pools and slightly worse convexity (in most refi incentive buckets) than max-250k pools.
Fig. 3: Speeds by Prepay Incentive and Social Criteria Share: Socially Rich (Non-Spec) Pools vs. Selected Spec Pools
We observe a similar effect when we compare non-spec pools with an above-average Social Density Score to the same spec pools (Fig. 4, below).
Fig. 4: Speeds by Prepay Incentive and Social Density Score: Socially Rich (Non-Spec) Pools vs. Selected Spec Pools
See how social index scores affect speeds relative to other spec pools.
