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

You will be graded on your MPT payment schedule in Excel and your answers to the individual questions. You must use the Excel template linked above. You will not receive credit if you do not use the template. This week’s content includes two videos that will walk you through all the steps necessary to complete this assignment. 

***Note 1: This is an individual homework assignment.

***Note 2: 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).

Mortgage pass-through securities (MPTs) differ from whole loans in that each share of an MPT security represents an undivided ownership interest in a pool of mortgages. In this way, MPTs are similar to publicly traded stocks. There are, however, many differences between MPTs and stocks. Read pages 639 – 654 in the textbook to learn more about the unique features of MPTs.

————————–

The MPT example provided in Exhibit 19-5 of the textbook uses payments that are calculated on an annual basis. In practice, MPT payments are made on a monthly basis to match the cash inflows (i.e., mortgage payments made by the borrowers) to the cash outflows (i.e., distribution of payments to investors). The video below demonstrates how to convert the annual payments in Exhibit 19-5 to monthly payments.

Prepayment can have a large effect on the cash flows (and associated returns) received by MPT investors. The prepayment assumption in Exhibit 19-7 uses a constant prepayment rate of 10%. This assumption does not match reality. In most cases, there is minimal prepayment in the early years of a mortgage and then it increases over time. The video below compares and contrasts the constant prepayment model to the more commonly used Public Securities Associate (PSA) prepayment model.