Answer All Questions From Part A) To Part D) 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% (Included Your Formula You Entered In Excel). 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% (Included Your Formula You Entered In Excel). 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%. (Included Your Formula You Entered In Excel) M N OP Q R R A) % % % % Monthly Annual IB.) % % % % % % c) A B C D E G G H K 1 2 2 Mortgage Pass Through Securities 3 4 4 Data Inputs 5 5 Number of mortgages in initial pool 40 6 6 Average mortgage balance $25,000 7 Initial mortgage pool balance $1,000,000 8 8 Prepayment rate 10.00% 9 9 Mortgage rate 9.00% 10 Coupon rate 8.50% 11 Market interest rate 9.00% 12 Servicing and Guarantee Fee 0.50% 13 14 (a) (b) (c) (d (d) (e) ) (0) (g) 15 Guarantee Total Pmt to 16 Total and Payments Individual 17 Pool Principal P81 Payment Service Fees to Investors Investor 18 Year Balance Prepayment Payment (b) + (c) (a) x (0.005) ( (d) -(e) (1) / 40 19 0 $1,000,000 (S25,000) 201 1 1 834,180 100,000 155 820 255,820 5,000 250,820 6,271 21 2 686,698 83,418 139,140 222,558 4,171 218,387 5,460 22 3 555,762 68,670 124,069 192,739 3,433 189,305 4,733 23 4 439,780 55,576 110,425 166,001 2,779 163,222 4,081 24 5 337,346 43,978 98,036 142,014 2,199 139.815 3,495 25 6 247,244 33,735 86.729 120,464 1,687 118,777 2.969 26 7 168,455 24,724 76,316 101,041 1236 99,805 2,495 27 8 100, 221 16,845 66,549 83,394 842 82,552 2,064 28 9 42,246 10,022 56,973 66,995 501 66,494 1,662 29 10 0 0 46,049 46,049 211 45,837 1,146 30 31 Discount rate Price to Green Value of MPT 32 8.50% $1,000,000 $25,000 33 7.50% $1,034,622 $25,866 34 9.50% $967,326 $24,183 35 D) % % % % Answer All Questions From Part A) To Part D) 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% (Included Your Formula You Entered In Excel). 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% (Included Your Formula You Entered In Excel). 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%. (Included Your Formula You Entered In Excel) M N OP Q R R A) % % % % Monthly Annual IB.) % % % % % % c) A B C D E G G H K 1 2 2 Mortgage Pass Through Securities 3 4 4 Data Inputs 5 5 Number of mortgages in initial pool 40 6 6 Average mortgage balance $25,000 7 Initial mortgage pool balance $1,000,000 8 8 Prepayment rate 10.00% 9 9 Mortgage rate 9.00% 10 Coupon rate 8.50% 11 Market interest rate 9.00% 12 Servicing and Guarantee Fee 0.50% 13 14 (a) (b) (c) (d (d) (e) ) (0) (g) 15 Guarantee Total Pmt to 16 Total and Payments Individual 17 Pool Principal P81 Payment Service Fees to Investors Investor 18 Year Balance Prepayment Payment (b) + (c) (a) x (0.005) ( (d) -(e) (1) / 40 19 0 $1,000,000 (S25,000) 201 1 1 834,180 100,000 155 820 255,820 5,000 250,820 6,271 21 2 686,698 83,418 139,140 222,558 4,171 218,387 5,460 22 3 555,762 68,670 124,069 192,739 3,433 189,305 4,733 23 4 439,780 55,576 110,425 166,001 2,779 163,222 4,081 24 5 337,346 43,978 98,036 142,014 2,199 139.815 3,495 25 6 247,244 33,735 86.729 120,464 1,687 118,777 2.969 26 7 168,455 24,724 76,316 101,041 1236 99,805 2,495 27 8 100, 221 16,845 66,549 83,394 842 82,552 2,064 28 9 42,246 10,022 56,973 66,995 501 66,494 1,662 29 10 0 0 46,049 46,049 211 45,837 1,146 30 31 Discount rate Price to Green Value of MPT 32 8.50% $1,000,000 $25,000 33 7.50% $1,034,622 $25,866 34 9.50% $967,326 $24,183 35 D) % % % %