Upload an Excel Workbook answers composed of 4 Worksheets entitled #1, #2, #3, and #4. 1. A two-year bond with par value $1,000 making annual coupon payments of $100 is priced at $1,000. What is the yield to maturity of the bond? What will be the realized compound yield to maturity if the one-year interest rate next year turns out to be (i) 8%, (ii) 10%, (iii) 12%? 2. Consider the following $1,000 par value zero-coupon bonds. Bond Years until maturity Yield to maturity A 1 5.0% B 2 6.0% 3 6.5% D 4 7.0% According to the expectation hypothesis, what is the markets expectation of the yield curve one year from now? Specifically, what are the expected values of next year's yield on bonds with maturities of (i) 1 year; (ii) 2 years; (iii) 3 years? 3. For your company, you plan to buy the following bond: Time to maturity, 6 years; coupon rate, 8%; Coupon payment, annual; Market interest rate, 8%; Face value, $1,000. a. Using Excel, calculate the duration of the bond based on concept shown in Spreadsheet 11.1 (p331). b. Explain and develop your immunization strategy if you invested in the bond. c. Assume you reinvest all your coupon income until the holding period based on your immunization strategy in question part b above. Using Excel, calculate the accumulated value of invested payment(or receipt) when you find market interest rate a year later is now 8%, 9%, and 7%, respectively. d. Using Excel, calculate geometric average rate of return (or realized compound return (p298)). e. Based on your results in part c and d above, verify whether your immunization strategy in part b above is working if you find market interest rate a year later is now 8%, 9%, and 7%, respectively. 4. Below are data on economic condition, probability, and expected return on stock and bond funds in a year. Scenario Probability Stock Fund (%) Bond Fund (%) Severe recession 0.05 -37 -10 Mild recession 0.25 -11 10 Normal growth 0.40 14 7 Boom 0.30 30 2 a. Calculate expected return and standard deviation of each fund. b. Calculate covariance and correlation coefficient between stock fund and bond fund. c. Your portfolio is composed of 85% in bond fund and 15% in stock fund. Using Excel, calculate expected return and standard deviation of your portfolio based on Equations 6.5 and 6.6. d. Discuss important implications with respect to portfolio theory as you evaluate all numbers in part a, b, and c above. Upload an Excel Workbook answers composed of 4 Worksheets entitled #1, #2, #3, and #4. 1. A two-year bond with par value $1,000 making annual coupon payments of $100 is priced at $1,000. What is the yield to maturity of the bond? What will be the realized compound yield to maturity if the one-year interest rate next year turns out to be (i) 8%, (ii) 10%, (iii) 12%? 2. Consider the following $1,000 par value zero-coupon bonds. Bond Years until maturity Yield to maturity A 1 5.0% B 2 6.0% 3 6.5% D 4 7.0% According to the expectation hypothesis, what is the markets expectation of the yield curve one year from now? Specifically, what are the expected values of next year's yield on bonds with maturities of (i) 1 year; (ii) 2 years; (iii) 3 years? 3. For your company, you plan to buy the following bond: Time to maturity, 6 years; coupon rate, 8%; Coupon payment, annual; Market interest rate, 8%; Face value, $1,000. a. Using Excel, calculate the duration of the bond based on concept shown in Spreadsheet 11.1 (p331). b. Explain and develop your immunization strategy if you invested in the bond. c. Assume you reinvest all your coupon income until the holding period based on your immunization strategy in question part b above. Using Excel, calculate the accumulated value of invested payment(or receipt) when you find market interest rate a year later is now 8%, 9%, and 7%, respectively. d. Using Excel, calculate geometric average rate of return (or realized compound return (p298)). e. Based on your results in part c and d above, verify whether your immunization strategy in part b above is working if you find market interest rate a year later is now 8%, 9%, and 7%, respectively. 4. Below are data on economic condition, probability, and expected return on stock and bond funds in a year. Scenario Probability Stock Fund (%) Bond Fund (%) Severe recession 0.05 -37 -10 Mild recession 0.25 -11 10 Normal growth 0.40 14 7 Boom 0.30 30 2 a. Calculate expected return and standard deviation of each fund. b. Calculate covariance and correlation coefficient between stock fund and bond fund. c. Your portfolio is composed of 85% in bond fund and 15% in stock fund. Using Excel, calculate expected return and standard deviation of your portfolio based on Equations 6.5 and 6.6. d. Discuss important implications with respect to portfolio theory as you evaluate all numbers in part a, b, and c above