Question: 1. Please make a spreadsheet that models the PSA prepayment curve we discussed in class + in the PPT + in the book. Months 0
1. Please make a spreadsheet that models the PSA prepayment curve we discussed in class + in the PPT + in the book. Months 0 to 360 in 1 column, CPR in the next column, and SMM in the third column.
(a) Add a parameter for X where X refers to X * PSA, e.g. 100PSA or 150PSA. Add the column which = SMM at X * PSA.
(b) Refer to the attached chart which is a figure showing a $400MM 7.5% pass-through with WAC of 8.125% and WAM of 357 months. Recreate this chart in Excel. Hint: SMM comes from the PSA assumption; in this chart it is 100PSA. Outstanding Balance = Last balance - Scheduled Prin - Prepay. Interest = WAC * Balance. Prepay = SMM * balance. Interest = Rate * Balance. etc.
Please refer to above image.
If you do this right, you should match the first 10 rows and last 7 rows when X = 100.
Remember that these 30-year loans are already 3 months seasoned if the WAM is 357 (i.e., we start in month 4).
Please show Formulas in excel Thanks!
Exhibit 11-3 Monthly Cash Flow for a $400 Million 7.5% Pass-Through Rate with a WAC of 8.125% and a WAM of 357 Months Assuming 100 PSA (1) (2) (3) (5) (6) (7) (8) (9) Outstanding Mortgage Scheduled Total Total Month Balance SMM Payment Interest Principal Prepayment Principal Cash Flow 1 400,000,000 0.00067 2,975,868 2,500,000 267,535 267,470 535,005 3,035,005 2 399,464,995 0.00084 2,973,877 2,496,656 269,166 334,198 603,364 3,100,020 3 398,861,631 0.00101 2,971,387 2,492,885 270,762 400,800 671,562 3,164,447 4 398,190,069 0.00117 2,968,399 2,488,688 272,321 467,243 739,564 3,228,252 5 397,450,505 0.00134 2,964,914 2,484,066 273,843 533,493 807,335 3,291,401 6 396,643,170 0.00151 2,960,931 2,479,020 275,327 599,514 874,841 3,353,860 7 395,768,329 0.00168 2,956,453 2,473,552 276,772 665,273 942,045 3,415,597 8 394,826,284 0.00185 2,951,480 2,467,664 278,177 730,736 1,008,913 3,476,577 9 393,817,371 0.00202 2,946,013 2,461,359 279,542 795,869 1,075,410 3,536,769 10 392,741,961 0.00219 2,940,056 2,454,637 280,865 860,637 1,141,502 3,596,140 350 0.00514 351 0.00514 352 0.00514 4,060,411 3,546,432 3,034,311 2,524,037 2,015,600 1,508,988 1,004,191 501,199 523,138 520,447 517,770 515,107 512,458 509,823 353 25,378 22,165 18,964 15,775 12,597 9,431 6,276 3,132 0.00514 0.00514 495,645 496,435 497,226 498,018 498,811 499,606 500,401 501,199 18,334 15,686 13,048 10,420 7,801 5,191 2,591 539,356 534,286 529,238 524,213 519,209 514,228 513,979 512,121 510,274 508,437 506,612 504,797 502,992 501,199 354 355 0.00514 356 0.00514 507,201 509,269 357 0.00514 504,592 0 504,331 Exhibit 11-3 Monthly Cash Flow for a $400 Million 7.5% Pass-Through Rate with a WAC of 8.125% and a WAM of 357 Months Assuming 100 PSA (1) (2) (3) (5) (6) (7) (8) (9) Outstanding Mortgage Scheduled Total Total Month Balance SMM Payment Interest Principal Prepayment Principal Cash Flow 1 400,000,000 0.00067 2,975,868 2,500,000 267,535 267,470 535,005 3,035,005 2 399,464,995 0.00084 2,973,877 2,496,656 269,166 334,198 603,364 3,100,020 3 398,861,631 0.00101 2,971,387 2,492,885 270,762 400,800 671,562 3,164,447 4 398,190,069 0.00117 2,968,399 2,488,688 272,321 467,243 739,564 3,228,252 5 397,450,505 0.00134 2,964,914 2,484,066 273,843 533,493 807,335 3,291,401 6 396,643,170 0.00151 2,960,931 2,479,020 275,327 599,514 874,841 3,353,860 7 395,768,329 0.00168 2,956,453 2,473,552 276,772 665,273 942,045 3,415,597 8 394,826,284 0.00185 2,951,480 2,467,664 278,177 730,736 1,008,913 3,476,577 9 393,817,371 0.00202 2,946,013 2,461,359 279,542 795,869 1,075,410 3,536,769 10 392,741,961 0.00219 2,940,056 2,454,637 280,865 860,637 1,141,502 3,596,140 350 0.00514 351 0.00514 352 0.00514 4,060,411 3,546,432 3,034,311 2,524,037 2,015,600 1,508,988 1,004,191 501,199 523,138 520,447 517,770 515,107 512,458 509,823 353 25,378 22,165 18,964 15,775 12,597 9,431 6,276 3,132 0.00514 0.00514 495,645 496,435 497,226 498,018 498,811 499,606 500,401 501,199 18,334 15,686 13,048 10,420 7,801 5,191 2,591 539,356 534,286 529,238 524,213 519,209 514,228 513,979 512,121 510,274 508,437 506,612 504,797 502,992 501,199 354 355 0.00514 356 0.00514 507,201 509,269 357 0.00514 504,592 0 504,331
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help