calculation on excel using this spreadsheet

2. You are given the following data on 8/24/2015 settlement date: Maturity 8/31/2017 8/15/2025 Annual Coupon Rate Bid Price 1.875 102.4922 6.875 144.6719 Asked Price 102.5078 144.6875 and the following data on 8/28/2015 settlement date: Maturity 8/31/2017 8/15/2025 Annual Coupon Rate Bid Price 1.875 102.1875 6.875 142.4531 Asked Price 102.2031 142.4688 a) On 8/24/2015, open a repo position using the 8/31/2017 maturity bond and close the position on 8/28/2015. Compute the profit/loss on this transaction. b) On 8/24/2015, open a reverse repo position using the 8/15/2025 maturity bond and close the position on 8/28/2015. Compute the profit/loss on this transaction. Use an annual repo rate of 1.0% and an annual reverse repo rate of 1.5%. Explain why you made a profit/loss in each of the above transactions. Also, make sure to account for accrued interest in your calculations and choose the bid or ask price appropriately. If you are using the Excel function for accrued interest, make up an Issue Date as the coupon payment date just prior to the settlement date. 1 2 3 Strategy on 9/14/2007. Repo Long $ 100,000,000.00 2-year Reverse Repo Short $ 100,000,000.00 10-year Buy/place as collateral/borrow cash/ then sell bond/repay cash with interest Borrow bond/sell bond/place cash as colateral/ then Buy bond/replace bond/collect cash plus interest 4 5 6 7 Repo 8 Bond bought in market and delivered 9 Loan amount received 10 Long position profit and Loss Date Long Market Value Repo rate Days Financing cost Interest is calculated on the market value of the collateral 9/14/07 100.0816 4.80% 3 9/17/07 100.0678 4.80% 1 $ 40,032.64