Finance Programming Excel for fixed income

Finance Programming Excel for fixed income

Fixed Income Due: 11/26/19 (electronically only, no late submissions accepted)

Project #1 30 Points Total

Each team will receive an email with two screen shots. If you didn’t work with someone for homework 5 and would like to for this project, send me an email with both names, otherwise I’ll pair people according to how homework 5 was submitted. The first shows the roll analysis and the second shows the cash flows. You will use these screen shots to answer the questions below. Be aware of the following payment dates: GNSF has a 14 day delay, FGMLC has a 14 day delay and FNCL has a 24 day delay.


1. (10 points) Create a dollar roll calculator in Excel (use the Dollar Roll Template spreadsheet pro- vided). You will use the input assumptions from the roll analysis screen shot emailed to you. Note: You will need to reduce the WAM by 1 month if factor date is 10/19 and by 2 months if factor date is 11/19 (find this in the Remaining Term cell). If your calculations are correct, you will agree with your Bloomberg screen to the penny! What is the Dollar Advantage, and is it for the mortgage roll or continued holding?

Understanding Dollar Roll Valuation

2. (2 points) Now add 0-03 to the drop you used above on the assumption that the roll is hot. What is the dollar advantage on $100 million of MBS?

3. (2 points) Assume you decide to roll $100 million of your MBS, how much do you receive at the beginning of the roll?

4. (2 points) How much interest do you earn during the roll period?

5. (2 points) How much do you pay to buy back the MBS at the end of the roll?

6. Now suppose you immediately sell the MBS on the forward settlement date (i.e., the later date listed) for a price that is 0-04 higher than the price you received at the beginning of the roll (i.e., IMM. Price).

(a) (3 points) What is the total return in dollars?

(b) (1 point) What is the cost basis for this trade? (You need this to answer following question.)

(c) (4 points) What is the annualized total return? Use 360/actual to annualize return.

(d) (4 points) What is the annualized total return if you had decided to hold the $100 million MBS and then sell on that same date for 0-04 higher than the IMM. price?