please answer the question using the excel sheet Question 1 Update the annual MPT template posted to accurately reflect distributions of monthiy 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%. 6.) 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% e) 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%. Mortgage Pass Through Seturi AI 5 75 7 You Bees 1000 24 58. 250 2221 4 2.773 4. 4 3034 7 ETT 26 16 100 . 10 TE Mortgage Pass Through Securities Al 7.50% 8.50% 9.50% Monthly Annual Data Inputs Number of mortgages in initial pool Average metgage balance Inital mortgage pool balance Prepaymerate Mortgagerie Coupon Market west rate Servicing at a Guaranteed B.] 40 $25,000 $1,000,000 10.00% 9.00% 8.50% 9.00% 0.50% 7.50%) 8.50% 9,50% 7.50% 8.50% 9.50 c) (ch (0 el Guarantee and Service Fees (0.005) D Total Payments to investors M. D) Total Payment I. PAI Principal Prepel 7.50% 8.50% 0.00% Year Payment Peel Dalarna $1.000.000 34180 MON 555.752 43.780 337146 247.244 168. 455 100221 42.246 5.000 4,171 3431 2.779 3 4 5 100.000 83.418 68670 55.676 43.978 33,736 24724 16.845 10.022 0 2.199 155,820 139, 140 12400 110.425 98.036 86.729 76316 66 549 56.973 45.049 255,820 222,650 192,730 106,000 142,014 120,464 101 041 83,304 66,995 46,040 Pinto Individual Investor 40 (125.000 6.271 5460 4.733 4.001 3405 2.969 2.495 2064 1662 1.146 250 120 218.7 109,105 163.222 139 815 118.777 9005 82.552 66494 45 837 6 7 1.687 1.215 842 501 211 9 10 Discount rate 8.50% 7.50 9.50% Price to Green Value of MPT $1,000,000 $25,000 $1.034.622 $25,866 $967,326 $24,183 Fally the came to me invece anul payments. In the parts are my 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%. please answer the question using the excel sheet Question 1 Update the annual MPT template posted to accurately reflect distributions of monthiy 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%. 6.) 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% e) 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%. Mortgage Pass Through Seturi AI 5 75 7 You Bees 1000 24 58. 250 2221 4 2.773 4. 4 3034 7 ETT 26 16 100 . 10 TE Mortgage Pass Through Securities Al 7.50% 8.50% 9.50% Monthly Annual Data Inputs Number of mortgages in initial pool Average metgage balance Inital mortgage pool balance Prepaymerate Mortgagerie Coupon Market west rate Servicing at a Guaranteed B.] 40 $25,000 $1,000,000 10.00% 9.00% 8.50% 9.00% 0.50% 7.50%) 8.50% 9,50% 7.50% 8.50% 9.50 c) (ch (0 el Guarantee and Service Fees (0.005) D Total Payments to investors M. D) Total Payment I. PAI Principal Prepel 7.50% 8.50% 0.00% Year Payment Peel Dalarna $1.000.000 34180 MON 555.752 43.780 337146 247.244 168. 455 100221 42.246 5.000 4,171 3431 2.779 3 4 5 100.000 83.418 68670 55.676 43.978 33,736 24724 16.845 10.022 0 2.199 155,820 139, 140 12400 110.425 98.036 86.729 76316 66 549 56.973 45.049 255,820 222,650 192,730 106,000 142,014 120,464 101 041 83,304 66,995 46,040 Pinto Individual Investor 40 (125.000 6.271 5460 4.733 4.001 3405 2.969 2.495 2064 1662 1.146 250 120 218.7 109,105 163.222 139 815 118.777 9005 82.552 66494 45 837 6 7 1.687 1.215 842 501 211 9 10 Discount rate 8.50% 7.50 9.50% Price to Green Value of MPT $1,000,000 $25,000 $1.034.622 $25,866 $967,326 $24,183 Fally the came to me invece anul payments. In the parts are my 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%