San Diego State University Business Worksheet

Download the Excel homework template Download Excel homework template and follow the instructions below to complete this assignment.

***Note: When you change the prepayment rate, the value of the cash flows will change. You can type/hardcode your answers off to the right of your spreadsheet (or copy and paste special values). This will ensure your answers do not change when you change the prepayment rate assumption.

—————————————–

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%.

—————————————–

Question 2

The Public Securities Association (PSA) Prepayment Model has become the industry standard. Create a copy of the worksheet from question 1 and modify it so that it uses a PSA Prepayment Model instead of a constant prepayment rate. Then calculate the following:

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%.

b.) Using an 75% 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%.

c.) Using a 125% 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%.

d.) Create a graph that displays the 125% PSA rate (see example below of 100% PSA).