Deep Dive: Pooling Module (pwrpool)

Narrative

The Pooling module is a mortgage loan securitization engine that groups individual loans into pools for sale to investors (Fannie Mae, Freddie Mac, Ginnie Mae, or whole-loan buyers). A pool is a named container that holds loans meeting configurable eligibility criteria, subject to constraints derived from a hierarchy of business agreements (master agreement → master commitment → marketing program → instrument → pool-level rules).

The pooling engine dynamically constructs SQL eligibility criteria from 12 different sources, retrieves qualifying loans, and allocates them to pools respecting dollar limits, percentage limits, weighted average limits, and count limits. The result is pools ready for designation to trades and securities.

---

Pooling Engine Algorithm

12 Syntax Sources (AND-ed together)

#	Source	Example
1	Default Pooling Criteria	loan_status = 'funded' AND cancelled = 'N'
2	Guarantor Securitization Rules	Guarantor-specific eligibility
3	Master Agreement Rules	Agreement-level criteria
4	Master Commitment Rules	Commitment-level criteria
5	Instrument Syntax	Eligible instruments (OR-joined)
6	Marketing Program Rules	Program-specific criteria
7	Pool Securitization Rules	Pool-level securitization rules
8	Pool Loan Qualifier	Free-form SQL on the pool
9	Note Rate Syntax	Computed min/max note rate range
10	Investor Instrument Rules	Per-instrument eligibility
11	Investor Rules	Per-investor eligibility
12	Note Rate Spread Limit	Max note rate spread within pool

---

Variance/Tolerance System

CASE "high":
    optimal_amount = target + (target * tolerance%)
    max_possible   = optimal_amount

CASE "low":
    optimal_amount = target - (target * tolerance%)
    max_possible   = target

CASE "target":
    optimal_amount = target
    max_possible   = target

---

Constraint Satisfaction Algorithm

Each constraint is defined as: "Where <syntax>, the <function>(<column>) should not exceed <limit>"

Supported aggregate functions:

Function	Check
count()	Would adding this loan exceed the count limit?
sum()	Would adding this loan's value exceed the sum limit?
avg()	Would the new average exceed the limit?
wtavg()	Would the weighted average exceed the limit?

Percent variants divide the qualifying subset by the total pool.

The engine runs a 3-pass allocation loop for both previously-pooled and new loans, because constraint headroom changes as loans are added -- a loan skipped in pass 1 may fit after other loans change the running totals.

---

Fee Calculation System

Note Rate Range (MBS Pools)

lower_bound = coupon_rate + servicing_fee + (guarantee_fee - max_fee_buy_down)
upper_bound = coupon_rate + guarantee_fee + max_fee_buy_up + servicing_fee + max_excess_servicing

Fee Slack Allocation

The "slack" is the note rate spread available for fee allocation:

slack = note_rate - (coupon_rate + servicing_fee + (guarantee_fee - max_fee_buy_down))

Three allocation strategies, each with values a (active/optimize), f (fixed/first), n (none):

• buydown_strategy -- Guarantee fee buy-down
• buyup_strategy -- Guarantee fee buy-up
• excess_strategy -- Excess servicing

This creates 24 possible allocation paths. When all 3 strategies are "active", the engine runs two parallel paths and picks the one yielding the highest excess servicing value.

Tiered Excess Servicing

Supports tiered grids from psusr_tiered_xs_current with basis points and multipliers per tier.

MSR Pricing

Joins rmcat_msr_prices with investor instrument, zone states, and loan data to determine per-loan required servicing and excess servicing values.

Buy-Up/Buy-Down Ratios

Retrieved from ratio tables based on instrument term and rate type (pass-through or note).

---

Business Rules

Pool Eligibility

• All 12 syntax sources are AND-ed into a single WHERE clause
• Pool name filter ensures only unpooled loans or loans already in this pool are considered
• Loan amount must be > 0
• Guaranteed re-entry (pooled_loan_priority = 'g') bypasses syntax for previously-pooled loans

Pool Protection

• Settled pools cannot be re-run (checked via trade settlement dates and security designations)
• Locked pools (designated to securities or trades) show a warning but can still be re-run

Allocation

• Previously-pooled loans get priority in re-allocation
• Multi-pass loop (3 passes) handles constraint interdependencies
• If no sort specified, loans are in random order (NEWID() on SQL Server)
• Optimization step (tolerance=low only) tries to add the smallest loan that fills the gap

---

Data Model

Table	Purpose
pscat_pools	Master pool records (target, tolerance, fees, syntax, sort)
pscat_pools_ach	Pool ACH banking information
loan	Active loans with pool assignment column
loan_shipped	Settled loans
pscat_master_agreements	Master agreement definitions
pscat_master_commitments	Master commitment definitions
pscat_programs	Marketing program definitions
pscat_instruments	Instrument configuration
pscat_securitization_rules	SQL eligibility rules
pscat_pool_security_relation	Pool-to-security junction
pscat_trades_pools_relation	Pool-to-trade junction
pscat_pool_fee_overrides	Per-pool fee overrides
pscat_pool_note_rate_cpn_adj	Note rate coupon adjustments
rmcat_msr_prices	MSR pricing grid
psusr_tiered_xs_current	Tiered excess servicing grid
pscat_current_values	System defaults (default pooling criteria)

Key NVOs

NVO	Purpose
n_cst_pooling	Pool utilities: unpool, rule verification, allocation updates
n_cst_pooling_engine	Core 12-step pooling algorithm
n_cst_pooling_syntax	Builds cumulative SQL from 12 syntax sources
n_cst_pooling_limits	Pool constraint management and checking
n_cst_calc_fee_values_dw	Fee allocation engine (24-path strategy)