Create an Excel spreadsheet in order to answer Questions #2, #3 and #4. Assumptions and suggested structure of the spreadsheet are below As in our class discussion and notes, all bonds have an annual coupon and our calculations all occur on a coupon date. Set up your spreadsheet as follows to answer the questions: Value / Calculation Discount rate for bonds and obligation Respective (annual) coupon rate per bond Years to maturity Face value of the bond(s) Use Excel PV function with above inputs (Face / Bond) Bond Excel DURATION (you must use dates) Excel PV of coupons after future date PV of Face Value (Principal) on future date Excel FV function for coupons to future date Bond Price on Obligation Payment Date Reinvested Coupons Given from the Obligation Amount Immunization Assignment Yield to maturity Coupon Rate Maturity Face Value Bond Price Today Face Value per EUR 1,000 Market Value Duration Bond Price on Obligation Payment Date Reinvested Coupons Total Bond Proceeds on Obligation Date Final Value Percentage of Face Value Purchased Final Value Total Bond Proceeds on Obligation Date Assume an initial discount rate for all assets and liabilities of 5%. The known, fixed zero-coupon liability obligation has a current value of EUR 1,000 and is payable in 9 years. Three annual EUR government coupon bonds are available to purchase and offset the obligation: 5.25% coupon and ten years to maturity EUR10: EUR15 5.75% coupon and fifteen years to maturity EUR30: 6.00% coupon and thirty years to maturity 2.) The first step in immunization is to purchase an asset which is the same value as the obligation. Calculate the face value purchase amounts which would be required for each of the four bonds in order to match the present value of the obligation 3.) Calculate the future value of the obligation. 4.) Step Two is to select a portfolio which matches the duration of the obligation. Construct a portfolio (A) of bonds EUR10 and EUR30 to offset the obligation a.) What are the weights of the bonds that create a matching portfolio? b.) What are the respective face values of the EUR10 and EUR30 bonds to be purchased in order to construct this portfolio? Create an Excel spreadsheet in order to answer Questions #2, #3 and #4. Assumptions and suggested structure of the spreadsheet are below As in our class discussion and notes, all bonds have an annual coupon and our calculations all occur on a coupon date. Set up your spreadsheet as follows to answer the questions: Value / Calculation Discount rate for bonds and obligation Respective (annual) coupon rate per bond Years to maturity Face value of the bond(s) Use Excel PV function with above inputs (Face / Bond) Bond Excel DURATION (you must use dates) Excel PV of coupons after future date PV of Face Value (Principal) on future date Excel FV function for coupons to future date Bond Price on Obligation Payment Date Reinvested Coupons Given from the Obligation Amount Immunization Assignment Yield to maturity Coupon Rate Maturity Face Value Bond Price Today Face Value per EUR 1,000 Market Value Duration Bond Price on Obligation Payment Date Reinvested Coupons Total Bond Proceeds on Obligation Date Final Value Percentage of Face Value Purchased Final Value Total Bond Proceeds on Obligation Date Assume an initial discount rate for all assets and liabilities of 5%. The known, fixed zero-coupon liability obligation has a current value of EUR 1,000 and is payable in 9 years. Three annual EUR government coupon bonds are available to purchase and offset the obligation: 5.25% coupon and ten years to maturity EUR10: EUR15 5.75% coupon and fifteen years to maturity EUR30: 6.00% coupon and thirty years to maturity 2.) The first step in immunization is to purchase an asset which is the same value as the obligation. Calculate the face value purchase amounts which would be required for each of the four bonds in order to match the present value of the obligation 3.) Calculate the future value of the obligation. 4.) Step Two is to select a portfolio which matches the duration of the obligation. Construct a portfolio (A) of bonds EUR10 and EUR30 to offset the obligation a.) What are the weights of the bonds that create a matching portfolio? b.) What are the respective face values of the EUR10 and EUR30 bonds to be purchased in order to construct this portfolio