Cash flow analysis of mortgage pool with OAS (option adjusted spread) using Excel (securitization). This task is not for simple mortgage rate calculation. This task for cash flow analysis of mortgage...

1 answer below »
Cash flow analysis of mortgage pool with OAS (option adjusted spread) using Excel (securitization). This task is not for simple mortgage rate calculation. This task for cash flow analysis of mortgage pool. The task is to update my draft solution (my given excel file) to solve 5 questions using MS-Excel. The key is to build OAS (option adjusted spread) for mortgage cash flow analysis.


Microsoft PowerPoint - HW_LIB_CRP.pptx 5/2/2022 1 The given Excel sample file can be modified following the requirement below. Dear Tutor: Please give it up quickly and report it to the admin-team if you cannot do this task. The key is to build OAS (option adjusted spread) model using the given Excel file and equations here. There are 5 questions on page 4. Step 1 PSA is prepayment speed = CPR (Constant Prepayment Rate), WAC (Weighted Average Coupon), WALA (Weighted Average Loan Age, SMM (Single Monthly Mortality) The given Excel sample file can be modified following the requirement below Step 2 The given Excel sample file can be modified following the requirement below Step 3 The given Excel sample file can be modified following the requirement below Q1 Q2 Q3 Q4 Q5 Pool 100MM-PSA50 Draft Solution. Please update this file to solve questions. INPUTS Initial Principal$100,000,000 fixed rate3.500% Maturity (months)360 Prepayment or Curtail (Yes 1 / No 0)1 Prepayment (PSA 50)0.001 Service fee0.700% Pass through investors100 Monthly Payment$449,044.69 WAL (unit:year)13.833 NOMortgage poolMonthBegin PrincipalMonthly PaymentPrepayment or CurtailService FeeInterest PaymentScheduled Principal PaymentEnd PrincipalCumulative Interest PaidPayment for each investors 11000001$100,000,000$449,045$100,000$3,143$288,523$160,521$99,739,479$288,523$4,490$449,044.69 21000002$99,739,479$449,045$100,000$3,143$287,763$161,281$99,478,197$576,287$4,490$898,089.38 31000003$99,478,197$449,045$100,000$3,143$287,001$162,043$99,216,154$863,288$4,490$1,347,134.06 41000004$99,216,154$449,045$100,000$3,143$286,237$162,808$98,953,347$1,149,525$4,490$1,796,178.75 51000005$98,953,347$449,045$100,000$3,143$285,471$163,574$98,689,773$1,434,996$4,490$2,245,223.44 61000006$98,689,773$449,045$100,000$3,143$284,702$164,343$98,425,430$1,719,698$4,490$2,694,268.13 71000007$98,425,430$449,045$100,000$3,143$283,931$165,114$98,160,316$2,003,629$4,490$3,143,312.81 81000008$98,160,316$449,045$100,000$3,143$283,158$165,887$97,894,429$2,286,786$4,490$3,592,357.50 91000009$97,894,429$449,045$100,000$3,143$282,382$166,663$97,627,766$2,569,168$4,490$4,041,402.19 1010000010$97,627,766$449,045$100,000$3,143$281,604$167,440$97,360,326$2,850,773$4,490$4,490,446.88 1110000011$97,360,326$449,045$100,000$3,143$280,824$168,220$97,092,106$3,131,597$4,490$4,939,491.57 1210000012$97,092,106$449,045$100,000$3,143$280,042$169,003$96,823,103$3,411,639$4,490$5,388,536.25 1310000013$96,823,103$449,045$100,000$3,143$279,257$169,787$96,553,316$3,690,897$4,490$5,837,580.94Interim Calculation 1410000014$96,553,316$449,045$100,000$3,143$278,471$170,574$96,282,741$3,969,367$4,490$6,286,625.63 1510000015$96,282,741$449,045$100,000$3,143$277,681$171,363$96,011,378$4,247,048$4,490$6,735,670.32 1610000016$96,011,378$449,045$100,000$3,143$276,890$172,155$95,739,223$4,523,938$4,490$7,184,715.00 1710000017$95,739,223$449,045$100,000$3,143$276,096$172,949$95,466,275$4,800,034$4,490$7,633,759.69 1810000018$95,466,275$449,045$100,000$3,143$275,300$173,745$95,192,530$5,075,334$4,490$8,082,804.38 1910000019$95,192,530$449,045$100,000$3,143$274,502$174,543$94,917,987$5,349,836$4,490$8,531,849.07 2010000020$94,917,987$449,045$100,000$3,143$273,701$175,344$94,642,643$5,623,537$4,490$8,980,893.761449044.69449044.69449044.69449044.690.083 2110000021$94,642,643$449,045$100,000$3,143$272,898$176,147$94,366,496$5,896,434$4,490$9,429,938.442449044.69898089.38898089.381347134.060.125 2210000022$94,366,496$449,045$100,000$3,143$272,092$176,952$94,089,544$6,168,527$4,490$9,878,983.133449044.691347134.061347134.062694268.130.167 2310000023$94,089,544$449,045$100,000$3,143$271,285$177,760$93,811,783$6,439,811$4,490$10,328,027.824449044.691796178.751796178.754490446.880.208 2410000024$93,811,783$449,045$100,000$3,143$270,474$178,570$93,533,213$6,710,286$4,490$10,777,072.515449044.692245223.442245223.446735670.320.250 2510000025$93,533,213$449,045$100,000$3,143$269,662$179,383$93,253,830$6,979,948$4,490$11,226,117.206449044.692694268.132694268.139429938.440.292 2610000026$93,253,830$449,045$100,000$3,143$268,847$180,198$92,973,633$7,248,795$4,490$11,675,161.887449044.693143312.813143312.8112573251.260.333 2710000027$92,973,633$449,045$100,000$3,143$268,030$181,015$92,692,618$7,516,824$4,490$12,124,206.578449044.693592357.503592357.5016165608.760.375 2810000028$92,692,618$449,045$100,000$3,143$267,210$181,835$92,410,783$7,784,035$4,490$12,573,251.269449044.694041402.194041402.1920207010.950.417 2910000029$92,410,783$449,045$100,000$3,143$266,388$182,657$92,128,127$8,050,423$4,490$13,022,295.9510449044.694490446.884490446.8824697457.830.458 3010000030$92,128,127$449,045$100,000$3,143$265,564$183,481$91,844,646$8,315,986$4,490$13,471,340.6311449044.694939491.574939491.5729636949.400.500 3110000031$91,844,646$449,045$100,000$3,143$264,737$184,308$91,560,338$8,580,723$4,490$13,920,385.3212449044.695388536.255388536.2535025485.650.542 3210000032$91,560,338$449,045$100,000$3,143$263,908$185,137$91,275,201$8,844,631$4,490$14,369,430.0113449044.695837580.945837580.9440863066.590.583 3310000033$91,275,201$449,045$100,000$3,143$263,076$185,969$90,989,232$9,107,707$4,490$14,818,474.7014449044.696286625.636286625.6347149692.220.625 3410000034$90,989,232$449,045$100,000$3,143$262,242$186,803$90,702,430$9,369,949$4,490$15,267,519.3915449044.696735670.326735670.3253885362.540.667 3510000035$90,702,430$449,045$100,000$3,143$261,405$187,639$90,414,790$9,631,354$4,490$15,716,564.0716449044.697184715.007184715.0061070077.540.708 3610000036$90,414,790$449,045$100,000$3,143$260,566$188,478$90,126,312$9,891,921$4,490$16,165,608.7617449044.697633759.697633759.6968703837.230.750 3710000037$90,126,312$449,045$100,000$3,143$259,725$189,320$89,836,992$10,151,646$4,490$16,614,653.4518449044.698082804.388082804.3876786641.620.792 3810000038$89,836,992$449,045$100,000$3,143$258,881$190,163$89,546,829$10,410,527$4,490$17,063,698.1419449044.698531849.078531849.0785318490.680.833 3910000039$89,546,829$449,045$100,000$3,143$258,035$191,010$89,255,819$10,668,562$4,490$17,512,742.8220449044.698980893.768980893.7694299384.440.875 4010000040$89,255,819$449,045$100,000$3,143$257,186$191,859$88,963,961$10,925,748$4,490$17,961,787.5121449044.699429938.449429938.44103729322.880.917 4110000041$88,963,961$449,045$100,000$3,143$256,335$192,710$88,671,251$11,182,083$4,490$18,410,832.2022449044.699878983.139878983.13113608306.020.958 4210000042$88,671,251$449,045$100,000$3,143$255,481$193,564$88,377,687$11,437,564$4,490$18,859,876.8923449044.6910328027.8210328027.82123936333.841.000 4310000043$88,377,687$449,045$100,000$3,143$254,625$194,420$88,083,268$11,692,189$4,490$19,308,921.5824449044.6910777072.5110777072.51134713406.341.042 4410000044$88,083,268$449,045$100,000$3,143$253,766$195,278$87,787,989$11,945,956$4,490$19,757,966.2625449044.6911226117.2011226117.20145939523.541.083 4510000045$87,787,989$449,045$100,000$3,143$252,905$196,140$87,491,850$12,198,861$4,490$20,207,010.9526449044.6911675161.8811675161.88157614685.421.125 4610000046$87,491,850$449,045$100,000$3,143$252,041$197,003$87,294,846$12,450,902$4,490$20,656,055.6427449044.6912124206.5712124206.57169738891.991.167 4710000047$87,294,846$449,045$100,000$3,143$251,467$197,578$87,097,268$12,702,368$4,490$21,105,100.3328449044.6912573251.2612573251.26182312143.251.208 4810000048$87,097,268$449,045$100,000$3,143$250,890$198,154$86,899,114$12,953,259$4,490$21,554,145.0129449044.6913022295.9513022295.95195334439.201.250 4910000049$86,899,114$449,045$100,000$3,143$250,312$198,732$86,700,382$13,203,571$4,490$22,003,189.7030449044.6913471340.6313471340.63208805779.831.292 5010000050$86,700,382$449,045$100,000$3,143$249,733$199,312$86,501,070$13,453,304$4,490$22,452,234.3931449044.6913920385.3213920385.32222726165.151.333 5110000051$86,501,070$449,045$100,000$3,143$249,151$199,893$86,301,176$13,702,456$4,490$22,901,279.0832449044.6914369430.0114369430.01237095595.161.375 5210000052$86,301,176$449,045$100,000$3,143$248,568$200,476$86,100,700$13,951,024$4,490$23,350,323.7733449044.6914818474.7014818474.70251914069.861.417 5310000053$86,100,700$449,045$100,000$3,143$247,984$201,061$85,899,639$14,199,008$4,490$23,799,368.4534449044.6915267519.3915267519.39267181589.251.458 5410000054$85,899,639$449,045$100,000$3,143$247,397$201,647$85,697,992$14,446,405$4,490$24,248,413.1435449044.6915716564.0715716564.07282898153.321.500 5510000055$85,697,992$449,045$100,000$3,143$246,809$202,236$85,495,756$14,693,214$4,490$24,697,457.8336449044.6916165608.7616165608.76299063762.081.542 5610000056$85,495,756$449,045$100,000$3,143$246,219$202,825$85,292,931$14,939,433$4,490$25,146,502.5237449044.6916614653.4516614653.45315678415.531.583 5710000057$85,292,931$449,045$100,000$3,143$245,628$203,417$85,089,514$15,185,061$4,490$25,595,547.2138449044.6917063698.1417063698.14332742113.671.625 5810000058$85,089,514$449,045$100,000$3,143$245,034$204,010$84,885,504$15,430,096$4,490$26,044,591.8939449044.6917512742.8217512742.82350254856.491.667 5910000059$84,885,504$449,045$100,000$3,143$244,439$204,605$84,680,898$15,674,535$4,490$26,493,636.5840449044.6917961787.5117961787.51368216644.001.708 6010000060$84,680,898$449,045$100,000$3,143$243,843$205,202$84,475,696$15,918,378$4,490$26,942,681.2741449044.6918410832.2018410832.20386627476.201.750 6110000061$84,475,696$449,045$3,143$243,244$205,801$84,269,896$16,161,622$4,490$27,391,725.9642449044.6918859876.8918859876.89405487353.091.792 6210000062$84,269,896$449,045$3,143$242,644$206,401$84,063,495$16,404,266$4,490$27,840,770.6443449044.6919308921.5819308921.58424796274.671.833 6310000063$84,063,495$449,045$3,143$242,042$207,003$83,856,492$16,646,308$4,490$28,289,815.3344449044.6919757966.2619757966.26444554240.931.875 6410000064$83,856,492$449,045$3,143$241,438$207,607$83,648,886$16,887,746$4,490$28,738,860.0245449044.6920207010.9520207010.95464761251.881.917 6510000065$83,648,886$449,045$3,143$240,833$208,212$83,440,674$17,128,578$4,490$29,187,904.7146449044.6920656055.6420656055.64485417307.521.958 6610000066$83,440,674$449,045$3,143$240,225$208,819$83,231,854$17,368,804$4,490$29,636,949.4047449044.6921105100.3321105100.33506522407.852.000 6710000067$83,231,854$449,045$3,143$239,616$209,428$83,022,426$17,608,420$4,490$30,085,994.0848449044.6921554145.0121554145.01528076552.862.042 6810000068$83,022,426$449,045$3,143$239,005$210,039$82,812,387$17,847,425$4,490$30,535,038.7749449044.6922003189.7022003189.70550079742.572.083 6910000069$82,812,387$449,045$3,143$238,393$210,652$82,601,735$18,085,818$4,490$30,984,083.4650449044.6922452234.3922452234.39572531976.962.125 7010000070$82,601,735$449,045$3,143$237,778$211,266$82,390,468$18,323,597$4,490$31,433,128.1551449044.6922901279.0822901279.08595433256.032.167 7110000071$82,390,468$449,045$3,143$237,162$211,882$82,178,586$18,560,759$4,490$31,882,172.8352449044.6923350323.7723350323.77618783579.802.208 7210000072$82,178,586$449,045$3,143$236,544$212,500$81,966,085$18,797,303$4,490$32,331,217.5253449044.6923799368.4523799368.45642582948.252.250 7310000073$81,966,085$449,045$3,143$235,924$213,120$81,752,965$19,033,227$4,490$32,780,262.2154449044.6924248413.1424248413.14666831361.402.292 7410000074$81,752,965$449,045$3,143$235,303$213,742$81,539,223$19,268,530$4,490$33,229,306.9055449044.6924697457.8324697457.83691528819.232.333 7510000075$81,539,223$449,045$3,143$234,679$214,365$81,324,858$19,503,210$4,490$33,678,351.5956449044.6925146502.5225146502.52716675321.742.375 7610000076$81,324,858$449,045$3,143$234,054$214,990$81,109,868$19,737,264$4,490$34,127,396.2757449044.6925595547.2125595547.21742270868.952.417 7710000077$81,109,868$449,045$3,143$233,427$215,618$80,894,250$19,970,691$4,490$34,576,440.9658449044.6926044591.8926044591.89768315460.842.458 7810000078$80,894,250$449,045$3,143$232,798$216,246$80,678,004$20,203,489$4,490$35,025,485.6559449044.6926493636.5826493636.58794809097.422.500 7910000079$80,678,004$449,045$3,143$232,168$216,877$80,461,126$20,435,657$4,490$35,474,530.3460449044.6926942681.2726942681.27821751778.692.542 8010000080$80,461,126$449,045$3,143$231,535$217,510$80,243,617$20,667,192$4,490$35,923,575.0261449044.6927391725.9627391725.96849143504.652.583 8110000081$80,243,617$449,045$3,143$230,901$218,144$80,025,473$20,898,092$4,490$36,372,619.7162449044.6927840770.6427840770.64876984275.292.625 8210000082$80,025,473$449,045$3,143$230,264$218,780$79,806,692$21,128,357$4,490$36,821,664.4063449044.6928289815.3328289815.33905274090.622.667 8310000083$79,806,692$449,045$3,143$229,626$219,418$79,587,274$21,357,983$4,490$37,270,709.0964449044.6928738860.0228738860.02934012950.642.708 8410000084$79,587,274$449,045$3,143$228,986$220,058$79,367,215$21,586,969$4,490$37,719,753.7865449044.6929187904.7129187904.71963200855.352.750 8510000085$79,367,215$449,045$3,143$228,344$220,700$79,146,515$21,815,314$4,490$38,168,798.4666449044.6929636949.4029636949.40992837804.752.792 8610000086$79,146,515$449,045$3,143$227,701$221,344$78,925,171$22,043,014$4,490$38,617,843.1567449044.6930085994.0830085994.081022923798.832.833 8710000087$78,925,171$449,045$3,143$227,055$221,990$78,703,181$22,270,069$4,490$39,066,887.8468449044.6930535038.7730535038.771053458837.602.875 8810000088$78,703,181$449,045$3,143$226,408$222,637$78,480,544$22,496,477$4,490$39,515,932.5369449044.6930984083.4630984083.461084442921.062.917 8910000089$78,480,544$449,045$3,143$225,758$223,286$78,257,258$22,722,235$4,490$39,964,977.2170449044.6931433128.1531433128.151115876049.202.958 9010000090$78,257,258$449,045$3,143$225,107$223,938$78,033,320$22,947,342$4,490$40,414,021.9071449044.6931882172.8331882172.831147758222.043.000 9110000091$78,033,320$449,045$3,143$224,454$224,591$77,808,730$23,171,796$4,490$40,863,066.5972449044.6932331217.5232331217.521180089439.563.042 9210000092$77,808,730$449,045$3,143$223,799$225,246$77
hwlibcrpoas-tbahjmap.pdf draft-solution-lar41kod.xlsx sample-libor-forwards-and-cprs-embrt31a.xlsx
Answered 1 days AfterMay 06, 2022

Answer To: Cash flow analysis of mortgage pool with OAS (option adjusted spread) using Excel (securitization)....

Neha answered on May 07 2022
89 Votes
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Mortgage OAS Analysis
Harvey Stein
Head, Quantitative Finance R&D
Bloomberg LP
16th February 2006
OAS calculations, Monte Carlo, OAS server and Linux cluster work by
Alexander Belikoff, Kirill Levin, Harvey Stein, and Xusheng Tian,
Quantitative Finance R&D group, Bloomberg LP.
Prepayment modeling by Warren Xia and Sherman Liu, Prepayment
Modeling Group, Bloomberg LP.
OAS application by Sean Dai, Mortgage group, Bloomberg LP.
Thanks to Bruno Dupire and Liuren Wu, Quantitative Finance R&D group,
Bloomberg LP, for assistance.
Header: /usr/local/cvs/FIR/TREQ/mtge-2-factor/doc/Columbia/columbia-presentation.latex.tex,v 1.9 2006/02/16 19:53:04
hjstein Exp
1 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Outline
1 Customary market size comments
2 Mortgage market structure
3 Prepayment modeling
4 Yield and OAS
5 Data and calibration
6 Interest rate models
7 Index projection
8 Monte Carlo analysis
9 Greeks
10 Validation
11 Robust parallelization
12 Summary
2 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Size of mortgage market
• The Case for U.S. Mortgage-Backed Securities for Global
Investors, Michael Wands, CFA, Head of U.S. Fixed Income,
Global Fixed Income, State Street Global Advisors:
• Lehman U.S. Aggregate Index - 2002
• MBS — 35%
• U.S. Credit — 27%
• U.S. Treasury — 22%
• U.S. Agency — 12%
• ABS — 2%
• CMBS — 2%
• The U.S. Mortgage Market, Fannie Mae, and Freddie Mac — An
IMF Study:
• March 2003 — $3.2 trillion in mortgage-backed issuance by
Fannie and Freddie.
3 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Outstanding debt
4 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Size over time
5 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS issuance
6 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
CMO issuance
7 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Mortgage market structure:
• Mortgages
• MBS pools
• CMOs
8 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
The subatomic particles -
mortgages
People take out loans (mortgages) to buy homes.
• Fixed rate mortgages — fixed coupon, monthly payments, self
amortizing, paying principal down to zero at maturity (15-30
years).
• Balloons – amortize on a 30 year basis, but expire in 5 or 7 years
with payment of the remaining outstanding balance.
• Adjustable rate mortgages (ARMS) — floating coupon based on
an index (LIBOR, treasury rates, . . . ), typically with protection
clauses against overly large coupon changes (lifetime and
periodic caps, annual resets, . . . ).
Low interest rates in recent years have sparked innovation — floating
rate balloons with interest only payments, various sorts of built in
protections, option ARMs, . . .
9 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Mortgage behavior
While it looks like ARM valuation might require some work, in that
they have embedded caps and ratchets and potentially float on a
CMS rate, one might think that at least fixed rate mortgages would
be easily valued.
With fixed monthly payments, monthly interest payments at a rate of
C on the outstanding balance, N monthly payments and an initial
balance of B , then monthly payments are:
C (1 + C )NB
(1 + C )N − 1
.
10 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Mortgage cash flows
Graphically, our cash flows look like:
so why not just discount and be done?
11 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
The painful intervention of reality
The problem with discounting the scheduled payments is that
• People move,
• refinance,
• default,
• make excess payments to pay off principal faster.
So, principal arrives randomly, or perhaps not at all (in default of
uninsured loans).
Payments above the scheduled payments pay down the principal and
are known as prepayment.
One of the major components of mortgage analysis is in modeling
prepayment behavior.
12 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Impact of prepayment on cash
flows
Thus, our simple mortgage, instead of having fixed cash flows, has
cash flows that are dependent on prepayment rates. Under one
prepayment assumption, we have:
13 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Impact of prepayment on cash
flows
Under a model that projects prepayments based on interest rates and
loan characteristics, we see a different cash flow structure for each
rate path. One example is:
14 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
The atoms — MBS Pools
In 1938, the collapse of the national housing market led to the federal
government’s formation of the Federal National Mortgage
Association, AKA “Fannie Mae”. Now, we have Fannie Mae, Freddie
Mac (The Federal Home Loan Mortgage Corporation), and Ginnie
Mae The Government National Mortgage Association.
• Banks make mortgages.
• Government insures conforming mortgages.
• Agencies buy conforming mortgages.
• Banks have money to make more mortgages.
• Agencies sell shares of mortgages on secondary market.
Ginnie, Fannie and Freddie — three sets of rules for conforming loans.
15 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS secondary market - pools
The agencies (Fannie, Freddie and Ginnie) buy mortgages, pool them
together into MBS pools and sell shares. Pools are pass-through
securities, in that the cash flows from the underlying collateral is
passed through to the shareholder, minus a service fee.
16 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics -
Geographic data
These days, there’s substantial information available about pool
composition, such as geographic data:
17 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics -
Loan purpose
18 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics -
Loan to value ratio and credit
ratings
19 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics -
loan rate distribution
20 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
LTV distribution
21 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Loan size distribution
22 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Maturity distribution
23 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Age distribution
24 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Credit rating distribution
25 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Servicers
26 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS pool fine characteristics
Sellers
27 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
MBS secondary market - TBAs
Pools are also sold before being created, trading as TBAs:
28 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Pool analysis
Pool analysis is like mortgage analysis, except that it benefits from
safety in numbers.
• Backed by a substantial number of individual loans, so variance is
reduced.
While it used to be the case that this was at the cost of only knowing
gross aggregate data about the pool, these days the fine structure of
the pool is often disclosed as well, telling us:
• Location of individual loans,
• Size of individual loans,
• LTV,
• and credit ratings.
The only thing missing is individual borrower details.
29 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
The molecules — CMOs
In 1983, Solomon Brothers and First Boston created the first
Collateralized Mortgage Obligation (CMO).
They realized that more pools could be sold if the pool cash flows
were carved up to stratify risk.
CMOs are:
• Backed by pools or directly by mortgages (whole loans),
sometimes by as many as 20,000 of them.
• Split up cash flows of underlying collateral into a number of
“bonds” or “tranches”.
• By creating desirable risk structures, tranches can be sold to a
wider audience, and at a profit.
CMOs are essentially arbitrary structured notes backed by mortgage
collateral.
30 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Tranche types
Tranches vary by how the principal and interest are carved up.
• Interest handling:
• Fixed cpn — Can behave like a pool or very differently,
depending on how principal is paid.
• POs — Only principal payments from underlying collateral.
• IOs — Only interest payments.
• Floaters — Where there’s a floater, there’s an inverse floater
(when you have fixed rate collateral).
• Inverse floaters.
• Principal handling:
• Sequential Pay — Sequence of tranches. First gets principal until
paid, then 2nd gets principal, etc. Last one is most prepayment
protected and behaves most like an ordinary bond.
• PACs — Scheduled principal will be payed as long as prepayment
remains in a specified band.
• TACs — Scheduled principal will be payed when prepayment is at
a specified level.
• Etc.
31 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Example CMO
32 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Example CMO tranche
33 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
Example CMO collateral
34 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
CMO modeling
Computing the cash flows of a CMO tranche requires modeling the
CMO, i.e. — converting the prospectus into a mathematical
specification of the tranche payouts as a function of collateral cash
flows.
35 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index projection
Monte Carlo
analysis
Greeks
Validation
Robust
parallelization
Summary
CMO cash flow engine
The cash flow engine computes CMO cash flows for each scenario.
Inputs:
• CMO deal specification.
• Index projections.
• Current outstanding balance of each piece of collateral.
Given the above inputs, the cash flow engine parses and evaluates the
deal specification, using the cash flows generated from running the
prepayment model on each piece of collateral and amortizing it.
36 / 106
Mortgage OAS
Analysis
Harvey Stein
Customary
market size
comments
Mortgage
market structure
Prepayment
modeling
Yield and OAS
Data and
calibration
Interest rate
models
Index...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?