Runoff Analysis

The RAP® Portfolio Insights Suite uses advanced analytics to improve customer retention and increase growth for innovators in the origination and servicing space.

Retention here refers to the ability of a mortgage lender to keep existing mortgage customers and loans within its portfolio.

Retention Strategies often include:

Portfolio Growth is defined as the expansion of a lender’s portfolio by acquiring new mortgage loans.

Strategies often include:

Both retention and growth are crucial for a mortgage lender’s financial health and overall market competitiveness. Retention helps ensure a stable revenue stream from interest payments on existing loans, while growth helps increase market share and overall revenue potential.

The RAP® Portfolio Insights Suite takes a data-driven approach to portfolio retention and growth strategies.

Introduction

Categorize mortgage transactions (sales, refis, reverse mortgages, natural payoffs) and classify paid in full loans as retained or lost. Get a competitive view of which lenders and loan officers you are losing out to. Understand which types of loan products you retain the most of and which regions you perform the best in.

1. Portfolio Match & Append. Match your monthly portfolio paid in full snapshot with ICE data to get a unique property ID assigned. This allows you to utilize the ICE Property, Real Estate and Mortgage datasets and tie them back to your own portfolio.

%%local 

 

from ipywidgets import widgets,Button,Layout,Label 

import IPython 

import time 

 

layout= Layout(width='500px',display='left',Positioning='left',justify_content='space-between') 

style = {'description_width': '400px'} 

 

start_date= widgets.DatePicker(description='Start Date', layout=layout) 

end_date= widgets.DatePicker(description='End Date', layout=layout) 

label1 = widgets.HTML(value='Database to save results to (No special characters or spaces)') 

db_for_results= widgets.Text(value="database_name",style =style,layout=layout) 

label2 = widgets.HTML(value='Table to save results to (No special characters or spaces)') 

table_name_for_results= widgets.Text(value="table_name",style =style,layout=layout) 

label3 = widgets.Label(value='Notebook File Path') 

file_path= widgets.Text(value= "Paste the file path here",style =style,layout=layout) 

run_button = widgets.Button(description="Run", style=dict(font_weight='bold', text_color='white',button_color='blue')) 

display(start_date,end_date,run_button) 

 

def on_button_clicked(b): 

  global localString,start_date_new,end_date_new 
  start_date_new=str(start_date.value).replace(",)",")") 
  end_date_new=str(end_date.value).replace(",)",")") 

   db_for_results_new=str(db_for_results.value).replace(",)",")") 

   table_name_for_results_new=str(table_name_for_results.value).replace(",)",")") 

  file_path_new=str(file_path.value).replace(",)",")") 
  local_list=[start_date_new,end_date_new]    
  localString = ' | '.join(local_list) 
  run_button.style.button_color = 'gray' 
  time.sleep(1) 
  run_button.style.button_color = 'blue' 
run_button.on_click(on_button_clicked) 

query = """ 

create or replace temp view ma_runoff_deed{} as 

select  
row_number() over (partition by p.input_record_number, d.fipscode, d.assessors_parcel_number order by p.input_record_number, d.fipscode, d.assessors_parcel_number, d.bkfsinternal_pid) as instance, 

p.input_record_number, 

-- ADDRESS DETAIL 
d.fipscode, d.assessors_parcel_number, d.property_full_street_address, d.property_city_name, d.property_state, d.property_zip_code, 

p.{}, 

-- DEED DETAIL 
d.original_date_of_contract, d.recording_date, d.document_type_code, 

-- LOAN DETAIL 
d.concurrent_tdloan_type, d.concurrent_tdloan_amount, d.concurrent_tdtype_financing, d.concurrent_tdinterest_rate, d.concurrent_tddue_date, d.adjustable_rate_rider, d.adjustable_rate_index, d.interest_only_period, d.fixed_step_rate_rider, d.stand_alone_refi, d.equity_credit_line,  

-- LENDER DETAIL 
d.concurrent_tdlender_name, d.concurrent_tdlender_type, d.loan_organization_nmls_id, d.loan_organization_name, d.mortgage_broker_nmls_id, d.mortgage_broker, d.loan_officer_nmls_id, d.loan_officer_name, 

-- BORROWER DETAIL 
d.number1_buyer_first_name_and_middle_name, d.number1_buyer_last_name_or_corporation_name, 

-- PROPERTY DETAIL 
d.assessors_land_use, d.residential_indicator, 

-- OTHER 

d.deed_transaction_type, d.bkfsinternal_pid 

from ma_runoff_deedpid{} p 
  inner join public_records.deed_nationwide d on 
    p.fipscode = d.fipscode and p.assessors_parcel_number = d.assessors_parcel_number and p.bkfsinternal_pid = d.bkfsinternal_pid 

where residential_indicator = 1 

""".format(table_ext_for_results, mapping_payoff_date, table_ext_for_results) 

spark.sql(query) 

4. Add additional data from the active lien table and release tables that enable mortgage transaction classification into sales, refis, forgiveness programs, reverse mortgages and natural payoffs.

5. Organize transactions into recaptured and lost categories. Add additional detail on the next transaction that has been classified in 3. when it is available

6. Visualize results in Plotly and/or Tableau. Track monthly changes in loss%.

Sources:

https://mortgageinnovators.com/a-guide-to-customer-retention-in-the-current-mortgage-industry/