please answer the question using the excel sheet

Question 1 Update the annual MPT template posted to accurately reflect distributions of monthly mortgage payments. Then calculate the following: a.) Set the constant prepayment rate equal to 0% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. b.) Set the constant prepayment rate equal to 5% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. c.) Compare the monthly vs, annual investor values when the prepayment rate is equal to 5%. Are the annual or monthly values different? If so, why? d.) Set the constant prepayment rate equal to 20% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. BC D E J L M N O 0 R Mortgage Pass Through Securities A) 7.50% 8.50% 9.50% Data input Number of mortgages in pool Average manage balance Initial mortgage pool balance Prepayment to Mortgage rate Coupon male Maria Werest rate Bonvicing and Guarantee Fes Monthly Annual 40 525.000 51.000.000 1600% 9.00 2509 9.00 DNON e) 7.50% 8 80% 9.BOX 7.50N 8.50% 8.50% C) ad Principal Prepayment tel Grand and Service Feed ((0.005) D) Total Payment to investore Total Payment c) PAI Payment TRI Pm to individual Investor 10/40 $25.000 6,271 7.50% 50% 9.504 83.410 5.460 Peel Year Balance o $1.000.000 834,180 2 666,696 3 555,762 439,780 5 337346 247,244 7 168,455 B 100.221 9 42.240 10 0 100.000 155,820 255,820 139,140 222558 68.670 124,069 192.730 55,576 110,425 166001 43.978 $8.036 142014 93,735 86.720 120.484 24.724 76.316 101,041 16,845 56,549 83,354 10.022 se 973 56.896 0 46.049 46,049 Discount Price te Green Value of MPT DON 51,000,000 125 000 7.50% 31.034,022 125.266 $267.320 124169 5.000 4,171 3,433 2,779 2,193 1.887 1.238 842 501 211 250.620 218.367 109.305 163,222 139,816 118,777 99.805 82.562 66.494 45,837 4,733 4,061 3.495 2,869 2,495 2,064 1.062 1.146 I Towe Question 1 Update the annual MPT template posted to accurately reflect distributions of monthly mortgage payments. Then calculate the following: a.) Set the constant prepayment rate equal to 0% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. b.) Set the constant prepayment rate equal to 5% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. c.) Compare the monthly vs, annual investor values when the prepayment rate is equal to 5%. Are the annual or monthly values different? If so, why? d.) Set the constant prepayment rate equal to 20% and calculate the value of the cash flows to the individual investor using a discount rate of 7.5%, 8.5%, and 9.5%. BC D E J L M N O 0 R Mortgage Pass Through Securities A) 7.50% 8.50% 9.50% Data input Number of mortgages in pool Average manage balance Initial mortgage pool balance Prepayment to Mortgage rate Coupon male Maria Werest rate Bonvicing and Guarantee Fes Monthly Annual 40 525.000 51.000.000 1600% 9.00 2509 9.00 DNON e) 7.50% 8 80% 9.BOX 7.50N 8.50% 8.50% C) ad Principal Prepayment tel Grand and Service Feed ((0.005) D) Total Payment to investore Total Payment c) PAI Payment TRI Pm to individual Investor 10/40 $25.000 6,271 7.50% 50% 9.504 83.410 5.460 Peel Year Balance o $1.000.000 834,180 2 666,696 3 555,762 439,780 5 337346 247,244 7 168,455 B 100.221 9 42.240 10 0 100.000 155,820 255,820 139,140 222558 68.670 124,069 192.730 55,576 110,425 166001 43.978 $8.036 142014 93,735 86.720 120.484 24.724 76.316 101,041 16,845 56,549 83,354 10.022 se 973 56.896 0 46.049 46,049 Discount Price te Green Value of MPT DON 51,000,000 125 000 7.50% 31.034,022 125.266 $267.320 124169 5.000 4,171 3,433 2,779 2,193 1.887 1.238 842 501 211 250.620 218.367 109.305 163,222 139,816 118,777 99.805 82.562 66.494 45,837 4,733 4,061 3.495 2,869 2,495 2,064 1.062 1.146 I Towe