Chariot Bank has begun making small loans to students. All students who get the loan receive exactly the same amount of money. Students pay back the loan in quarterly installments beginning 1 quarter from now. The loans are 2.5 - year loans that require the student make 10 quarterly payments of $200 each. The $200 quarterly payment covers the repayment of principal as well as interest.

Assume for this exercise that Chariot Bank makes 30 loans. The loans are ranked according to the estimated credit risk of the students. Eight of the 30 students are deemed to have high credit risk; the remainder have average credit risk. Rather than holding the 30 individual loans, the Bank pools together the cash flows together and create 4 financial securities (CSLs) from the cash flows. These securities are:

I) CSL-H. The owner of this security receives the pooled cash flows from the 8 high-risk loans only. They receive all the payments over all the time periods.

II) CSL-A. The owner of this security receives the pooled cash flows from the 22 average credit risk loans. From those loans, they receive all the payments for quarters 1 and 2; no payments in quarters 3 through 10.

III. CSL-B. The owner of this security receives the pooled cash flows from the 22 average credit risk loans. From these loans, they receive all the payments for quarters 3 through 8. They receive no payment in quarters 1 and 2 and no payments in quarters 9 and 10.

IV. CSL-C. The owner of this security receives the pooled cash flows from the 22 average credit risk loans. From these loans, they receive all the payments for quarters 9 and 10. They receive no payments in quarters 1 through 8.

Given the above information answer the following questions.

1. Create and excel spreadsheet with chart(s) showing the cash flows from each of the 30 loans over time. In your spreadsheet be sure to identify which cash flows are going to which security. (Your job in this question is to create a chart or charts that you can use effectively to convey the information about the cash flows and use for pricing purposes).

2. If investor's required rate of return on all CSL securities is 6%, calculate their price. On your Excel spreadsheet create a formula and shows your calculations; no explanation is necessary.