Cash flow analysis of mortgage pool securitization using Excel (securitization). This task is not for simple mortgage rate calculation. This task is for cash flow analysis of mortgage pool. The task is to update my draft solution (my given excel file) to solve 2 questions using MS-Excel.
Pool 100MM-Default0-0-PSA0 INPUTS Initial Principal$100,000,000 fixed rate6.000% Maturity (months)360 Prepayment or Curtail (Yes 1 / No 0)0 Prepayment (PSA 100)0.000 Service fee0.700% Asset Default Rate (Annual)0.00 Monthly Payment$599,550.53 WAL (unit:year)14.792Class AClass BClass C NOMortgage poolMonthBegin PrincipalMonthly PaymentPrepayment or CurtailService FeeInterest PaymentScheduled Principal PaymentEnd PrincipalCumulative Interest Paid750000001500000010000000 11000001$100,000,000$599,551$0$4,197$495,803$103,747$99,896,253$495,803$250,000$100,000$249,551$599,550.53 21000002$99,896,253$599,551$0$4,197$495,284$104,266$99,791,987$991,088$250,000$100,000$249,551$1,199,101.05 31000003$99,791,987$599,551$0$4,197$494,763$104,787$99,687,199$1,485,851$250,000$100,000$249,551$1,798,651.58 41000004$99,687,199$599,551$0$4,197$494,239$105,311$99,581,888$1,980,090$250,000$100,000$249,551$2,398,202.10 51000005$99,581,888$599,551$0$4,197$493,713$105,838$99,476,050$2,473,802$250,000$100,000$249,551$2,997,752.63 61000006$99,476,050$599,551$0$4,197$493,183$106,367$99,369,683$2,966,986$250,000$100,000$249,551$3,597,303.15 71000007$99,369,683$599,551$0$4,197$492,652$106,899$99,262,784$3,459,637$250,000$100,000$249,551$4,196,853.68 81000008$99,262,784$599,551$0$4,197$492,117$107,433$99,155,350$3,951,754$250,000$100,000$249,551$4,796,404.20 91000009$99,155,350$599,551$0$4,197$491,580$107,971$99,047,380$4,443,334$250,000$100,000$249,551$5,395,954.73 1010000010$99,047,380$599,551$0$4,197$491,040$108,510$98,938,869$4,934,374$250,000$100,000$249,551$5,995,505.25 1110000011$98,938,869$599,551$0$4,197$490,497$109,053$98,829,816$5,424,872$250,000$100,000$249,551$6,595,055.78 1210000012$98,829,816$599,551$0$4,197$489,952$109,598$98,720,218$5,914,824$250,000$100,000$249,551$7,194,606.30 1310000013$98,720,218$599,551$0$4,197$489,404$110,146$98,610,071$6,404,228$250,000$100,000$249,551$7,794,156.83Interim Calculation 1410000014$98,610,071$599,551$0$4,197$488,854$110,697$98,499,374$6,893,082$250,000$100,000$249,551$8,393,707.35 1510000015$98,499,374$599,551$0$4,197$488,300$111,251$98,388,124$7,381,382$250,000$100,000$249,551$8,993,257.88 1610000016$98,388,124$599,551$0$4,197$487,744$111,807$98,276,317$7,869,126$250,000$100,000$249,551$9,592,808.40 1710000017$98,276,317$599,551$0$4,197$487,185$112,366$98,163,951$8,356,310$250,000$100,000$249,551$10,192,358.93 1810000018$98,163,951$599,551$0$4,197$486,623$112,928$98,051,024$8,842,933$250,000$100,000$249,551$10,791,909.45 1910000019$98,051,024$599,551$0$4,197$486,058$113,492$97,937,531$9,328,991$250,000$100,000$249,551$11,391,459.98 2010000020$97,937,531$599,551$0$4,197$485,491$114,060$97,823,472$9,814,482$250,000$100,000$249,551$11,991,010.501599550.53599550.53599550.53599550.530.083 2110000021$97,823,472$599,551$0$4,197$484,921$114,630$97,708,842$10,299,403$250,000$100,000$249,551$12,590,561.032599550.531199101.051199101.051798651.580.125 2210000022$97,708,842$599,551$0$4,197$484,347$115,203$97,593,639$10,783,750$250,000$100,000$249,551$13,190,111.553599550.531798651.581798651.583597303.150.167 2310000023$97,593,639$599,551$0$4,197$483,771$115,779$97,477,859$11,267,521$250,000$100,000$249,551$13,789,662.084599550.532398202.102398202.105995505.250.208 2410000024$97,477,859$599,551$0$4,197$483,192$116,358$97,361,501$11,750,714$250,000$100,000$249,551$14,389,212.605599550.532997752.632997752.638993257.880.250 2510000025$97,361,501$599,551$0$4,197$482,611$116,940$97,244,561$12,233,325$250,000$100,000$249,551$14,988,763.136599550.533597303.153597303.1512590561.030.292 2610000026$97,244,561$599,551$0$4,197$482,026$117,525$97,127,037$12,715,351$250,000$100,000$249,551$15,588,313.657599550.534196853.684196853.6816787414.700.333 2710000027$97,127,037$599,551$0$4,197$481,438$118,112$97,008,925$13,196,789$250,000$100,000$249,551$16,187,864.188599550.534796404.204796404.2021583818.910.375 2810000028$97,008,925$599,551$0$4,197$480,848$118,703$96,890,222$13,677,637$250,000$100,000$249,551$16,787,414.709599550.535395954.735395954.7326979773.630.417 2910000029$96,890,222$599,551$0$4,197$480,254$119,296$96,770,926$14,157,891$250,000$100,000$249,551$17,386,965.2310599550.535995505.255995505.2532975278.880.458 3010000030$96,770,926$599,551$0$4,197$479,658$119,893$96,651,033$14,637,549$250,000$100,000$249,551$17,986,515.7511599550.536595055.786595055.7839570334.660.500 3110000031$96,651,033$599,551$0$4,197$479,058$120,492$96,530,541$15,116,607$250,000$100,000$249,551$18,586,066.2812599550.537194606.307194606.3046764940.960.542 3210000032$96,530,541$599,551$0$4,197$478,456$121,095$96,409,446$15,595,063$250,000$100,000$249,551$19,185,616.8013599550.537794156.837794156.8354559097.790.583 3310000033$96,409,446$599,551$0$4,197$477,850$121,700$96,287,746$16,072,913$250,000$100,000$249,551$19,785,167.3314599550.538393707.358393707.3562952805.140.625 3410000034$96,287,746$599,551$0$4,197$477,242$122,309$96,165,437$16,550,155$250,000$100,000$249,551$20,384,717.8615599550.538993257.888993257.8871946063.020.667 3510000035$96,165,437$599,551$0$4,197$476,630$122,920$96,042,517$17,026,785$250,000$100,000$249,551$20,984,268.3816599550.539592808.409592808.4081538871.420.708 3610000036$96,042,517$599,551$0$4,197$476,016$123,535$95,918,982$17,502,801$250,000$100,000$249,551$21,583,818.9117599550.5310192358.9310192358.9391731230.350.750 3710000037$95,918,982$599,551$0$4,197$475,398$124,152$95,794,830$17,978,199$250,000$100,000$249,551$22,183,369.4318599550.5310791909.4510791909.45102523139.800.792 3810000038$95,794,830$599,551$0$4,197$474,777$124,773$95,670,057$18,452,976$250,000$100,000$249,551$22,782,919.9619599550.5311391459.9811391459.98113914599.780.833 3910000039$95,670,057$599,551$0$4,197$474,153$125,397$95,544,659$18,927,130$250,000$100,000$249,551$23,382,470.4820599550.5311991010.5011991010.50125905610.280.875 4010000040$95,544,659$599,551$0$4,197$473,526$126,024$95,418,635$19,400,656$250,000$100,000$249,551$23,982,021.0121599550.5312590561.0312590561.03138496171.310.917 4110000041$95,418,635$599,551$0$4,197$472,896$126,654$95,291,981$19,873,553$250,000$100,000$249,551$24,581,571.5322599550.5313190111.5513190111.55151686282.860.958 4210000042$95,291,981$599,551$0$4,197$472,263$127,287$95,164,694$20,345,816$250,000$100,000$249,551$25,181,122.0623599550.5313789662.0813789662.08165475944.941.000 4310000043$95,164,694$599,551$0$4,197$471,627$127,924$95,036,770$20,817,442$250,000$100,000$249,551$25,780,672.5824599550.5314389212.6014389212.60179865157.551.042 4410000044$95,036,770$599,551$0$4,197$470,987$128,564$94,908,206$21,288,429$250,000$100,000$249,551$26,380,223.1125599550.5314988763.1314988763.13194853920.671.083 4510000045$94,908,206$599,551$0$4,197$470,344$129,206$94,779,000$21,758,774$250,000$100,000$249,551$26,979,773.6326599550.5315588313.6515588313.65210442234.331.125 4610000046$94,779,000$599,551$0$4,197$469,698$129,852$94,649,147$22,228,472$250,000$100,000$249,551$27,579,324.1627599550.5316187864.1816187864.18226630098.511.167 4710000047$94,649,147$599,551$0$4,197$469,049$130,502$94,518,646$22,697,521$250,000$100,000$249,551$28,178,874.6828599550.5316787414.7016787414.70243417513.211.208 4810000048$94,518,646$599,551$0$4,197$468,396$131,154$94,387,492$23,165,917$250,000$100,000$249,551$28,778,425.2129599550.5317386965.2317386965.23260804478.441.250 4910000049$94,387,492$599,551$0$4,197$467,741$131,810$94,255,682$23,633,658$250,000$100,000$249,551$29,377,975.7330599550.5317986515.7517986515.75278790994.201.292 5010000050$94,255,682$599,551$0$4,197$467,082$132,469$94,123,213$24,100,739$250,000$100,000$249,551$29,977,526.2631599550.5318586066.2818586066.28297377060.481.333 5110000051$94,123,213$599,551$0$4,197$466,419$133,131$93,990,082$24,567,158$250,000$100,000$249,551$30,577,076.7832599550.5319185616.8019185616.80316562677.281.375 5210000052$93,990,082$599,551$0$4,197$465,754$133,797$93,856,285$25,032,912$250,000$100,000$249,551$31,176,627.3133599550.5319785167.3319785167.33336347844.611.417 5310000053$93,856,285$599,551$0$4,197$465,085$134,466$93,721,819$25,497,996$250,000$100,000$249,551$31,776,177.8334599550.5320384717.8620384717.86356732562.471.458 5410000054$93,721,819$599,551$0$4,197$464,412$135,138$93,586,680$25,962,409$250,000$100,000$249,551$32,375,728.3635599550.5320984268.3820984268.38377716830.851.500 5510000055$93,586,680$599,551$0$4,197$463,737$135,814$93,450,866$26,426,145$250,000$100,000$249,551$32,975,278.8836599550.5321583818.9121583818.91399300649.751.542 5610000056$93,450,866$599,551$0$4,197$463,057$136,493$93,314,373$26,889,203$250,000$100,000$249,551$33,574,829.4137599550.5322183369.4322183369.43421484019.181.583 5710000057$93,314,373$599,551$0$4,197$462,375$137,176$93,177,198$27,351,578$250,000$100,000$249,551$34,174,379.9338599550.5322782919.9622782919.96444266939.141.625 5810000058$93,177,198$599,551$0$4,197$461,689$137,861$93,039,336$27,813,267$250,000$100,000$249,551$34,773,930.4639599550.5323382470.4823382470.48467649409.621.667 5910000059$93,039,336$599,551$0$4,197$461,000$138,551$92,900,786$28,274,267$250,000$100,000$249,551$35,373,480.9840599550.5323982021.0123982021.01491631430.631.708 6010000060$92,900,786$599,551$0$4,197$460,307$139,243$92,761,542$28,734,574$250,000$100,000$249,551$35,973,031.5141599550.5324581571.5324581571.53516213002.161.750 6110000061$92,761,542$599,551$4,197$459,611$139,940$92,621,603$29,194,185$250,000$100,000$249,551$36,572,582.0342599550.5325181122.0625181122.06541394124.211.792 6210000062$92,621,603$599,551$4,197$458,911$140,639$92,480,963$29,653,096$250,000$100,000$249,551$37,172,132.5643599550.5325780672.5825780672.58567174796.791.833 6310000063$92,480,963$599,551$4,197$458,208$141,343$92,339,621$30,111,304$250,000$100,000$249,551$37,771,683.0844599550.5326380223.1126380223.11593555019.901.875 6410000064$92,339,621$599,551$4,197$457,501$142,049$92,197,571$30,568,805$250,000$100,000$249,551$38,371,233.6145599550.5326979773.6326979773.63620534793.531.917 6510000065$92,197,571$599,551$4,197$456,791$142,760$92,054,812$31,025,596$250,000$100,000$249,551$38,970,784.1346599550.5327579324.1627579324.16648114117.691.958 6610000066$92,054,812$599,551$4,197$456,077$143,473$91,911,338$31,481,673$250,000$100,000$249,551$39,570,334.6647599550.5328178874.6828178874.68676292992.372.000 6710000067$91,911,338$599,551$4,197$455,360$144,191$91,767,148$31,937,033$250,000$100,000$249,551$40,169,885.1948599550.5328778425.2128778425.21705071417.582.042 6810000068$91,767,148$599,551$4,197$454,639$144,912$91,622,236$32,391,672$250,000$100,000$249,551$40,769,435.7149599550.5329377975.7329377975.73734449393.312.083 6910000069$91,622,236$599,551$4,197$453,914$145,636$91,476,600$32,845,586$250,000$100,000$249,551$41,368,986.2450599550.5329977526.2629977526.26764426919.572.125 7010000070$91,476,600$599,551$4,197$453,186$146,364$91,330,236$33,298,772$250,000$100,000$249,551$41,968,536.7651599550.5330577076.7830577076.78795003996.352.167 7110000071$91,330,236$599,551$4,197$452,454$147,096$91,183,139$33,751,227$250,000$100,000$249,551$42,568,087.2952599550.5331176627.3131176627.31826180623.662.208 7210000072$91,183,139$599,551$4,197$451,719$147,832$91,035,308$34,202,946$250,000$100,000$249,551$43,167,637.8153599550.5331776177.8331776177.83857956801.492.250 7310000073$91,035,308$599,551$4,197$450,980$148,571$90,886,737$34,653,925$250,000$100,000$249,551$43,767,188.3454599550.5332375728.3632375728.36890332529.852.292 7410000074$90,886,737$599,551$4,197$450,237$149,314$90,737,423$35,104,162$250,000$100,000$249,551$44,366,738.8655599550.5332975278.8832975278.88923307808.742.333 7510000075$90,737,423$599,551$4,197$449,490$150,060$90,587,363$35,553,652$250,000$100,000$249,551$44,966,289.3956599550.5333574829.4133574829.41956882638.142.375 7610000076$90,587,363$599,551$4,197$448,740$150,811$90,436,552$36,002,392$250,000$100,000$249,551$45,565,839.9157599550.5334174379.9334174379.93991057018.082.417 7710000077$90,436,552$599,551$4,197$447,986$151,565$90,284,988$36,450,378$250,000$100,000$249,551$46,165,390.4458599550.5334773930.4634773930.461025830948.542.458 7810000078$90,284,988$599,551$4,197$447,228$152,322$90,132,665$36,897,606$250,000$100,000$249,551$46,764,940.9659599550.5335373480.9835373480.981061204429.522.500 7910000079$90,132,665$599,551$4,197$446,466$153,084$89,979,581$37,344,073$250,000$100,000$249,551$47,364,491.4960599550.5335973031.5135973031.511097177461.032.542 8010000080$89,979,581$599,551$4,197$445,701$153,849$89,825,732$37,789,774$250,000$100,000$249,551$47,964,042.0161599550.5336572582.0336572582.031133750043.062.583 8110000081$89,825,732$599,551$4,197$444,932$154,619$89,671,113$38,234,706$250,000$100,000$249,551$48,563,592.5462599550.5337172132.5637172132.561170922175.622.625 8210000082$89,671,113$599,551$4,197$444,159$155,392$89,515,721$38,678,864$250,000$100,000$249,551$49,163,143.0663599550.5337771683.0837771683.081208693858.712.667 8310000083$89,515,721$599,551$4,197$443,382$156,169$89,359,552$39,122,246$250,000$100,000$249,551$49,762,693.5964599550.5338371233.6138371233.611247065092.322.708 8410000084$89,359,552$599,551$4,197$442,601$156,950$89,202,603$39,564,847$250,000$100,000$249,551$50,362,244.1165599550.5338970784.1338970784.131286035876.452.750 8510000085$89,202,603$599,551$4,197$441,816$157,734$89,044,868$40,006,663$250,000$100,000$249,551$50,961,794.6466599550.5339570334.6639570334.661325606211.112.792 8610000086$89,044,868$599,551$4,197$441,027$158,523$88,886,345$40,447,691$250,000$100,000$249,551$51,561,345.1667599550.5340169885.1940169885.191365776096.302.833 8710000087$88,886,345$599,551$4,197$440,235$159,316$88,727,030$40,887,925$250,000$100,000$249,551$52,160,895.6968599550.5340769435.7140769435.711406545532.012.875 8810000088$88,727,030$599,551$4,197$439,438$160,112$88,566,918$41,327,364$250,000$100,000$249,551$52,760,446.2169599550.5341368986.2441368986.241447914518.242.917 8910000089$88,566,918$599,551$4,197$438,638$160,913$88,406,005$41,766,002$250,000$100,000$249,551$53,359,996.7470599550.5341968536.7641968536.761489883055.002.958 9010000090$88,406,005$599,551$4,197$437,833$161,717$88,244,287$42,203,835$250,000$100,000$249,551$53,959,547.2671599550.5342568087.2942568087.291532451142.293.000 9110000091$88,244,287$599,551$4,197$437,025$162,526$88,081,761$42,640,859$250,000$100,000$249,551$54,559,097.7972599550.5343167637.8143167637.811575618780.103.042 9210000092$88,081,761$599,551$4,197$436,212$163,339$87,918,423$43,077,071$250,000$100,000$249,551$55,158,648.3173599550.5343767188.3443767188.341619385968.443.083 9310000093$87,918,423$599,551$4,197$435,395$164,155$87,754,268$43,512,466$250,000$100,000$249,551$55,758