i have this assignment due on march 28th can someone help me do this on excel?

MGT 4068 - Fixed Income Securities Assignment 2 Prof. Hsu This assignment must be submitted by the end of class on Wednesday, March 30th , 2016. The assignment questions are to be completed in groups of up to three people. You must show the details of your work in Excel spreadsheets. Please HIGHLIGHT your nal answers. The assignment will be marked based on (1) how you arrive at the solution, (2) is the solution correct or does it make sense? (3) the presentation of your results. Remember, you must present your work in a clear and concise manner. Make sure your spreadsheets are tidy, and clearly labeled. Your group members can be dierent from assignment 1. However, you are no longer allowed to change your group members after this assignment. This assignment consists of two parts: A and B. PART (A) Hedging and Risk Management 1. Risk Measurement, Dollar Duration, Convexity, and Macaulay Duration - 30 points The following discount factors are given: B(0, 1) = 0.957067 B(0, 2) = 0.87358 B(0, 3) = 0.802550 B(0, 4) = 0.784688 As a reminder, $ Duration, for a bond with continuously compounded yields is: T tKt eyt . $ = t=1 (a) Using continuous compounding, calculate the duration D(y), modied duration M D(y),the dollar duration $ , and the dollar convexity $ of a 4-year 9% annual coupon bond with face value $1,000. (b) If there is a 200 basis point downward shift in the continuously compounded zeroyield curve (assume all shifts in the continuously compounded zero yield curve are uniform) calculate the following: i. The actual price of this 4-year, 9% coupon bond; ii. The price of the 4-year bond as estimated by $ alone; iii. The price of the 4-year bond as estimated by both $ and $ . 2. Risk Management, Duration, and Convexity - 30 points Consider the following three securities: Security A is a 2-year zero which matures two years from today, and pays $1 at maturity; 1 Security B is a 3-year zero which matures three years from today, and pays $1 at maturity; Security C is a 1-year forward contract which matures one year from today, and delivers a 1-year zero (with face value $1) at maturity. My portfolio currently consists of a long position of 5,000 units of security B and a short position (liability) of 3,000 units of security A. Suppose the only securities I can currently trade (buy/sell) are Security A and Security C. The current term structure based on zero coupon bonds (with 1-, 2-, 3-, and 4-year maturity from today) in annualized rates with continuous compounding is: R(0, 1) = 5% R(0, 2) = 6% R(0, 3) = 8% R(0, 4) = 10% (a) What are the current value, the dollar duration and the convexity of my portfolio? Interpret the dollar duration and the convexity numbers (i.e. for a 100 basis point parallel increase in the term structure, what do the duration and convexity numbers say?). (b) Without changing the value of my portfolio (found in part (a)), what do I have to do now (i.e., if anything, long/short what? how much?) to achieve a dollar duration of zero for my portfolio? (Suppose I don't care about convexity.) (c) How long is the hedge in part (b) (i.e., the zero dollar duration position) good for? Why? Explain. 3. Risk Management, relaxing the parallel shift assumption - 40 points Currently, t = 0, a portfolio of 5 annually coupon-paying bonds, each with a face value of $100. Their characteristics are given by Bond A Current Price $98 Maturity 2 Annual Coupon payment 6.25% Bond B $98 3 7.00% Bond C $98 4 7.50% Bond D $95 5 8.00% Bond E $85 6 6.00% In addition to the above bond prices, you also know that the current one-year continuously compounded spot rate is R(0, 1) = 4%. Your objective here is construct a portfolio that hedges the changes in the 1-year, 2-year, and 3-year spot rates. You decide to leave your portfolio exposed to changes in the interest rates at the longer horizon (i.e., 4-, 5- and 6-year spot rates). This is because you are betting on the changes in the zero rates at the longer end of the maturity. However, you want to limit your risk of the short-term rate changes. (a) Construct a zero initial cost portfolio using Bond A to Bond E such that you are hedged against the changes in the 1-year, 2-year, and 3-year spot rates. This portfolio must have zero initial investment cost. Note, you only have to hedge against the rst-order changes in these rates. Hint: there are more than one way to construct your portfolio. Show me the possible solutions that are sensible (at 2 least three). Use your common sense. Then nally choose the portfolio that is most appropriate for your objective. Use this optimal portfolio in your Part B and C. (b) Assume that exactly a year has gone by, t = 1, and you want to close out the portfolio that you constructed in (a). Notes, all these bonds are now a year closer to their maturities. The zero rates at time t = 1 is at at 5% across all the maturities. How much prot or loss has this trading strategy been for you? (c) You decide not close out your positions at year 1, but hold the portfolio until two years have gone by, hence we are now at t = 2. The zero rates at time t = 2 is at at 10% across all the maturities. How much prot or loss has this trading strategy been for you? 3