Professor Kay Giesecke
Fall 2012 October 30, 2012
Project I Date due: Nov. 13, 2012

Bond Portfolio Management Project (Part I)

You are the manager of SCIEN-INVEST Pension Fund. The date is 10/11/2012 and SCIEN-INVEST must purchase a portfolio of U.S. Treasury Bonds to meet the fund’s projected liabilities in the future. In order to construct this optimal bond portfolio, you need to know the term structure of interest rates as of 10/11/2012. Bond price quotations appear on the following page.

Estimating the term structure of interest rates(Step 1)Adjust the bond price quotes so that they reflect all accrued interest. (Note that Excel can ‘add’ and ‘subtract’ dates).
(Step 2)One approach to estimating the term structure is to calculate the market spot interest rates at each maturity date by constructing a zero coupon bond for each maturity. (Construct a portfolio of two bonds with the same maturity to obtain the payout of a zero-coupon bond of that maturity. If there are no coupon payments remaining before maturity, use each bond individually to compute a spot rate and then take an average of those spot rates as your estimate.) Use the continuous compounding form to calculate spot rates from prices of these zero coupon bonds:
D(t) = e – r ( t) t
Where D(t) is the discount factor at time t, r(t) is the spot rate and t is in units of years. For simplicity assume 365 days in a year. Plot this term structure.
(Step 3) On the advice of a consultant, you decide instead to estimate the term structure of interest rates as a smooth function with a polynomial, using the bond prices adjusted for accrued interest in Step 1. (not using the zero-coupon bond data obtained in Step 2). Extensive testing by the consultant has given some merit to estimation with a 4th order polynomial in time: r(tj)= a0 + a1tj + a2 t2j + a3t3j + a4t4j where the ai’s are the polynomial coefficients. To find the ai’s, use an ordinary least squares procedure involving the price of the bonds where we seek to minimize the sum of squared price errors, with the restriction that a0  0. (That is, suppose Pj(t) is the dirty price of j’th bond, and let Qj(t) be the price using the 4th order polynomial for discounting. Note that Qj(t) will depend upon the values of the coefficients, ai. You should minimize the sum of the squared errors, (Pj - Qj)2 , by changing the coefficients ai.) Assume continuous compounding, and use the Microsoft Excel Solver to find the ‘best fit’ coefficients.
(Step 4) Plot this fitted term structure and give the values of the 5 polynomial coefficients, namely, the 5 ai’s. Compare the spot rates you get from Step 2 and Step 3 and discuss the advantages and disadvantages of both methods.
Hint: You should use Excel’s Solver to estimate the term structure with the following OPTION settings. Solver can be found under the FORMULA Menu, or alternatively under Add Ins under the OPTIONS Menu. The procedure is straightforward. You may also wish to read the explanation in Excel HELP.
The followings are recommended settings in using Solver:
Optimization Time: 3600 Seconds
Iteration: 100Precision: 0.000001
Tolerance: 0.05%Convergence: 0.0001
Tangent, Forward Derivative, Newton’s Method
Note:Start with a0 = a1= a2= a3= a4= 0. Solver may be sensitive to starting values if you start with different values. You may also want to run Solver more than once if you don’t get the optimal solution from Solver in your first trial.

Bond Quotations on October 11, 2012.
Assume all payments fall on the 15th of the month and that coupons are paid semi-annually. The par or face value of all bonds is $100. The coupons are stated as a percentage of the face value. Ask Prices are the prices for buying bonds and the fractional part is quoted in $1/32nds.
Ignore all taxes.
Table 1: Bond Quotation Maturity | Coupon (%) | Quoted Ask Price | 15-Feb-2013 | 4 5/8 | 101 | 22 | 15-Feb-2013 | 7/8 | 100