Each scenario includes a sample Cortex Code input and the corresponding SQL example, highlighting potential refinance opportunities based on equity, loan structure, and interest rate characteristics.
Scenario: First Refinance Opportunities (Removing Mortgage Insurance)
Use this scenario to identify properties where the borrower may now qualify for a first refinance due to improved equity, including cases where mortgage insurance may be removed.
Cortex Code input:
Write an SQL query that returns properties in Colorado where the home was purchased within a specific date range, the lien was created within the same period, the loan type is FHA, the loan-to-value ratio is below 80%, and the loan is not in default.
SQL example:
SELECT
address,
city,
county,
deed_market_value_price,
lien1_amount,
hc_value_estimate,
(
lien1_amount / NULLIF(hc_value_estimate, 0) * 100
) AS ltv_ratio,
lien1_contract_date,
deed_date,
lien1_loan_type
FROM
**********
WHERE
state ILIKE '%CO%'
lien1_loan_type ILIKE '%FHA%'
AND TRY_TO_DATE (deed_date) BETWEEN '2023-1-01'
AND '2023-12-31'
AND lien1_contract_date BETWEEN '2023-1-01'
AND '2023-12-31'
AND (
lien1_amount / NULLIF(hc_value_estimate, 0) * 100
) < 80
AND default_yn = 0
ORDER BY
city,
address;
Scenario: Limited Equity at Purchase, Improved Loan-to-Value Today
Use this scenario to identify properties that were purchased with little to no equity but now meet standard refinance thresholds due to appreciation or principal paydown.
Cortex Code input:
Write an SQL query that returns properties in [insert state here] where the home was purchased within a [specific date range], the original lien-to-price ratio was greater than 95% at purchase, and the current total loan-to-value ratio is 80% or less.
SQL example:
SELECT
address,
city,
county,
deed_price,
lien1_amount,
lien_amount_total,
hc_value_estimate,
(
lien1_amount / NULLIF(
TRY_TO_DECIMAL (REPLACE (deed_price, '$', '')),
0
)
) AS lien1_to_price_ratio,
(lien_amount_total / NULLIF(hc_value_estimate, 0)) AS total_lien_to_value_ratio,
lien1_contract_date,
deed_date
FROM
XXXXX
WHERE
TRY_TO_DATE (deed_date) BETWEEN '2023-10-01'
AND '2023-10-31'
AND lien1_contract_date BETWEEN '2023-10-01'
AND '2023-10-31'
AND (
lien1_amount / NULLIF(
TRY_TO_DECIMAL (REPLACE (deed_price, '$', '')),
0
)
) > 0.95
AND (lien_amount_total / NULLIF(hc_value_estimate, 0)) < 0.80
AND TRY_TO_DECIMAL (REPLACE (deed_price, '$', '')) > 0
AND hc_value_estimate > 0
ORDER BY
city,
address;
Scenario: Cash-Out Refinance Candidates
Use this scenario to find high-equity properties that may be good candidates for a cash-out refinance based on property value, existing lien balances, interest rates, and loan seasoning.
Cortex Code input:
Write an SQL query that identifies high-equity properties in California where the home value exceeds $200K, total liens are less than the home value, the loan is not in default, the first lien interest rate is above 6.5%, and the loan is at least six months old. Exclude construction, modification, and assumption loans.
SQL example:
SELECT
address,
city,
county,
state,
zipcode,
property_type,
year_built,
living_area,
lot_size,
hc_value_estimate,
lien1_amount,
lien1_loan_type,
lien1_interest_rate_used,
lien1_contract_date,
lien2_amount,
lien2_loan_type,
lien2_interest_rate_used,
lien2_contract_date,
hc_value_estimate - lien_amount_total AS est_equity,
ROUND(lien_amount_total / hc_value_estimate, 4) AS ltv_percent,
ROUND((lien1_amount + COALESCE(lien2_amount, 0)) / hc_value_estimate, 4) AS combined_ltv_percent,
owner_name,
last_close_date,
last_close_price,
tax_year,
tax_amount_annual,
lien1_lender_name,
lien2_lender_name,
default_yn
FROM
**********
WHERE
state ILIKE 'CA'
AND hc_value_estimate > 200000
AND lien_amount_total > 0
AND lien_amount_total < hc_value_estimate
AND (default_yn = 0 OR default_yn IS NULL)
AND lien1_interest_rate_used > 6.5
AND lien1_contract_date < DATEADD(MONTH, -6, CURRENT_DATE)
AND lien1_loan_type NOT IN ('CONSTRUCTION', 'MODIFICATION', 'ASSUMPTION')
ORDER BY
combined_ltv_percent ASC,
est_equity DESC;
Scenario: Second Mortgage or HELOC Opportunities
Use this scenario to identify properties that may be strong candidates for a second mortgage or HELOC based on available equity, favorable first-lien interest rates, and the absence of an existing second lien.
Cortex Code input:
Write an SQL query that finds high-equity properties where the home value exceeds $200K, the first lien balance is less than the home value, the first lien interest rate is below 5%, there is no second lien on record, and the loan is not in default.
SQL example:
SELECT
address,
city,
zipcode,
property_type,
year_built,
hc_value_estimate AS home_value,
lien1_amount,
lien1_interest_rate_used,
lien1_lender_name,
lien1_contract_date,
(hc_value_estimate - lien1_amount) AS estimated_equity,
(lien1_amount / NULLIF(hc_value_estimate, 0)) * 100 AS combined_ltv
FROM
XXXXX
WHERE
hc_value_estimate > 200000
AND lien1_amount < hc_value_estimate
AND lien1_interest_rate_used < 5
AND default_yn = 0
AND lien2_amount IS NULL
AND NOT hc_value_estimate IS NULL
AND NOT lien1_amount IS NULL
ORDER BY
combined_ltv ASC,
estimated_equity DESC;