Microsoft PowerPoint - HW_LIB_CRP.pptx5/2/20221The given Excel sample file can be modified following...

Question

Microsoft PowerPoint - HW_LIB_CRP.pptx5/2/20221The 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 1PSA 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 belowStep 2The given Excel sample file can be modified following the requirement belowStep 3The given Excel sample file can be modified following the requirement belowQ1Q2Q3Q4Q5 Pool 100MM-PSA50                                            Draft Solution. Please update this file to solve questions.                                                    INPUTS                                            Initial Principal        $100,000,000                                            fixed rate        3.500%                                            Maturity (months)        360                                            Prepayment or Curtail (Yes 1 / No 0)        1                                            Prepayment (PSA 50)        0.001                                            Service fee        0.700%                                            Pass through investors        100                                            Monthly Payment        $449,044.69                                            WAL (unit:year)        13.833        NO    Mortgage pool                                Month    Begin Principal    Monthly Payment    Prepayment or Curtail    Service Fee    InterestPayment    ScheduledPrincipalPayment    EndPrincipal    CumulativeInterestPaid    Payment for each investors        1    100000                                1    $100,000,000    $449,045    $100,000    $3,143    $288,523    $160,521    $99,739,479    $288,523    $4,490        $449,044.69             2    100000                                2    $99,739,479    $449,045    $100,000    $3,143    $287,763    $161,281    $99,478,197    $576,287    $4,490        $898,089.38        3    100000                                3    $99,478,197    $449,045    $100,000    $3,143    $287,001    $162,043    $99,216,154    $863,288    $4,490        $1,347,134.06        4    100000                                4    $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        5    100000                                5    $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        6    100000                                6    $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        7    100000                                7    $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        8    100000                                8    $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        9    100000                                9    $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        10    100000                                10    $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        11    100000                                11    $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        12    100000                                12    $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        13    100000                                13    $96,823,103    $449,045    $100,000    $3,143    $279,257    $169,787    $96,553,316    $3,690,897    $4,490        $5,837,580.94                                                                                    Interim Calculation        14    100000                                14    $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        15    100000                                15    $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        16    100000                                16    $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        17    100000                                17    $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        18    100000                                18    $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        19    100000                                19    $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        20    100000                                20    $94,917,987    $449,045    $100,000    $3,143    $273,701    $175,344    $94,642,643    $5,623,537    $4,490        $8,980,893.76                                                                                    1     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.083        21    100000                                21    $94,642,643    $449,045    $100,000    $3,143    $272,898    $176,147    $94,366,496    $5,896,434    $4,490        $9,429,938.44                                                                                    2     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.125        22    100000                                22    $94,366,496    $449,045    $100,000    $3,143    $272,092    $176,952    $94,089,544    $6,168,527    $4,490        $9,878,983.13                                                                                    3     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.167        23    100000                                23    $94,089,544    $449,045    $100,000    $3,143    $271,285    $177,760    $93,811,783    $6,439,811    $4,490        $10,328,027.82                                                                                    4     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.208        24    100000                                24    $93,811,783    $449,045    $100,000    $3,143    $270,474    $178,570    $93,533,213    $6,710,286    $4,490        $10,777,072.51                                                                                    5     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.250        25    100000                                25    $93,533,213    $449,045    $100,000    $3,143    $269,662    $179,383    $93,253,830    $6,979,948    $4,490        $11,226,117.20                                                                                    6     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.292        26    100000                                26    $93,253,830    $449,045    $100,000    $3,143    $268,847    $180,198    $92,973,633    $7,248,795    $4,490        $11,675,161.88                                                                                    7     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.333        27    100000                                27    $92,973,633    $449,045    $100,000    $3,143    $268,030    $181,015    $92,692,618    $7,516,824    $4,490        $12,124,206.57                                                                                    8     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.375        28    100000                                28    $92,692,618    $449,045    $100,000    $3,143    $267,210    $181,835    $92,410,783    $7,784,035    $4,490        $12,573,251.26                                                                                    9     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.417        29    100000                                29    $92,410,783    $449,045    $100,000    $3,143    $266,388    $182,657    $92,128,127    $8,050,423    $4,490        $13,022,295.95                                                                                    10     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.458        30    100000                                30    $92,128,127    $449,045    $100,000    $3,143    $265,564    $183,481    $91,844,646    $8,315,986    $4,490        $13,471,340.63                                                                                    11     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.500        31    100000                                31    $91,844,646    $449,045    $100,000    $3,143    $264,737    $184,308    $91,560,338    $8,580,723    $4,490        $13,920,385.32                                                                                    12     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.542        32    100000                                32    $91,560,338    $449,045    $100,000    $3,143    $263,908    $185,137    $91,275,201    $8,844,631    $4,490        $14,369,430.01                                                                                    13     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.583        33    100000                                33    $91,275,201    $449,045    $100,000    $3,143    $263,076    $185,969    $90,989,232    $9,107,707    $4,490        $14,818,474.70                                                                                    14     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.625        34    100000                                34    $90,989,232    $449,045    $100,000    $3,143    $262,242    $186,803    $90,702,430    $9,369,949    $4,490        $15,267,519.39                                                                                    15     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.667        35    100000                                35    $90,702,430    $449,045    $100,000    $3,143    $261,405    $187,639    $90,414,790    $9,631,354    $4,490        $15,716,564.07                                                                                    16     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.708        36    100000                                36    $90,414,790    $449,045    $100,000    $3,143    $260,566    $188,478    $90,126,312    $9,891,921    $4,490        $16,165,608.76                                                                                    17     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.750        37    100000                                37    $90,126,312    $449,045    $100,000    $3,143    $259,725    $189,320    $89,836,992    $10,151,646    $4,490        $16,614,653.45                                                                                    18     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.792        38    100000                                38    $89,836,992    $449,045    $100,000    $3,143    $258,881    $190,163    $89,546,829    $10,410,527    $4,490        $17,063,698.14                                                                                    19     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.833        39    100000                                39    $89,546,829    $449,045    $100,000    $3,143    $258,035    $191,010    $89,255,819    $10,668,562    $4,490        $17,512,742.82                                                                                    20     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.875        40    100000                                40    $89,255,819    $449,045    $100,000    $3,143    $257,186    $191,859    $88,963,961    $10,925,748    $4,490        $17,961,787.51                                                                                    21     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.917        41    100000                                41    $88,963,961    $449,045    $100,000    $3,143    $256,335    $192,710    $88,671,251    $11,182,083    $4,490        $18,410,832.20                                                                                    22     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    0.958        42    100000                                42    $88,671,251    $449,045    $100,000    $3,143    $255,481    $193,564    $88,377,687    $11,437,564    $4,490        $18,859,876.89                                                                                    23     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.000        43    100000                                43    $88,377,687    $449,045    $100,000    $3,143    $254,625    $194,420    $88,083,268    $11,692,189    $4,490        $19,308,921.58                                                                                    24     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.042        44    100000                                44    $88,083,268    $449,045    $100,000    $3,143    $253,766    $195,278    $87,787,989    $11,945,956    $4,490        $19,757,966.26                                                                                    25     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.083        45    100000                                45    $87,787,989    $449,045    $100,000    $3,143    $252,905    $196,140    $87,491,850    $12,198,861    $4,490        $20,207,010.95                                                                                    26     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.125        46    100000                                46    $87,491,850    $449,045    $100,000    $3,143    $252,041    $197,003    $87,294,846    $12,450,902    $4,490        $20,656,055.64                                                                                    27     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.167        47    100000                                47    $87,294,846    $449,045    $100,000    $3,143    $251,467    $197,578    $87,097,268    $12,702,368    $4,490        $21,105,100.33                                                                                    28     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.208        48    100000                                48    $87,097,268    $449,045    $100,000    $3,143    $250,890    $198,154    $86,899,114    $12,953,259    $4,490        $21,554,145.01                                                                                    29     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.250        49    100000                                49    $86,899,114    $449,045    $100,000    $3,143    $250,312    $198,732    $86,700,382    $13,203,571    $4,490        $22,003,189.70                                                                                    30     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.292        50    100000                                50    $86,700,382    $449,045    $100,000    $3,143    $249,733    $199,312    $86,501,070    $13,453,304    $4,490        $22,452,234.39                                                                                    31     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.333        51    100000                                51    $86,501,070    $449,045    $100,000    $3,143    $249,151    $199,893    $86,301,176    $13,702,456    $4,490        $22,901,279.08                                                                                    32     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.375        52    100000                                52    $86,301,176    $449,045    $100,000    $3,143    $248,568    $200,476    $86,100,700    $13,951,024    $4,490        $23,350,323.77                                                                                    33     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.417        53    100000                                53    $86,100,700    $449,045    $100,000    $3,143    $247,984    $201,061    $85,899,639    $14,199,008    $4,490        $23,799,368.45                                                                                    34     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.458        54    100000                                54    $85,899,639    $449,045    $100,000    $3,143    $247,397    $201,647    $85,697,992    $14,446,405    $4,490        $24,248,413.14                                                                                    35     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.500        55    100000                                55    $85,697,992    $449,045    $100,000    $3,143    $246,809    $202,236    $85,495,756    $14,693,214    $4,490        $24,697,457.83                                                                                    36     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.542        56    100000                                56    $85,495,756    $449,045    $100,000    $3,143    $246,219    $202,825    $85,292,931    $14,939,433    $4,490        $25,146,502.52                                                                                    37     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.583        57    100000                                57    $85,292,931    $449,045    $100,000    $3,143    $245,628    $203,417    $85,089,514    $15,185,061    $4,490        $25,595,547.21                                                                                    38     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.625        58    100000                                58    $85,089,514    $449,045    $100,000    $3,143    $245,034    $204,010    $84,885,504    $15,430,096    $4,490        $26,044,591.89                                                                                    39     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.667        59    100000                                59    $84,885,504    $449,045    $100,000    $3,143    $244,439    $204,605    $84,680,898    $15,674,535    $4,490        $26,493,636.58                                                                                    40     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.708        60    100000                                60    $84,680,898    $449,045    $100,000    $3,143    $243,843    $205,202    $84,475,696    $15,918,378    $4,490        $26,942,681.27                                                                                    41     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.750        61    100000                                61    $84,475,696    $449,045        $3,143    $243,244    $205,801    $84,269,896    $16,161,622    $4,490        $27,391,725.96                                                                                    42     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.792        62    100000                                62    $84,269,896    $449,045        $3,143    $242,644    $206,401    $84,063,495    $16,404,266    $4,490        $27,840,770.64                                                                                    43     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.833        63    100000                                63    $84,063,495    $449,045        $3,143    $242,042    $207,003    $83,856,492    $16,646,308    $4,490        $28,289,815.33                                                                                    44     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.875        64    100000                                64    $83,856,492    $449,045        $3,143    $241,438    $207,607    $83,648,886    $16,887,746    $4,490        $28,738,860.02                                                                                    45     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.917        65    100000                                65    $83,648,886    $449,045        $3,143    $240,833    $208,212    $83,440,674    $17,128,578    $4,490        $29,187,904.71                                                                                    46     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    1.958        66    100000                                66    $83,440,674    $449,045        $3,143    $240,225    $208,819    $83,231,854    $17,368,804    $4,490        $29,636,949.40                                                                                    47     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.000        67    100000                                67    $83,231,854    $449,045        $3,143    $239,616    $209,428    $83,022,426    $17,608,420    $4,490        $30,085,994.08                                                                                    48     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.042        68    100000                                68    $83,022,426    $449,045        $3,143    $239,005    $210,039    $82,812,387    $17,847,425    $4,490        $30,535,038.77                                                                                    49     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.083        69    100000                                69    $82,812,387    $449,045        $3,143    $238,393    $210,652    $82,601,735    $18,085,818    $4,490        $30,984,083.46                                                                                    50     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.125        70    100000                                70    $82,601,735    $449,045        $3,143    $237,778    $211,266    $82,390,468    $18,323,597    $4,490        $31,433,128.15                                                                                    51     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.167        71    100000                                71    $82,390,468    $449,045        $3,143    $237,162    $211,882    $82,178,586    $18,560,759    $4,490        $31,882,172.83                                                                                    52     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.208        72    100000                                72    $82,178,586    $449,045        $3,143    $236,544    $212,500    $81,966,085    $18,797,303    $4,490        $32,331,217.52                                                                                    53     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.250        73    100000                                73    $81,966,085    $449,045        $3,143    $235,924    $213,120    $81,752,965    $19,033,227    $4,490        $32,780,262.21                                                                                    54     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.292        74    100000                                74    $81,752,965    $449,045        $3,143    $235,303    $213,742    $81,539,223    $19,268,530    $4,490        $33,229,306.90                                                                                    55     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.333        75    100000                                75    $81,539,223    $449,045        $3,143    $234,679    $214,365    $81,324,858    $19,503,210    $4,490        $33,678,351.59                                                                                    56     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.375        76    100000                                76    $81,324,858    $449,045        $3,143    $234,054    $214,990    $81,109,868    $19,737,264    $4,490        $34,127,396.27                                                                                    57     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.417        77    100000                                77    $81,109,868    $449,045        $3,143    $233,427    $215,618    $80,894,250    $19,970,691    $4,490        $34,576,440.96                                                                                    58     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.458        78    100000                                78    $80,894,250    $449,045        $3,143    $232,798    $216,246    $80,678,004    $20,203,489    $4,490        $35,025,485.65                                                                                    59     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.500        79    100000                                79    $80,678,004    $449,045        $3,143    $232,168    $216,877    $80,461,126    $20,435,657    $4,490        $35,474,530.34                                                                                    60     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.542        80    100000                                80    $80,461,126    $449,045        $3,143    $231,535    $217,510    $80,243,617    $20,667,192    $4,490        $35,923,575.02                                                                                    61     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.583        81    100000                                81    $80,243,617    $449,045        $3,143    $230,901    $218,144    $80,025,473    $20,898,092    $4,490        $36,372,619.71                                                                                    62     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.625        82    100000                                82    $80,025,473    $449,045        $3,143    $230,264    $218,780    $79,806,692    $21,128,357    $4,490        $36,821,664.40                                                                                    63     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.667        83    100000                                83    $79,806,692    $449,045        $3,143    $229,626    $219,418    $79,587,274    $21,357,983    $4,490        $37,270,709.09                                                                                    64     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.708        84    100000                                84    $79,587,274    $449,045        $3,143    $228,986    $220,058    $79,367,215    $21,586,969    $4,490        $37,719,753.78                                                                                    65     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.750        85    100000                                85    $79,367,215    $449,045        $3,143    $228,344    $220,700    $79,146,515    $21,815,314    $4,490        $38,168,798.46                                                                                    66     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.792        86    100000                                86    $79,146,515    $449,045        $3,143    $227,701    $221,344    $78,925,171    $22,043,014    $4,490        $38,617,843.15                                                                                    67     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.833        87    100000                                87    $78,925,171    $449,045        $3,143    $227,055    $221,990    $78,703,181    $22,270,069    $4,490        $39,066,887.84                                                                                    68     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.875        88    100000                                88    $78,703,181    $449,045        $3,143    $226,408    $222,637    $78,480,544    $22,496,477    $4,490        $39,515,932.53                                                                                    69     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.917        89    100000                                89    $78,480,544    $449,045        $3,143    $225,758    $223,286    $78,257,258    $22,722,235    $4,490        $39,964,977.21                                                                                    70     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    2.958        90    100000                                90    $78,257,258    $449,045        $3,143    $225,107    $223,938    $78,033,320    $22,947,342    $4,490        $40,414,021.90                                                                                    71     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    3.000        91    100000                                91    $78,033,320    $449,045        $3,143    $224,454    $224,591    $77,808,730    $23,171,796    $4,490        $40,863,066.59                                                                                    72     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX     XXXXXXXXXX    3.042        92    100000                                92    $77,808,730    $449,045        $3,143    $223,799    $225,246    $77

Neha · Accepted Answer

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.

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...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment