MBS examples#
This section presents examples of MBS (mortgage-backed securities) queries written in TenFrame.
To perform TenFrame operations on MBS data, first import the py1010, tenFrame, pandas and numpy libraries, and create a 1010data session:
import py1010
import tenFrame as tf
import pandas as pd
import numpy as np
session = py1010.Session("1010data URL", "USERNAME", "PASSWORD", py1010.POSSESS)
Example 1: Agency pools#
This example computes the number of Agency Pools for each agency/gse (FNMA/FHLMC/GNMA) that have loans in each state for each month. The results show the outstanding balance of those loans and the number of loans. For instance, in Sept of 2022, there were 106,117 Freddie Mac Pools that had at least one active loan in CA, there was an approximately $510 billion balance and 1.578 million loans.
geo = tf.TenFrame(session, "pub.fin.embs.combined.stnd_pool.geo")
geo = geo[(geo.rectype == "G") & (geo.value != "ZZ")]
geo = geo.merge("pub.fin.embs.combined.stnd_pool.sec", on="issueid")
geo = geo[geo.colltype == "LOAN"]
geo = geo.groupby(["value", "agency", "effdt"]).agg({"value": "cnt", "rpb": "sum", "loans": "sum"})
geo2 = geo[geo.rpb_sum == geo.groupby(["value", "agency"]).rpb_sum.max(adjoin=True)['rpb_sum_max']]
rv = geo2 = geo2[["value", "agency", "effdt", "value_cnt", "rpb_sum", "loans_sum"]]
geo2 = geo2.sort_values(by=["agency", "value", "effdt"], ascending=True)
If you are familiar with 1010data’s Macro Language, you can use the tenFrame.TenFrame.printprettyXML() method to print the TenFrame geo2 as 1010data Macro Language:
<?xml version="1.0" ?>
<macro>
<base table="pub.fin.embs.combined.stnd_pool.geo"/>
<sel value="(((rectype) = ('G')) & ((value) <> ('ZZ')))"/>
<link table2="pub.fin.embs.combined.stnd_pool.sec" col="issueid" type="exact"/>
<sel value="(colltype) = ('LOAN')"/>
<tabu breaks="value,agency,effdt">
<tcol name="value_cnt" source="value" fun="cnt"/>
<tcol name="rpb_sum" source="rpb" fun="sum"/>
<tcol name="loans_sum" source="loans" fun="sum"/>
</tabu>
<willbe value="g_hi((value,agency);();(rpb_sum))" name="rpb_sum_max"/>
<sel value="(rpb_sum) = (rpb_sum_max)"/>
<colord cols="value,agency,effdt,value_cnt,rpb_sum,loans_sum"/>
<sort cols="agency,value,effdt" naorder="last"/>
</macro>
Example 2: Historical cohort#
This example shows the historical cohort (product, origination year, coupon) prepayment speeds (CPR) for the largest 10 cohorts (determined by the largest monthly outstanding balance for each cohort).
Note
g_tshift() is a 1010data function that returns the value of the first row that is a specified time period before (or after) the current row within a given group.
fh = tf.TenFrame(session, "pub.fin.embs.combined.stnd_pool.fh")
fh = fh.merge("pub.fin.embs.combined.stnd_pool.sec", on="issueid")
fh = fh[fh.colltype == 'LOAN']
fh = fh.merge("pub.fin.embs.combined.stnd_pool.sc", on="secid")
fh = fh[fh["coupontype"] == 'FIX']
fh["balance"] = fh.issamt * fh.fctr
fh["wam2"] = tf.select([fh.product.str.endswith('BALL')],
[fh.wam + (fh.waolterm - fh.origwam)], fh.wam)
fh["n_schedbal"] = fh.balance * fh.wac.amort(fh.wam2, 1)
# Adding aggregated columns needs to be done via "adjoin"
fh = fh.groupby("issueid").effdt.tshift("M", fh.n_schedbal, -1, name="schedbal", adjoin=True)
fh = fh[fh.schedbal.notnull()]
fh = fh[fh.coupon - tf.func.int(fh.coupon) == 0.5]
# Note the use of columnparams below, in order to supply the "weight"
# parameter to the wavg (weighted average) function. When there are
# columnparams, any columns which don't have columnparams applied to them
# are moved to the end, so we have to add extra entries to columnparams for
# the first two columns to keep them in place.
fh2 = fh.groupby(["effdt", "coupon", "product", "productionyear"]).agg(
{"balance": "sum",
"schedbal": "sum",
"wac": "wavg",
"issueid": "cnt"},
columnparams=[
{"source":"balance", "fun":"sum"},
{"source":"schedbal", "fun":"sum"},
{"source":"wac", "fun":"wavg", "weight":"balance"}])
fh2["smm"] = 1 - fh2.balance_sum / fh2.schedbal_sum
fh2["cpr"] = tf.func.smm2cpr(100 * fh.smm)
fh2 = fh2.groupby(["coupon", "product", "productionyear"]).balance_sum.max(name="hibal", adjoin=True)
fh2 = fh2.hibal.rankuniq(name="rank", adjoin=True)
fh2 = fh2[fh2.rank < 10]
fh3 = fh2.groupby("effdt").agg({"cpr":"first", "balance_sum":"first"},
cbreaks=["coupon", "product", "productionyear"])
fh3 = fh3.sort_values("effdt", ascending=False)
fh3
Example 3: Cohort and S curve#
This example shows cohort (product, origination year, and coupon) and S curve (CPR by rate incentive) with 50 basis point incentive buckets.
fh = tf.TenFrame(session, "pub.fin.embs.combined.stnd_pool.fh")
fh = fh.merge("pub.fin.embs.combined.stnd_pool.sec", on="secid")
fh = fh.merge("pub.fin.embs.combined.stnd_pool.sc", on="secid")
fh['linkmonth'] = fh.effdt.shiftmonths(-1,0).yrmo()
fh = fh.merge("pub.fin.econ.rates.pmms.nm", on="linkmonth", right_on="month")
fh['origterm'] = fh.origwam + fh.origwala
fh['benchmark_rate'] = tf.select([fh.coupontype == 'ARM', fh.origterm <= 180],
[fh.r51, fh.r15], fh.r30)
fh["incentive"] = fh["wac"] - fh["benchmark_rate"]
roundto = 0.5
fh["rincentive"] = fh.incentive.round(0.5)
fh["origmonth"] = fh.effdt.shiftmonths(-fh.wala, 0)
fh['origmonth'].setasdate() # mark this column as a date.
fh["vintage"] = fh.origmonth.year()
fh["curbal"] = fh.issamt * fh.fctr
fh["amortfctr"] = fh.wac.amort(fh.wam, 1)
fh["schedbal"] = fh.curbal * fh.amortfctr
fh["smmdec"] = fh.cprm / 100
fh2 = fh[fh.colltype == 'LOAN']
fh3 = fh2.groupby(["product","coupon","productionyear","rincentive"]).agg(
{"smmdec" : "wavg", "curbal": "sum"},
breakelts={"col":"rincentive", "sort":"up"},
columnparams=[{"source":"smmdec", "fun":"wavg", "weight": "schedbal", "name": "smm"}])
fh3['cpr'] = 100 * (1 - (1 - fh3.smm)**12)
fh3 = fh3[fh3.cpr.notnull()]
limit = 500000000
fh3 = fh3[fh3.curbal_sum >= limit]
fh3 = fh3[["product", "coupon", "productionyear", "rincentive", "cpr"]]
fh3.sort_values("rincentive", inplace=True)
rv = fh3[fh3.rincentive.notnull()]
rv
Example 4: CPR by occupancy type#
This example shows prepayments (CPR) by occupancy type (owner/investor/second home) for FHLMC GOLD 30 Year 2012 Production 4% pools.
ldst = tf.TenFrame(session, "pub.fin.embs.combined.stnd_pool.ldst_orig")
ldst = ldst[ldst.rectype == 'O']
ldst = ldst[ldst.rpb > 0]
ldst = ldst.merge("pub.fin.embs.combined.stnd_pool.fh", on=["issueid", "effdt"])
ldst = tf.merge(ldst, "pub.fin.embs.combined.stnd_pool.sec", on="issueid")
ldst = ldst[(ldst.colltype == 'LOAN') & (ldst.product == 'FHLG30')]
ldst = ldst.merge("pub.fin.embs.combined.stnd_pool.sc", on="secid")
ldst = ldst[(ldst.coupon == 4) & (ldst.productionyear == 2012)]
ldst["nschedbal"] = ldst.rpb * ldst.wac.amort(ldst.wam, 1)
ldst = ldst.groupby(["issueid", "value1"]).effdt.tshift('M', ldst.nschedbal, -1, adjoin=True, name="schedbal")
ldst["prepaybal"] = ldst.schedbal.notnull() * ldst.rpb
ldst2 = ldst.groupby(["value1", "effdt"]).agg({"value1":"cnt", "schedbal":"sum", "prepaybal":"sum"})
ldst2["smm"] = 1 - ldst2.prepaybal_sum / ldst2.schedbal_sum
ldst2["cpr"] = tf.func.smm2cpr(100 * ldst2.smm)
rv = ldst3 = ldst2.groupby("effdt").agg({"cpr":"first"}, cbreaks="value1")
ldst3
Example 5: GNMA prepayment and buyout CPR#
This example shows loan level GNMA prepayment and buyout CPR and outstanding principal balance for GNMA 30 Year 4% coupon 2014 production year.
gll = tf.TenFrame(session, "pub.fin.embs.combined.loan.gll")
gll = gll[gll.updsrc == "GLLM"]
gll['rpb'] = tf.select([(gll.currmonliquidationflag == 'Y'), gll.currrpb.notnull(), gll.currrpb.isnull()],
[0, gll.currrpb, gll.origloanamt * gll.noterate.amort(gll.origterm, gll.loanage)])
gll = gll.merge("pub.fin.embs.combined.stnd_pool.sec", on="secmnem")
gll = gll.merge("pub.fin.embs.combined.stnd_pool.sc", on="secmnem")
gll = gll[(gll.product == "GNM30") & (gll.coupon == 4) & (gll.productionyear == 2014)]
gll['sbal'] = gll.rpb * gll.noterate.amort(gll.remterm, 1)
gll = gll.groupby(["secmnem", "loanseqnum"]).fctrdt.tshift("M", gll.sbal, -1, adjoin=True, name="schedbal")
gll = gll.groupby(["secmnem", "loanseqnum"]).fctrdt.tshift("M", gll.rpb, -1, adjoin=True, name="prev_rpb")
gll = gll.resetbreaks()
gll['prepays'] = tf.select([gll.currmonliquidationflag == "Y"], [gll.prev_rpb])
gll['buyouts'] = (gll.removalreasoncode == 2) * gll.currrpb
gll2 = gll.groupby("fctrdt").agg({"rpb":"sum", "schedbal":"sum", "prepays":"sum", "buyouts":"sum"},
breakelts={"col":"fctrdt", "sort":"up"})
gll2['smm'] = gll2.prepays_sum / gll2.schedbal_sum
gll2['cpr'] = 100 * (1 - (1 - gll2.smm)**12)
gll2['smm_buyout'] = gll2.buyouts_sum / gll2.schedbal_sum
gll2['cpr_buyout'] = 100 * (1 - (1 - gll2.smm_buyout)**12)
rv = gll2[['fctrdt', 'rpb_sum', 'cpr', 'cpr_buyout']]
rv
Example 6: Loan level FHLMC CPR#
This example displays loan level CPR. There is no 0 balance record supplied, so CPR is computed using a forward scheduled balance calculation compared with next months balance. If there is no record then a payoff has occured. The maximum factor date for the table is computed as to not consider empty records as a prepay.
ll = tf.TenFrame(session, "pub.fin.embs.combined.loan.ll")
ll = ll[ll.updsrc == 'FHML']
ll_2 = ll.agg({"fctrdt": ["cnt", "max"]})
ll = ll.merge(ll_2, on=1)
ll['schedbal'] = ll.currrpb * ll.orignoterate.amort(ll.remterm, 1)
ll = ll.groupby(["secmnem", "loanseqnum"]).fctrdt.tshift("M", ll.currrpb, 1, adjoin=True, name="nextbal_raw")
ll = ll[ll.fctrdt < ll.fctrdt_max]
ll['nextbal'] = tf.select([ll.nextbal_raw.notnull()], [ll.nextbal_raw], default=0)
ll2 = ll.groupby("fctrdt").agg({"fctrdt":"cnt", "nextbal":"sum", "schedbal":"sum"})
ll2['cpr'] = tf.func.smm2cpr(100 * (1 - ll.nextbal_sum / ll.schedbal_sum))
rv = ll2
rv
