excel assignment 3
Introduction to Investments
Finance 340
Excel Assignment #3: Bond Portfolio Immunization
Objectives:
- Bond portfolio immunization.
- Excel ‘Data Table’.
The in-class hand-on example is based on Handout exercise 6.
Problem:
You will be paying $13,159.32 for a Hawaii trip in 7 years. The market interest rate is 4%, so the present value of the obligation is $10,000.
1) Construct a portfolio of two Bonds (Bond1: Coupon payments 3% paid annually, face value $1000 and maturity 5 years, Bond2: Coupon payments 5% paid annually, face value $1000 and maturity 15 years) that can immunize this obligation. Let’s call this portfolio A.
2) What would be the time to maturity of a zero-coupon bond with face value $1000 that would also immunize your obligation (Bond3)? Let’s call this portfolio B (includes only zero-coupon bond).
3) Check if our immunization works when interest rate changes, for example to 5%.
4) Plot present values of the two portfolios by varying the yield to maturity.
5) What portfolio, A or B, is better?
I describe the steps in details below: 1) Immunization with Bond1 and Bond 2
i) Calculate PV of the liability.
- What is duration of the liability?
ii) Calculate the price and duration of Bond1 and Bond2
- Use Excel functions price and duration. iii) Find the weights of bonds such that the duration of asset matches the duration of the liability
iv) Fully fund the obligation (i.e to make sure that PV of asset = PV of liability). Calculate numbers of Bond1 and Bond2 to be purchased.
2) Alternatively, we can immunize this obligation with a zero-coupon bond (Bond3). What should be the maturity of the zero-coupon bond? How many of this bond we will buy?
3) Check if our immunization works when interest rate changes, for example to 9%.
i) Calculate the bond prices at the new interest rate. ii) Calculate value of bond portfolio after rate change. Note the value of bond
Finance 340
Excel Assignment #3: Bond Portfolio Immunization
Objectives:
- Bond portfolio immunization.
- Excel ‘Data Table’.
The in-class hand-on example is based on Handout exercise 6.
Problem:
You will be paying $13,159.32 for a Hawaii trip in 7 years. The market interest rate is 4%, so the present value of the obligation is $10,000.
1) Construct a portfolio of two Bonds (Bond1: Coupon payments 3% paid annually, face value $1000 and maturity 5 years, Bond2: Coupon payments 5% paid annually, face value $1000 and maturity 15 years) that can immunize this obligation. Let’s call this portfolio A.
2) What would be the time to maturity of a zero-coupon bond with face value $1000 that would also immunize your obligation (Bond3)? Let’s call this portfolio B (includes only zero-coupon bond).
3) Check if our immunization works when interest rate changes, for example to 5%.
4) Plot present values of the two portfolios by varying the yield to maturity.
5) What portfolio, A or B, is better?
I describe the steps in details below: 1) Immunization with Bond1 and Bond 2
i) Calculate PV of the liability.
- What is duration of the liability?
ii) Calculate the price and duration of Bond1 and Bond2
- Use Excel functions price and duration. iii) Find the weights of bonds such that the duration of asset matches the duration of the liability
iv) Fully fund the obligation (i.e to make sure that PV of asset = PV of liability). Calculate numbers of Bond1 and Bond2 to be purchased.
2) Alternatively, we can immunize this obligation with a zero-coupon bond (Bond3). What should be the maturity of the zero-coupon bond? How many of this bond we will buy?
3) Check if our immunization works when interest rate changes, for example to 9%.
i) Calculate the bond prices at the new interest rate. ii) Calculate value of bond portfolio after rate change. Note the value of bond