3. You graduated from USF and got a job at MetLife pension department. Your supe
ID: 2792500 • Letter: 3
Question
3. You graduated from USF and got a job at MetLife pension department. Your supervisor needs your help with some of its liabilities and risk control. The pension fund has a series of liabilities to be paid every 6 months to the pension plan beneficiaries: $2,000,000, $2,200,000, $2,500,000, $3,200,000, $3,700,000, $4,300,000, $4,700,000, and $5,100,000. Your company wishes to construct a portfolio of assets to cover this series of liabilities, such that it is immunized against interest rate risk right now. The company is considering investing in four bonds: (1) a 1-year Treasury Bill with a face value of $1,000 and no coupon, (2) a 2-year Treasury note with a face value of $1,000 and an annual coupon rate of 1.5%, (3) a 3-year Treasury note with a face value of $1,000 and an annual coupon rate of 1.90%, and (4) a 4-year Treasury note with a face value of $1,000 and an annual coupon rate of 2.30%. All Treasury notes make 2 (semi-annual) coupon payments per year. The current APR yield on all bonds is 1.45%. Your boss wants you to find out how many of each of these four Treasury bonds the fund should buy in order to fully fund the liability and be immunized against interest rate risk right now?
Please show step by step for Excel
Explanation / Answer
APR Yield
1.45%
Cash Out Flow
PV of Cash Outflow
6 Month
2,000,000
1,992,789
Year 1
2,200,000
2,168,556
6 Month
2,500,000
2,446,404
Year 2
3,200,000
3,109,180
6 Month
3,700,000
3,568,744
Year 3
4,300,000
4,118,246
6 Month
4,700,000
4,468,244
Year 4
5,100,000
4,814,620
Total Cash Outflow
26,686,783
Coupon
Cash Inflow
6 Month
Year 1
6 Month
Year 2
6 Month
Year 3
6 Month
Year 4
0
Bond 1
1000
1.50%
Bond 2
7.5
7.5
7.5
1007.5
1.90%
Bond 3
8.5
8.5
8.5
8.5
8.5
1008.5
2.30%
Bond 4
11.5
11.5
11.5
11.5
11.5
11.5
11.5
1011.5
Total Cash Inflow
27.5
1027.5
27.5
1027.5
20
1020
11.5
1011.5
PV of Cash Inflow
27.40
1012.81
26.91
998.34
19.29
976.89
10.93
954.90
Total Cash Inflow
4027.47
Total No. of Bonds of $1000 each
6626.19
6627
Total 6627 units of bond each $1000 of different maturity need to be purchased.
APR Yield
1.45%
Cash Out Flow
PV of Cash Outflow
6 Month
2,000,000
1,992,789
Year 1
2,200,000
2,168,556
6 Month
2,500,000
2,446,404
Year 2
3,200,000
3,109,180
6 Month
3,700,000
3,568,744
Year 3
4,300,000
4,118,246
6 Month
4,700,000
4,468,244
Year 4
5,100,000
4,814,620
Total Cash Outflow
26,686,783
Coupon
Cash Inflow
6 Month
Year 1
6 Month
Year 2
6 Month
Year 3
6 Month
Year 4
0
Bond 1
1000
1.50%
Bond 2
7.5
7.5
7.5
1007.5
1.90%
Bond 3
8.5
8.5
8.5
8.5
8.5
1008.5
2.30%
Bond 4
11.5
11.5
11.5
11.5
11.5
11.5
11.5
1011.5
Total Cash Inflow
27.5
1027.5
27.5
1027.5
20
1020
11.5
1011.5
PV of Cash Inflow
27.40
1012.81
26.91
998.34
19.29
976.89
10.93
954.90
Total Cash Inflow
4027.47
Total No. of Bonds of $1000 each
6626.19
6627
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.