Please use Excel to complete the problem below.
Immunization
You, the CFO of a large pension fund, are responsible for making sure that your fund is fully funded (i.e., that your fund has enough money to meet its obligations). You estimate that, in 10 years, your fund will need to pay out $5 billion. The market interest rate is 5.4%. You decide to fund the obligation using five-year zero-coupon bonds and perpetuities that make annual coupon payments.
1. How can you immunize the obligation? (Here, you need to construct an immunized portfolio that consists of the zero-coupon bonds and the perpetuities.) To begin, you need to find the present value of the obligation.
2. Now suppose that one year has passed and that the market rate is still 5.4%. You need to ensure that the obligation is still fully-funded and immunized. Is the obligation still fully-funded and immunized? If not, what do you need to do to fully fund and immunized the obligation?
TEMPLATE
D F 1 Immunization 3 Amount of Obligaton = FILL IN. 4 Market interest rate FILL IN (Decimal). Obligation due in FILL IN. 6 PV (Obligation) = PROGRAM. years 1. To immunize, need to find the duration of the asset and liability, equate them, 10 and solve for optimal weights of investment in the zeros and in the perpetuity. 22 Duration of obligation FILL IN. years Duration of zeros = FILLIN. years 34 Duration of perpetuity = (1+r/r= years PROGRAM. 16 Let w be the percentage of funds invested in the zeros. Then (1-w) = percentage of funds invested in the perpetuity. WRITE FORUMULA WITH NUMBERS (e.g., 10w + 6(1-w) = 13). 19 Setting D(assets) = Dliabilities) yields 22 The above equation yields w= 22 Also, 1-w= FILL IN AS DECIMAL. Do algebra elsewhere. PROGRAM. 24 Therefore, invest 25 and PROGRAM PROGRAM. in zeros in the perpetuity. 27 2. One year has passed, and market rate is still = FILL IN AS PERCENT. 28 As a decimal, the market rate PROGRAM. 29 30 is obligation still fully funded? To answer this question, 3 need to see if amounts invested in assets still equal the new PV (obligation). 32 33 PV[obligation) is now = PROGRAM 34 35 PV (zeros) = PROGRAM. 38 PV (perpetuity) = FILLIN. 33 Perpetuity has paid coupon PROGRAM. 36 So, total PV[asset) is now = PROGRAM. 39 40 is obligation still fully funded? FILL IN ANSWER. 42 ts position still immunized? Need to see if weights need to be changed 43 by recalculating D(assets) and D[liability), equating them, and solving forw. 44 D[liability) now FILLIN. years. 45 Dizeros) now FILLIN. years. -47 (perpetuity) = PROGRAM years. 45 49 so, setting D[asset) = D(liability) yields WRITE FORUMULA WITH NUMBERS, SIMILAR TO ABOVE. 50 This equation yields w= FILL IN AS DECIMAL., 50 (1-w) = PROGRAM. Leave as decimal. 51 52. Based on wand 1-w, is the position still immunized? ANSWER HERE. 53 54 If we need to re-immunize our portfolio, invest PROGRAM. in zeros and PROGRAM. in perpetuity. 55 55 D F 1 Immunization 3 Amount of Obligaton = FILL IN. 4 Market interest rate FILL IN (Decimal). Obligation due in FILL IN. 6 PV (Obligation) = PROGRAM. years 1. To immunize, need to find the duration of the asset and liability, equate them, 10 and solve for optimal weights of investment in the zeros and in the perpetuity. 22 Duration of obligation FILL IN. years Duration of zeros = FILLIN. years 34 Duration of perpetuity = (1+r/r= years PROGRAM. 16 Let w be the percentage of funds invested in the zeros. Then (1-w) = percentage of funds invested in the perpetuity. WRITE FORUMULA WITH NUMBERS (e.g., 10w + 6(1-w) = 13). 19 Setting D(assets) = Dliabilities) yields 22 The above equation yields w= 22 Also, 1-w= FILL IN AS DECIMAL. Do algebra elsewhere. PROGRAM. 24 Therefore, invest 25 and PROGRAM PROGRAM. in zeros in the perpetuity. 27 2. One year has passed, and market rate is still = FILL IN AS PERCENT. 28 As a decimal, the market rate PROGRAM. 29 30 is obligation still fully funded? To answer this question, 3 need to see if amounts invested in assets still equal the new PV (obligation). 32 33 PV[obligation) is now = PROGRAM 34 35 PV (zeros) = PROGRAM. 38 PV (perpetuity) = FILLIN. 33 Perpetuity has paid coupon PROGRAM. 36 So, total PV[asset) is now = PROGRAM. 39 40 is obligation still fully funded? FILL IN ANSWER. 42 ts position still immunized? Need to see if weights need to be changed 43 by recalculating D(assets) and D[liability), equating them, and solving forw. 44 D[liability) now FILLIN. years. 45 Dizeros) now FILLIN. years. -47 (perpetuity) = PROGRAM years. 45 49 so, setting D[asset) = D(liability) yields WRITE FORUMULA WITH NUMBERS, SIMILAR TO ABOVE. 50 This equation yields w= FILL IN AS DECIMAL., 50 (1-w) = PROGRAM. Leave as decimal. 51 52. Based on wand 1-w, is the position still immunized? ANSWER HERE. 53 54 If we need to re-immunize our portfolio, invest PROGRAM. in zeros and PROGRAM. in perpetuity. 55 55
