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