Analytical RoadMap Cleaning

Which segments of the Nebraska mortgage market to focus on?

2

Nebraska mortgage that Fannie Mae acquired between 2000-2021

Data visualization, Classification models, identify important features related to profit and defaulting loan

Target best segment through dominating variables

Successfully expand the market of Nebraska while fitting the 2 objectives

Business Outcome

Business Action

Data

Data analytics

Data Analysis (Who is the Competitors?)

Profit/Defaulting Loans & Servicer

​

​

​

Finding:

• The top 5 competitors that has the highest profit. In these banks, Wells Fargo Bank has the highest profit that far more greater than other banks, and a relatively low defaulting loan.

​

Recommendation:

• Pay attention to the Wells Fargo Bank’s features. Wells Fargo Bank is one of the top bank in US, which is focus on Wholesale Banking, always do bank services sold to large clients.

3

Data Analysis (What is a good consideration factor?)

Profit/Defaulting Loans & Credit Score Group

Finding:

• From the credit score group, Fair, Good and Very good have the highest groups of profit.
• When the credit score increases, the percentage of defaulting loan decreases.

​

Recommendation:

• We recommend Great Lakes Midwest Bank investigate more on who has a very good credit score (740-799), which as a high profit and low defaulting loan transactions.

4

Data Analysis (Where is the good region?)

Profit & Region Segmentation

The region group distinguishes where the property securing the mortgage loan.

​

Finding:

• Throughout Nebraska, the further southeast, the higher the profit.
• The more deviated from the central position, the less profitable.

​

Recommendation:

• Promote on real estate loan sales in highlighted areas

5

Nebraska

Data Analysis (When is current trend going towards?)

Profit/Defaulting Loans & Year Group

Finding:

​

• The profit growth range is increasing while the fraud rate is steady decreasing.
• The defaulting loans rate has a decreasing trend during the long term.
• Between year 2020-2021, the decreasing of profit might because of the covid pandemic. Other than year 2021, it shows a increasing trend of profit in the short term.

This is the slide footer, which can be edited under Header & Footer, along with slide numbers and date

6

5/28/18

Data Analysis (Why is currently a good investment timing?)

Original Interest Rate & Year

Finding:

​

• The average mortgage Interest rate for each transaction is decreasing during years. Number of transactions significantly increases from 2018-2021.

​

• The bank will have a lower profit, but more people are willing to borrow money for buying houses or cars e.g., because of lower opportunity cost.

7

Model

Percentage of defaulting loan = (Number of Delinquent + Number of Deed-in-Lieu) / Number of total loans

​

​

​

Random Forest

F30_UPB : UPB at the Time of Removal (30 Days)

ORIG_VAL : The original value of the property.

LAST_RT : Current Interest Rate

CSCORE_B : Borrower Credit Score at Origination

DTI : Debt-To-Income

OCLTV : Original Combined Loan to Value Ratio

Findings

Output before covid

According to the data that is collected before the covid time, there are three segments in the market that this research will suggest the bank to focus on:

​

• Sell loans that have original value of at least $275,000, and to people whose Borrower Credit Score at Origination is in the range of 751-776.
• This is a high risky segment, since compare to the segment of 0.15% defaulting loan percentage with $284,165.3 average profit, the suggested segment with 0.45% bad loan percentage with $301,444.9 average profit seems more risky but it can get more profit.

​

​

​

9

High risk high return

Findings

Output before covid

The second suggested segment of the market:

• Sell loans and set the Interest Rate in the range of 6-11.5, and to people whose Borrower Credit Score at Origination is in the range of 776-795.
• This segment is considered as a medium risky segment, the reason to say that is the second best one with 0.35 % defaulting loan percentage in this range is slightly higher than the suggested one with 0.26%, and the average profit is $200,137.9, which is much higher than $190,914.4, and we think it is worthy to take that risk in order to get higher profit.

​

​

​

10

Medium risk, medium return

Findings

Output before COVID

The third suggested segment of the market:

• Sell loans that have Original Value from $275,000 to $6,950,000 with the Interest Rate in the range of 5-6.
• This segment is considered as a suitable segment, since in these 25 choices, it is clear that the suggested one has the highest average profit with the medium percentage of defaulting loan, which is the best choice.

​

11

medium risk, high return

Findings

Output during COVID

We believe that the effect of covid will last for years, which means the findings after 2020 March will have certain reference value for future predictions.

• The first segment that is suggested after covid and as a prediction to the future, our bank should sell more loans to people with the Original Value of $275,000 to $6,950,000 and set the Interest Rate to 6-11.5.
• The second segment of the market is those people with Borrower Credit Score at Origination is in the range of 712-751 and the Original Value is set as higher than $275,000.
• The third segment is setting the Interest Rate from 4.25-5 and selling to people with Borrower Credit Score at Origination from 751-776.

​

There are some differences between the period during covid and before covid, it is clear that set a lower interest rate to people with medium C score will be better, and the similarity is that original value should always be in the highest region.

​

12

Highest profit with low risk

Summary

Key takeaways from data analytics & modelling

• Focus on the segment with an excellent credit score (740-799) and customer who has property in the southeast of Nebraska, along with high profit and low defaulting loan transactions.
• Segment of market before the covid:
• Original Value of at least $275,000, and to people whose Borrower Credit Score at Origination in the range of 751-776. Which is a high risky segment.
• Original Value from $275,000 to $6,950,000 with the Interest Rate in the range of 5-6. Consider as the segment of medium risk and medium return.
• Interest Rate in the range of 6-11.5, and to people whose Borrower Credit Score at Origination in the range of 776-795. Which is the most suitable.
• Segment of market during the covid:
• Original Value of $275,000 to $6,950,000 and set the Interest Rate to 6-11.5.
• Borrower Credit Score at Origination is in the range of 712-751 and the Original Value is set as higher than $275,000.
• Interest Rate from 4.25-5 and selling to people with Borrower Credit Score at Origination from 751-776.

​

13

Summary

Recommendation

​

• The bank should focus on the segments of excellent credit score (740-799) and property in the southeast of Nebraska of the Nebraska mortgage market.
• The suggestion that will be given to the bank in the short run, which is during the COVID period, the segment of Interest Rate from 4.25-5 and selling to people with Borrower Credit Score at Origination from 751-776 is the best segment we will advise bank to focus.
• In the long run, we suggest that the situation before Covid should be considered more, since after the effect of Covid subsides, market situation will move closer to before. Hence the best segment to be suggested is the one with Original Value from $275,000 to $6,950,000 with the Interest Rate in the range of 5-6.

​

​

Future consideration

• We need to investigate more on Great Lakes Midwest Bank, which is a strong competitor.
• As the effect of COVID continues, the bank should set lower interest rate and focus on the people with Borrower Credit Score at Origination 751 or higher in order to avoid the defaulting loans.
• From the economic background of Nebraska, it is clear that the economy of this state will become more prosperous and suitable for investment in the future.

14

Thank you!

Any Questions?

Appendixata Cleaning

Content

This is the slide footer, which can be edited under Header & Footer, along with slide numbers and date

16

5/28/18

Tableau Interactive Dashboard: https://prod-ca-a.online.tableau.com/#/site/xueyijia/workbooks/5938?:origin=card_share_link

Code and Datasets: https://drive.google.com/drive/folders/11AGIiUTq9z6hMLML3TDtRP0HGbv-O3LI?usp=sharing

​

Appendixata Cleaning

Data Cleaning

This is the slide footer, which can be edited under Header & Footer, along with slide numbers and date

17

5/28/18

Approach:

• Calculates profit of each loan.
• Classifies profits into 5 classes by quantile binning.
• Drops not critical variables, like dates, msa, and so on.
• Applies Random Forest Model(RF) to the data
• Obtains top 3 dominant variables from RF: Current Interest Rate(LAST_RT), Original value of the property(ORIG_VAL), and Borrower Credit Score at Origination (CSCORE_B).
• Applies quantile binning to the LAST_RT, ORIG_VAL, and CSCORE_B to divide them into 5 classes with labels 1,2 ,3 ,4, 5 respectively.
• Due to the covid pandemic, the economy is fluctuating and unpredictable, therefore it’s wise to divide the data into 2 sets (before covid and during covid, the partition is on 2021/2/29)

​

F30_UPB = UPB at the Time of Removal (30 Days)

​

Profit = Original UPB * (Original Rate / 100) * (Original Loan Term / 12) - F30_UPB

Percentage of defaulting loan

During COVID

Percentage of profit

During COVID