Inputs

Many of the calculations that can be specified by the calcs attribute in the lp_calc implementation of the Universal Calculation Library can be customized by providing specific information about filtering, summarizing, calculating new metrics, and linking in different data sets.

This section details the inputs available for calculations in the lp_calc implementation of the Universal Calculation Library. These inputs allow for further customization of the calculation outputs. The inputs are specified using custom attributes only available in the Universal Calculation Library.

If an input only works with a limited number of calculations, those will be specified here. If no calculations are specified, assume the input is valid for all calculations.

A basic example is shown below:

<import path="path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr" ncpr="1,3,6,12" breaks="deal_name"/>

In the example above the ncpr input accepts a comma-separated list of integer values, and the breaks attribute specifies a column name for grouping the final tabulation. The example will calculate 1-month, 3-month, 6-month, and 12-month CPRs broken out by deal.

The following groups of inputs are available to the lp_calc block.

Misc inputs

inc_always_current
Accepts a 1 to activate. Will be ignored if any other value is provided to the input. When set to true, this input will add an Always Current (AC) column to the roll rate matrix.
inc_payoff_in_always_current
Accepts a 1 to activate. If active, will change the way the Always Current (AC) flag column is calculated. When this flag is not provided, payoff events may be counted as always current. When this input is active, payoffs are not considered to have a status of always current.
ncpr
Accepts a comma-separated list of integers corresponding to the prepayment time horizon (i.e., 1 is a 1-month CPR and 12 is a 12-month CPR).

The default if no value is provided is 1.

This input is only useable with the following calculation:
  • cpr
ncdr
Accepts a comma-separated list of integers corresponding to the default time horizon (i.e., 1 is a 1-month CDR and 12 is a 12-month CDR).

The default if no value is provided is 1.

This input is only useable with the following calculation:
  • cdr
suffix
Accepts a text value that is a column name suffix. Appends the text value provided to the column name of all columns created by the library.
dlq_col
Determines which metric is used in delinquency calculations.

Accepts one of two values:

  • mba
  • ots

The default is mba.

notab
Executes the entire query up to the final tabulation. Useful for debugging.
nroll
Accepts an integer value representing the number of months between the start and end of the roll period.
This input is only useable with the following calculation:
  • roll_rates
balance_col
Accepts one of two column names:
inv_bal
Investor balance
balance
Actual balance of the loan

The default value is inv_bal.

Filter inputs

cusips
Accepts a comma-separated list of valid CUSIP values by which to filter the table prior to the final tabulation.
start_date
Accepts a month or date to filter the output date range. This attribute is used in conjunction with end_date.

The filter is applied just prior to the final tabulation so that g_functions may be fully evaluated before tabulating.

Note: The start_date and end_date must be used together. In addition, the value of start_date must be less than the value of end_date.
end_date
Accepts a month or date to filter the output date range. This attribute is used in conjunction with start_date.

The filter is applied just prior to the final tabulation so that g_functions may be fully evaluated before tabulating.

Note: The start_date and end_date must be used together. In addition, the value of end_date must be greater than the value of start_date.
filter
Accepts a comma-separated list of an arbitrary number of selection statements to be applied before the final tabulation.

An example is shown below:

<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
filter="state='NY',prod_type='10',orig_date>20100101"/>
If the selection criteria require the use of a comma then a different delimiter can be used, then specified, by placing a question mark (?) after the final selection criteria and then the new delimiting character in single quotes, as follows:
<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
filter="state='NY':orig_date>20100102:servicer='XYZ,NY'?':'"/>
filter_post_tab
Accepts a comma-separated list of an arbitrary number of selection statements to be applied before the after tabulation. An example is shown below:
<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
filter_post_tab="state='NY',prod_type='10',orig_date>20100101"/>

If the selection criteria require the use of a comma then a different delimiter can be specified using the same syntax as described above for the filter input:

<import path="pub.fin.eq.eg.lib_mbscalc_alpha" library=""/>
<insert block="lp_calc" calcs="cpr,cdr"
 filter_post_tab="state='NY':orig_date>20100102:servicer='XYZ,NY'?':'"/>

If the selection criteria require the use of a comma then a different delimiter can be used, then specified by placing a question mark (?) after the final selection criteria and then the new delimiting character in single quotes, as follows:

Linking inputs

hpi
Accepts a space-separated list of values representing the specific indices to be included in the analysis.

Mapping tables for all indices can be found in the following folder:

pub.fin.lpmisc
Values and table locations are as follows:
  • zillow - Zillow (Default) - pub.fin.misc_datasets.zillow.monthly_index
  • lp - Corelogic HPI - pub.fin.lpmisc.hpi
  • lp_reas - CoreLogic Real Estate Suite - pub.fin.corelogic.reas.hpi.hist
  • csw - Case Shiller Weiss - pub.fin.csw.monthly
  • csw_sa Case Shiller Weiss (Seasonally Adjusted)
  • ofheo - FHFA Housing Index - pub.fin.ofheo.mon

Zillow is available to all subscribers. Other data sets are dependent upon subscription. If access to an index is not available this link will fail and provide an error.

When working with the CoreLogic Securities data sets, 1010data provides an updated lookup for each index which provides the optimal indices for each loan based on geographic parameters. Using this index a monthly HPA is computed. The hpi input includes two additional inputs that apply specifically to the lp and ofheo input values. These additional inputs are as follows:
tier
The tier input is only available when hpi="lp". tier accepts an integer value between 1 and 12. Refer to the CoreLogic data documentation for definitions of tiers for each available value.
seas_adj
The seas_adj input is only available when hpi="ofheo". Accepts the value 1. When seas_adj="1", the calculation will use seasonally adjusted data.
unemp
Accepts 1 to link in BLS Unemployment table using the following columns: zipcode, cbsa, and state. Path to table: pub....
conforming
Accepts 1 to link in the Conforming Loan Limits table, then creates two computed columns: is_conforming, based on the original balance of the loan, and is_conforming_curr, based on the current balance of the loan. New columns are boolean, and can be used for custom analysis and logic before final tabulation.
n_expand and date_expand
Accepts an integer value (N) and uses a <sel expand="1"> operation to create N new rows. date_expand accepts an integer date value (YYYYMMDD). For each loan, the most recent record will be copied M months where M is the number of months between the last remittance date of the loan and the value given to date_expand.
default_logic
Accepts a text value that is a valid 1010data expression. This logical expression is used to determine whether a loan has defaulted. When inputting logic, single quotes (') must be escaped with a backslash (\). If no logic is passed to default_logic, the following logic is automatically used:
Note: The code below represents the logic used automatically when default_logic is not given a value. This code does not need to be passed to the default_logic input.
if((loss_amt_sum >100 | (has_loss = 'N' & ots_stat_sum = '6' '9' 'F' 'R')) 
&  yrmo(fdate)=yrmo(payoff_d_sum);inv_bal;0)
link_tables
Accepts a comma-separated list of table names for linking. The tables will be linked in the order they are provided in the list passed to lp_calc.
link_from_cols
Accepts a pipe-separated (|) list of comma-separated lists of column names. For example, if three base tables are specified to be linked from, the following list would link on two columns from each base table: link_from_cols="tab1_col1,tab1_col2|tab2_col1,tab2_col2|tab3_col1,tab3_col2".
link_to_cols
Accepts a pipe-separated (|) list of comma-separated lists of column names. I.e., if three foreign tables are specified to be linked to, the following list would link on two columns from each foreign table: link_from_cols="tab1_col1,tab1_col2|tab2_col1,tab2_col2|tab3_col1,tab3_col2".
link_suffixes
Accepts a comma-separated list of suffixes to be added to the new column names of the new worksheet created by the linking operation.
link_types
Accepts a comma-separated list of valid values for the type attribute of a <link> operation. See the documentation on <link> for a list of values.

Summarization inputs

breaks

Accepts a comma-separated list of columns names which will be used as the groups for the final tabulation. Defaults to the value specified in the time input if no other breaks are specified. When breaks are provided, value specified in the time input will be appended as an additional break column, unless the none value is provided to the time input.

time
Accepts a the name of a column that indicates the time order of the data. The default is fdate. Also accepts age or the name of a computed column created by a <willbe> operation. If no time-series data is desired for the final results, pass the text value none to the time input.
sources
Accepts a comma-separated list of column names that will be used as the data to aggregate/summarize by the group or groups specified in the breaks, cbreaks, and time inputs.
weights
Accepts a comma-separated list of column names that will be used to weight the results of the final tabulation. Used as the reference column for tabulation functions that require a weight (i.e., weighted average).
names
Accepts a comma-separated list of column names that will be used as the names of the columns in the final tabulation. The number of column names in the comma-separated list provided to names input must correspond to the number of column names provided to the sources input.
Note: This option does not apply to cross-tabulations.
functions
Accepts a comma-separated list of functions that will be used in the final tabulation. The number of functions in the comma-separated list provided to functions must correspond to the number of column names provided to the sources input.
cbreaks
Accepts a comma-separated list of columns names which will be used by the cross-tabulation operation to group columns calculated by the cross-tabulation.
csource
The name of a single column that will be used as the data to aggregate/summarize by the group or groups specified in the cbreaks input.
cweight
Accepts a single value that will be used to weight the results of the cross-tabulation component of the final tabulation.
cfunction
The function that will be applied to the cross-tabulation columns of the final tabulation.
sort
Accepts a comma-separated list of the values up and down. Each break column of the summarization will be sorted in the order corresponding to the values in the list passed to sort.

Calculation inputs (<willbe>)

willbes
Accepts a comma-separated list of <willbe> operations that specify the new column's name and a value expressions for the value of the computed columns. Columns are created before the final tabulation.

The syntax for the expression is:

[NEW_COL_NAME]:[EXPRESSION]

An example is shown below:

<import path="pub.fin.eq.eg.lib_mbscalc_alpha" library=""/>
<insert block="lp_calc" calcs="cpr,cdr" 
willbes="is_mod:"mode_rate<>NA,ever_mod:g_cumhi(loan_nid;;({@time};is_mod))"/>

The delimiter for the list of expressions can be changed in the event an expression needs in the list needs to use commas, as shown below:

<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
willbes="limit:100!range:range2(ltv;50,60,90,120)?'!'"/>
willbes_post_tab
Accepts a comma-separated list of <willbe> operations that specify the new column's name and a value expressions for the value of the computed columns. Columns are created after the final tabulation.

The syntax for the expression is:

[NEW_COL_NAME]:[EXPRESSION]

An example is shown below:

<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
willbes_post_tab="is_mod:
"mode_rate<>NA,ever_mod:g_cumhi(loan_nid;;({@time};is_mod))"/>

The delimiter for the list of expressions can be changed in the event an expression needs in the list needs to use commas, as shown below:

<import path="pub.lib.ucl.prod.mbscalc"/>
<insert block="lp_calc" calcs="cpr,cdr" 
willbes_post_tab="limit:100!range:range2(ltv;50,60,90,120)?'!'"/>

Spread calculations

spread_range
Accepts a comma-separated list that defines value ranges that will be used by the range2f function. The resultant column contains a Range for the current spread to PMMS rates. The library will calculate a spread for each loan to the regional PMMS rate. It then assigns this spread to one of the range buckets provided to spread_range.
spread_range_inc
Accepts a comma-separated list that defines value ranges that will be used by the range1f function. Accepts a numeric value. This value specifies a fixed increment to be used for the current spread range.
sato_range
Accepts a comma-separated list that defines value ranges that will be used by the range2f function. The resultant column contains a range for the spread to PMMS rates at origination. The library will calculate a spread for each loan to the regional PMMS rate. It then assigns this spread to one of the range buckets provided to satp_range.
sato_range_inc
Accepts a numeric value. This value specifies a fixed increment to be used for the current spread range.
sato_lookback
Accepts an integer specifying the number of months to look back from the origination date into PMMS Rates table. The default value is 2.