Please help me by providing equations in Excel format for solving the following questions in attached document. Response due by 5:30pm Wednesday, June 28, 2017. Thanks.

Quantitative - Sample Questions 1) The price of a bond rose from $975 to $995 when the yield to maturity fell from 9.75% to 9.25%. What is the modified duration? 2) Consider the following balance sheet (expressed in millions of dollars): Assets Liabilities Overnight loans: $300 Long term debt: $800 (D* = 6 years) 1-year Treasuries: $300 (D* = 0.9 years) Net worth: $100 3-year loans: $300 in (D* = 2.5 years) How would you duration-hedge this balance sheet with an interest rate swap with a fixed-rate side having a modified duration of 4 years and a floating-rate side having a modified duration of 6 months if interest rates increased by 1%? What would be the notional principle (NP) on the swap? a) Receive fixed and pay floating, NP = 600 million b) Receive floating and pay fixed, NP = 720 million c) Receive fixed and pay floating, NP = 850 million d) Receive floating and pay fixed, NP = 1,240 million e) Receive fixed and pay floating, NP = 1,080 million 3) Bank XYZ has the following market-value balance sheet (expressed in millions of dollars): Assets Liabilities Short Term Loans 1000 5-year CDs 1350 Long-Term Loans 500 Net Worth 150 The short-terms loans are zero coupon and repaid at the end of 1 year. The Long-term loans are zero coupon loans that mature in 3 years. On the liability side, the 5-year CDs are also zero coupon. Assume that the yield curve is flat and interest rates are 5% today. Suppose you want to duration hedge the bank's equity by buying a 10-year Treasury STRIP financed with overnight borrowing in the interbank market. How would you hedge against a 1% increase in interest rates using STRIPS? a) Long 425 million b) Short 425 million c) Long 500 million d) Short 500 million e) Long 375 million 4) You are in charge of the risk management division at Hacks, Inc. Hacks' assets consist of investments worth $30 million today in 3-year zero-coupon bonds (so $30 million is a present value). Hack's liabilities consist of a $26 million zero-coupon CD due in 10 years (again, $26 is the present value). The term structure of interest rates is flat at 5%. Assume that all shifts in the term structure are parallel. Hacks thinks that interest rates may rise to 5.5%. Hacks wants to duration hedge its net worth using a 2-year interest rate swap. The payments are swapped at the end of each year, so there are 2 net payments, one in 1 year's time and a second in two year's time. The floating rate resets at the end of the first year. Compute the notional principal of the swap needed for Hacks to be duration hedged, and be sure to say whether Hacks is receiving the fixed rate side of the swap or the floating rate side. a) Receive fixed and pay floating, NP = 155 million b) Receive floating and pay fixed, NP = 155 million c) Receive fixed and pay floating, NP = 178 million d) Receive floating and pay fixed, NP = 178 million e) Receive fixed and pay floating, NP = 327 million 5) Compare the risks for the net worth (equity) of the following three portfolios: a. b. Assets: 500 in 10 year T-bond 100 in cash Liabilities: 500 in floating-rate collateralized finance (repo) 100 in Equity Assets: Liabilities: 100 in cash 100 in Equity Also, there is the following off-balance sheet item: 10-year swap, receive fixed, pay float on notional principal of 500. Which portfolio do you think has the highest overall risk? Consider both interest rate risk and credit risk. Which of the following is correct? a) Portfolio a has higher interest rate risk. b) Portfolio a has higher credit risk. c) Portfolio b has higher interest rate risk. d) Portfolio b has higher overall risk. e) Portfolio a has higher credit risk, but lower interest rate risk. 6) Consider the following two syndication strategies for a $3.3 billion loan: Structure 1 Number Lead Bank Sub-Underwriters Participants Structure 2 Lead Bank Sub-Underwriters Participants 1 4 9 Number 1 0 20 Underwritten amount S/U $3,300 Individual Amount $660 $660 Individual Allocation $300 $300 $200 Underwritten amount S/U $3,300 Individual Amount - Individual Allocation $300 $150 Which of the following is correct? a) The credit risk is higher with Structure 2. b) The credit risk is lower with Structure 1. c) The underwriting risk is higher with Structure 2. d) The underwriting reward is higher with Structure 1. e) None of the above. 7) Consider the following balance sheet: Assets: 950 (10 years = modified duration) Liabilities: 860 in borrowed funds (2 years = modified duration) 90 in Equity Show how to hedge this balance sheet against a 1% increase in interest rates using Treasury bond futures assuming that the underlying bond has a modified duration of 9 years. a) Long 864 million b) Short 864 million c) Short 927 million d) Long 927 million e) Long 746 million 8) Consider the following balance sheet: Assets: 950 (10 years = modified duration) Liabilities: 860 in borrowed funds (2 years = modified duration) 90 in Equity How would you hedge this balance sheet against a 1% increase in interest rates using an interest rate swap with a fixed rate payment with a modified duration of 6 years and a floating rate payment with a modified duration of 2 years? a) Receive fixed and pay floating, NP = 1,864 million b) Receive floating and pay fixed, NP =1,864 million c) Receive floating and pay fixed, NP =1,945 million d) Receive fixed and pay floating, NP =1,945 million e) Receive fixed and pay floating, NP =1,746 million 9) Imagine that you are managing a trading portfolio with 50,000 shares of Deutsche Telecom (DT). The current price is 13 Euros per share. The standard deviation of the daily return on DT estimated over the past 3 months is 2.5 percent. The $/Euro exchange rate is currently 1.4 (that is $1.4 = 1 Euros), and the standard deviation of the percentage change in the exchange rate is 1% per day, again using the past 3 months of data. Also, the correlation between the percentage change in the exchange rate and the return on DT is estimated to be equal to 0.5. Use delta normal to compute the 1-day, 99% VAR for the same trading portfolio from the perspective of a US bank (i.e. in US dollars). a) 62,313 b) 62,459 c) 63,312 d) 63,718 e) 66,206 10) You own a fixed income portfolio with a single 10-year zero-coupon bond currently worth $100 million and paying an annual yield of 4%. During the past 40 trading days there were 4 days when the yield on these bonds did not change, 8 days when the yield increased 1 basis point, 9 days when the yield decreased by 1 basis point, 6 days when the yield increased by 2 basis points, 4 days when the yield decreased by 2 basis points, 4 days when the yield increased by 3 basis points, 2 days when the yield decreased by 3 basis points, 2 days when the yield increased by 10 basis points, and 1 day when the yield increased by 14 basis points. The standard deviation of the yield changes over these 40 trading days is 3.16 basis points. What is the 1-day 95% VAR for this portfolio using historical simulation? a) 0.82 million b) 0.88 million c) 0.92 million d) 0.96 million e) 1.00 million 11) Imagine that you are managing a trading portfolio with 100 shares of stock in Samsung. The current price is 12,800 Won (the South Korean currency). The standard deviation of the daily return on Samsung estimated over the past 3 months is 1 percent. The $/Won exchange rate is currently 1/980 (that is $1 = 980 Won), and the standard deviation of the exchange rate (in percentage terms) is 0.4% per day, again using the past 3 months of data. Also, the correlation between the percentage change in the exchange rate and the return on Samsung is estimated to be equal to 0.5. Use delta normal to compute the 1-day, 99% VAR for the portfolio from the perspective of a US bank (i.e. in US dollars). a) $38 b) $44 c) $48 d) $52 e) $56