IO & PO Pricing Excel Assignment NA. ou are considering the purchase of a mortgage backed security and need to develop a financial model to help determi appropriate price. The MBS is backed by 100 identical mortgages. Each mortgage has a term of 5 years, a contract ra of 10%, and an original amount of $180,000. Temortgagesmakeannualpaments. To make the problem easier, assume that interest rates will change only at the end of each year Experts predict that changes interest rates in the future will be normally distributed with a mean of zero and a standard deviation of0.40%. Mortgage rates are currently 10%. You think prepayments will occur in 32% percent of the mortgages when rates fan to the point that refinancing is cost effective. Otherwise, you do not ex mortgage balance (the mortgage balance after the current payment) You require a return of 1 1% to invest in this MBS. pect any prepayments. You assume that refinancing costs will total 1.1% of the (I) Using the Monte Carlo method we discussed in class, generate the probability distribution of outcomes for the MBS, the IO Strip and the PO Strip. Use Excel functions to find the mean, median, min, max and standard deviation of the outcomes range of outcomes into 5 equal parts. Your last bin should be the MAX + . Explain your results in the space below he Data Analysis tool or Countif function to make a histogram of the outcomes. To determine the bins, divide the ee the back of this page for hints IO & PO Pricing Excel Assignment NA. ou are considering the purchase of a mortgage backed security and need to develop a financial model to help determi appropriate price. The MBS is backed by 100 identical mortgages. Each mortgage has a term of 5 years, a contract ra of 10%, and an original amount of $180,000. Temortgagesmakeannualpaments. To make the problem easier, assume that interest rates will change only at the end of each year Experts predict that changes interest rates in the future will be normally distributed with a mean of zero and a standard deviation of0.40%. Mortgage rates are currently 10%. You think prepayments will occur in 32% percent of the mortgages when rates fan to the point that refinancing is cost effective. Otherwise, you do not ex mortgage balance (the mortgage balance after the current payment) You require a return of 1 1% to invest in this MBS. pect any prepayments. You assume that refinancing costs will total 1.1% of the (I) Using the Monte Carlo method we discussed in class, generate the probability distribution of outcomes for the MBS, the IO Strip and the PO Strip. Use Excel functions to find the mean, median, min, max and standard deviation of the outcomes range of outcomes into 5 equal parts. Your last bin should be the MAX + . Explain your results in the space below he Data Analysis tool or Countif function to make a histogram of the outcomes. To determine the bins, divide the ee the back of this page for hints