Submit by email: (A) the excel models; (B) a one-page written report with a summary of the major results. The submission deadline is February 27, 2022 by 11:59pm. Note: Make sure that your models are well organized. Have the inputs clustered and color coded; modularize different types of analyses or calculations in different sheets; document the models with good titles, good labels and short but informative/self- explanatory comments. Bonds and Term Structure Consider the data from Yahoo's bond screener that occurred on May 4, 2011 (Settlement Date) for non-callable BB-rated bonds. The data have been provided to you. Answer the following questions: 1. Graph the bond yield to maturity (YTM) on the y-axis of an XY-scatter plot, with the bond to maturity in years on the x-axis. 2. Use a polynomial regression of order three to determine the relation between the YTM and the bond maturity. 3. Identify two possibly mispriced bond issues, one overpriced and one underpriced. For each of these bonds estimate for how much it is mispriced relatively to the values represented by the model of the yield curve estimated in Question 2. 4. Compute the Macauley duration and the modified duration for the following bond issues using the Excel functions ISSUE CINCINNATI BELL INC PIONEER NAT RES CO NEW YORK N Y CITY INDL DEV AGY PILOT BDS 5. Assume that immediately after these calculations were done there was a parallel shift upwards in the term structure such that all rates were increased by 0.20%. a. Compute the actual percentage change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: . Ar -DX P 1+1 Price 105.26 105.21 104.92 105.33 104.99 106 106.5 104.61 105.5 108 104.57 104.79 Issue INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP SEARS ROEBUCK ACCEP CORP BOMBARDIER INC INTERNATIONAL LEASE FIN MTN BE REGIONS FINL CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE DELTA AIRLINES MAY DEPT STORES CO FORD MOTOR CREDIT CO LLC FORD MTR CR CO MIN BE KINDER MORGAN INC INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN CORP DILLARD DEPT STORES INC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP STEEL DYNAMICS INC TESORO CORP INTERNATIONAL LEASE FIN CORP VULCAN MATLS CO EL PASO ENERGY CORP MIN BE NEW YORK NY CITY INDL DEV AGY PILOT BDS INTERNATIONAL LEASE FIN MIN BE CITIZENS COMMUNICATIONS CO INTERNATIONAL LEASE FIN CORP MACYS RETAIL HLDGS INC PULTE GROUP INC STARWOOD HOTELS&RESORTS WRLDWD 104.81 104.73 105.25 109.45 108.44 107.88 107.87 105.25 104.5 109.25 106.07 105 105,64 Coupon(%) Maturity 5.5 4/15/2012 5.65 4/15/2012 5.45 4/15/2012 5.75 4/15/2012 5.5 4/15/2012 6.7 4/15/2012 6.75 5/1/2012 5.3 5/1/2012 6.375 5/15/2012 7.8 6/1/2012 5 6/15/2012 5.2 6/15/2012 5.2 6/15/2012 5 6/15/2012 6.417 7/2/2012 8 7/15/2012 7.5 8/1/2012 7 8/15/2012 6.5 9/1/2012 5.55 9/5/2012 5 9/15/2012 7.85 10/1/2012 5.1 10/15/2012 5.1 10/15/2012 4.8 | 10/15/2012 5.15 10/15/2012 4.85 10/15/2012 7.375 11/1/2012 6.25 11/1/2012 4.9 11/15/2012 5.6 11/30/2012 7.375 12/15/2012 5 1/1/2013 5.25 1/10/2013 6.25 1/15/2013 4.95 1/15/2013 5.875 1/15/2013 6.25 2/15/2013 6.25 2/15/2013 Fitch YTM(%) Ratings 0.042 BB 0.238 BB 0.332 BB 0.21 BB 0.313 BB 0.454 BB 0.29 BB 0.704BB 1.078 BB 0.445 BB 0.928 BB 0.931 BB 0.911 BB 0.792 BB 1.886 BB 0.204 BB 0.747 BB 0.892 BB 0.601 BB 1.609 BB 1.687 BB 1.278 BB 0.921 BB 1.626 BB 0.918 BB 0.925 BB 1.652 BB 1.408 BB 1.102 BB 2.294BB 1.569 BB 0.299 BB 1.743 BB 2.855 BB 1.503 BB 2.435 BB 0.723 BB 0.121 BB 0.936 BB 106.13 104.62 108.88 107.68 103.94 106.3 111.5 105.36 103.95 108 104.19 108.76 111 109.45 PHH CORP INTERNATIONAL LEASE FIN MTN BE AMSOUTH BKN A BIRMINGHAM ALA INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP WILMINGTON TRUST CORP REGIONS FINANCIAL CORP NEW DR HORTON INC INTERNATIONAL LEASE FIN CORP LIBERTY MEDIA CORP NEW SANDISK CORP RHODE ISLAND ST HEALTH & EDL B PUBLIC SCH REV BDS UNITED STATES STL CORP NEW INTERNATIONAL LEASE FIN MTN BE ROYAL CARIBBEAN CRUISES LTD VULCAN MATLS CO DETROIT MICH CTFS PARTN DETROIT RETIREMENT SYS FUN CHESAPEAKE ENERGY CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE WESTINGHOUSE AIR BRAKE CO NEW WINDSTREAM CORP LEUCADIA NATL CORP LEUCADIA NATL CORP MAY DEPT STORES CO CASE NEW HOLLAND INC HESPERIA CALIF CMNTY REDEV AGY TAX ALLOC BDS VENEZUELA REP INTERNATIONAL LEASE FIN MIN BE CENTEX CORP ECHOSTAR DBS CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE ALLEGHENY CNTY PA HOSP DEV AUT REV BDS ROYAL CARIBBEAN CRUISES LTD EL PASO CORP PHILIPPINES REP PULTE GROUP INC SUNGARD DATA SYS INC 106.75 106.24 104.25 105.55 105.28 104.95 104.88 108.75 104.88 106.55 100.88 104.97 107.34 106.09 109.12 108.5 95.44 112.25 106.28 108.3 108.38 110 112.25 110.05 110 110.3 111.26 102.89 105.5 104.12 109.5 110.38 110.45 105.14 106 104.38 110 7.125 3/1/2013 6.375 3/25/2013 4.85 4/1/2013 5.95 4/15/2013 5.7 4/15/2013 4.875 4/15/2013 4.875 4/26/2013 6.875 5/1/2013 5.875 5/1/2013 5.7 5/15/2013 1 5/15/2013 4.25 5/15/2013 5.65 6/1/2013 6 6/15/2013 7 6/15/2013 6.3 6/15/2013 4.503 6/15/2013 7.625 7/15/2013 6.4 7/15/2013 7.5 7/15/2013 7.5 7/15/2013 6.875 7/31/2013 8.125 8/1/2013 7.75 8/15/2013 7 8/15/2013 7.625 8/15/2013 7.75 9/1/2013 3.5 9/1/2013 10.75 9/19/2013 5.625 9/20/2013 5.125 10/1/2013 7 10/1/2013 7 10/1/2013 5.35 10/15/2013 6.625 11/15/2013 5 11/15/2013 6.875 12/1/2013 12 12/12/2013 8.25 1/15/2014 5.25 1/15/2014 4.875 1/15/2014 3.313 BB 2.985 BB 2.572 BB 3.015 BB 2.915 BB 2.283 BB 2.358 BB 2.387 BB 3.345 BB 2.401 BB 0.569 BB 1.764 BB 2.044 BB 3.027 BB 2.569 BB 2.193 BB 6.834 BB 1.943 BB 3.428 BB 3.564 BB 3.528 BB 2.298 BB 2.509 BB 3.176 BB 2.492 BB 2.952 BB 2.748 BB 2.227 BB 8.164 BB 3.804 BB 1.141 BB 2.559 BB 2.529 BB 3.164 BB 4.118 BB 3.197 BB 2.844BB 1.802 BB 1.7 BB 2.746 BB 3.223 BB 126 117.3 106.5 104.25 bonds estimate for how much it is mispriced relatively to the values represented by the model of the yield curve estimated in Question 2. 4. Compute the Macauley duration and the modified duration for the following bond issues using the Excel functions ISSUE CINCINNATI BELL INC PIONEER NAT RES CO NEW YORK N Y CITY INDL DEV AGY PILOT BDS 5. Assume that immediately after these calculations were done there was a parallel shift upwards in the term structure such that all rates were increased by 0.20%. a. Compute the actual percentage change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: b. Compute the actual change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: 6. Perform a portfolio immunization test on the three issues in Question 4. Assume that we start with an interest rate of 7% and the planned holding period is 8 years. For simplicity, round the time maturity of the issues to the nearest whole year. Make sure to show your results graphically. Interpret your results. Open in... plied to move in to... Price 105.26 105.21 104.92 105.33 104.99 106 106.5 104.61 105.5 108 104.57 104.79 Issue INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP SEARS ROEBUCK ACCEP CORP BOMBARDIER INC INTERNATIONAL LEASE FIN MTN BE REGIONS FINL CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE DELTA AIRLINES MAY DEPT STORES CO FORD MOTOR CREDIT CO LLC FORD MTR CR CO MIN BE KINDER MORGAN INC INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN CORP DILLARD DEPT STORES INC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP STEEL DYNAMICS INC TESORO CORP INTERNATIONAL LEASE FIN CORP VULCAN MATLS CO EL PASO ENERGY CORP MIN BE NEW YORK NY CITY INDL DEV AGY PILOT BDS INTERNATIONAL LEASE FIN MIN BE CITIZENS COMMUNICATIONS CO INTERNATIONAL LEASE FIN CORP MACYS RETAIL HLDGS INC PULTE GROUP INC STARWOOD HOTELS&RESORTS WRLDWD 104.81 104.73 105.25 109.45 108.44 107.88 107.87 105.25 104.5 109.25 106.07 105 105,64 Coupon(%) Maturity 5.5 4/15/2012 5.65 4/15/2012 5.45 4/15/2012 5.75 4/15/2012 5.5 4/15/2012 6.7 4/15/2012 6.75 5/1/2012 5.3 5/1/2012 6.375 5/15/2012 7.8 6/1/2012 5 6/15/2012 5.2 6/15/2012 5.2 6/15/2012 5 6/15/2012 6.417 7/2/2012 8 7/15/2012 7.5 8/1/2012 7 8/15/2012 6.5 9/1/2012 5.55 9/5/2012 5 9/15/2012 7.85 10/1/2012 5.1 10/15/2012 5.1 10/15/2012 4.8 | 10/15/2012 5.15 10/15/2012 4.85 10/15/2012 7.375 11/1/2012 6.25 11/1/2012 4.9 11/15/2012 5.6 11/30/2012 7.375 12/15/2012 5 1/1/2013 5.25 1/10/2013 6.25 1/15/2013 4.95 1/15/2013 5.875 1/15/2013 6.25 2/15/2013 6.25 2/15/2013 Fitch YTM(%) Ratings 0.042 BB 0.238 BB 0.332 BB 0.21 BB 0.313 BB 0.454 BB 0.29 BB 0.704BB 1.078 BB 0.445 BB 0.928 BB 0.931 BB 0.911 BB 0.792 BB 1.886 BB 0.204 BB 0.747 BB 0.892 BB 0.601 BB 1.609 BB 1.687 BB 1.278 BB 0.921 BB 1.626 BB 0.918 BB 0.925 BB 1.652 BB 1.408 BB 1.102 BB 2.294BB 1.569 BB 0.299 BB 1.743 BB 2.855 BB 1.503 BB 2.435 BB 0.723 BB 0.121 BB 0.936 BB 106.13 104.62 108.88 107.68 103.94 106.3 111.5 105.36 103.95 108 104.19 108.76 111 109.45 PHH CORP INTERNATIONAL LEASE FIN MTN BE AMSOUTH BKN A BIRMINGHAM ALA INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP WILMINGTON TRUST CORP REGIONS FINANCIAL CORP NEW DR HORTON INC INTERNATIONAL LEASE FIN CORP LIBERTY MEDIA CORP NEW SANDISK CORP RHODE ISLAND ST HEALTH & EDL B PUBLIC SCH REV BDS UNITED STATES STL CORP NEW INTERNATIONAL LEASE FIN MTN BE ROYAL CARIBBEAN CRUISES LTD VULCAN MATLS CO DETROIT MICH CTFS PARTN DETROIT RETIREMENT SYS FUN CHESAPEAKE ENERGY CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE WESTINGHOUSE AIR BRAKE CO NEW WINDSTREAM CORP LEUCADIA NATL CORP LEUCADIA NATL CORP MAY DEPT STORES CO CASE NEW HOLLAND INC HESPERIA CALIF CMNTY REDEV AGY TAX ALLOC BDS VENEZUELA REP INTERNATIONAL LEASE FIN MIN BE CENTEX CORP ECHOSTAR DBS CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE ALLEGHENY CNTY PA HOSP DEV AUT REV BDS ROYAL CARIBBEAN CRUISES LTD EL PASO CORP PHILIPPINES REP PULTE GROUP INC SUNGARD DATA SYS INC 106.75 106.24 104.25 105.55 105.28 104.95 104.88 108.75 104.88 106.55 100.88 104.97 107.34 106.09 109.12 108.5 95.44 112.25 106.28 108.3 108.38 110 112.25 110.05 110 110.3 111.26 102.89 105.5 104.12 109.5 110.38 110.45 105.14 106 104.38 110 7.125 3/1/2013 6.375 3/25/2013 4.85 4/1/2013 5.95 4/15/2013 5.7 4/15/2013 4.875 4/15/2013 4.875 4/26/2013 6.875 5/1/2013 5.875 5/1/2013 5.7 5/15/2013 1 5/15/2013 4.25 5/15/2013 5.65 6/1/2013 6 6/15/2013 7 6/15/2013 6.3 6/15/2013 4.503 6/15/2013 7.625 7/15/2013 6.4 7/15/2013 7.5 7/15/2013 7.5 7/15/2013 6.875 7/31/2013 8.125 8/1/2013 7.75 8/15/2013 7 8/15/2013 7.625 8/15/2013 7.75 9/1/2013 3.5 9/1/2013 10.75 9/19/2013 5.625 9/20/2013 5.125 10/1/2013 7 10/1/2013 7 10/1/2013 5.35 10/15/2013 6.625 11/15/2013 5 11/15/2013 6.875 12/1/2013 12 12/12/2013 8.25 1/15/2014 5.25 1/15/2014 4.875 1/15/2014 3.313 BB 2.985 BB 2.572 BB 3.015 BB 2.915 BB 2.283 BB 2.358 BB 2.387 BB 3.345 BB 2.401 BB 0.569 BB 1.764 BB 2.044 BB 3.027 BB 2.569 BB 2.193 BB 6.834 BB 1.943 BB 3.428 BB 3.564 BB 3.528 BB 2.298 BB 2.509 BB 3.176 BB 2.492 BB 2.952 BB 2.748 BB 2.227 BB 8.164 BB 3.804 BB 1.141 BB 2.559 BB 2.529 BB 3.164 BB 4.118 BB 3.197 BB 2.844BB 1.802 BB 1.7 BB 2.746 BB 3.223 BB 126 117.3 106.5 104.25 2 102.3 89.48 98.05 111.5 110.75 130 121.08 94.17 112 112.45 109.25 AMSOUTH BKN A BIRMINGHAM ALA DETROIT MICH GEN OBLIG BDS DETROIT MICH GO BDS FORD MOTOR CREDIT CO LLC FRONTIER COMMUNICATIONS CORP FORD MOTOR CREDIT CO LLC PNM RES INC HARRISBURG PA PKG AUTH PKG REV PKG REV BDS ECHOSTAR DBS CORP MAY DEPT STORES CO CENTEX CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE REGIONS FINANCIAL CORP NEW MACYS RETAIL HLDGS INC ROYAL CARIBBEAN CRUISES LTD FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP LEUCADIA NATL CORP ZIONS BANCORPORATION ETHAN ALLEN GLOBAL INC AES CORP PENNEY JC INC MTN BE ITT CORP NEV ZIONS BANCORPORATION CENTRAL PLAINS ENERGY PROJ NEB GAS PROJECT REV BDS CMS ENERGY CORP GEON CO KINDER MORGAN FINANCE CORP ULC CASE CORP DR HORTON INC MOHAWK INDS INC BANCO CRUZEIRO DO SUL SA ECHOSTAR DBS CORP EL PASO CORP SERVICE CORP INTL DETROIT MICH GO BDS DETROIT MICH GO REF BDS AES CORP DR HORTON INC HEALTH MGMT ASSOC INC NEW 107.5 105.58 105.38 119.2 127.25 108 114.5 113.88 105.75 102 110.75 111 114.45 107 5.2 4/1/2015 4.53 4/1/2015 5 4/1/2015 7 4/15/2015 7.875 4/15/2015 12 5/15/2015 9.25 5/15/2015 3.7 5/15/2015 7.75 5/31/2015 7.5 6/1/2015 5.25 6/15/2015 6.75 6/15/2015 6.5 6/15/2015 5.75 6/15/2015 8.375 7/15/2015 11.875 7/15/2015 5.625 9/15/2015 8.625 9/15/2015 8.125 9/15/2015 6 9/15/2015 5.375 10/1/2015 7.75 10/15/2015 6.875 10/15/2015 7.375 11/15/2015 5.5 11/16/2015 5 12/1/2015 6.875 12/15/2015 7.5 12/15/2015 5.7 1/5/2016 7.25 1/15/2016 5.625 1/15/2016 6.875 1/15/2016 8.25 1/20/2016 7.125 2/1/2016 8.25 2/15/2016 6.75 4/1/2016 5 5 4/1/2016 5.25 4/1/2016 9.75 4/15/2016 6.5 4/15/2016 6.125 4/15/2016 4.552 BB 7.683 BB 5.559 BB 3.844BB 4.86 BB 3.912 BB 3.606 BB 5.319 BB 4.502 BB 4.159 BB 2.859 BB 4.729 BB 4.987 BB 4.314 BB 3.44BB 4.672 BB 3.632 BB 4.904 BB 4.59 BB 4.536 BB 4.866 BB 5.03 BB 4.15 BB 3.877 BB 3.809 BB 4.991 BB 3.35 BB 5.281 BB 4.044 BB 4.54 BB 4.726 BB 4.439 BB 7.859 BB 4.918 BB 3.974 BB 4.303 BB 6.476 BB 4.228 BB 5.656 BB 5.115 BB 4.643 BB 100.03 115 109 107 111.38 103.75 110.25 101.5 109.25 118.5 110.75 93.85 104.5 117.5 106 106.5 104.05 108 113 91.42 109.5 112.35 116.5 108 114.5 111.25 108.97 112 115 115.38 111 STORA ENSO CORP CENTEX CORP OWENS-BROCKWAY GLASS CONTAINER HARRISBURG PA PKG AUTH PKG REV PKG REV BDS ROYAL CARIBBEAN CRUISES LTD UNIVERSAL HLTH SVCS INC JABIL CIRCUIT INC PIONEER NAT RES CO PENNEY JC INC CONSTELLATION BRANDS INC SEAGATE TECHNOLOGY HDD HLDGS MAY DEPT STORES CO PEABODY ENERGY CORP FEDERATED RETAIL HLDGS INC SPRINT NEXTEL CORP FORD MOTOR CREDIT CO LLC NATIONAL POWER CORP NATIONAL POWER CORP NEW YORK N Y CITY INDL DEV AGY PILOT BDS PHILIPPINE LONG DIS TEL MTN BE INTERNATIONAL LEASE FIN CORP PIONEER NAT RES CO PENNEY J CINC GOL FINANCE FRONTIER COMMUNICATIONS CORP CONSTELLATION BRANDS INC FIRST INDL LP REPUBLICA ORIENTAL DEL URUGUAY UNITED STATES STL CORP NEW EL PASO CORP SERVICE CORP INTL GUAM GOVT WTRWKS AUTH WTR & WA REV BDS REPUBLIC OF TURKEY LIMITED BRANDS INC MACYS RETAIL HLDGS INC CMS ENERGY CORP FORD MOTOR CREDIT CO LLC SPRINT NEXTEL CORP INTERNATIONAL LEASE FIN CORP TENNESSEE ENERGY ACQUISITION C GAS REV BDS AES CORP 104 118.25 129.81 129.81 100.5 118.33 115.5 6.404 4/15/2016 6,5 5/1/2016 7.375 5/15/2016 4 5/15/2016 7.25 6/15/2016 7.125 6/30/2016 7.75 7/15/2016 5.875 7/15/2016 7.65 8/15/2016 7.25 9/1/2016 6.8 10/1/2016 7.45 10/15/2016 7.375 11/1/2016 5.9 12/1/2016 6 12/1/2016 8 12/15/2016 8.4 12/15/2016 8.4 12/15/2016 4.1 1/1/2017 8.35 3/6/2017 8.75 3/15/2017 6.65 3/15/2017 7.95 4/1/2017 7.5 4/3/2017 8.25 4/15/2017 7.25 5/15/2017 5.95 5/15/2017 9.25 5/17/2017 6.05 6/1/2017 7 6/15/2017 7 6/15/2017 4.25 7/1/2017 7.5 7/14/2017 6.9 7/15/2017 7.45 7/15/2017 6.55 7/17/2017 6.625 8/15/2017 8.375 8/15/2017 8.875 9/1/2017 5.25 9/1/2017 8 10/15/2017 5.461 BB 4.689 BB 4.469 BB 5.994 BB 5.119 BB 4.425 BB 4.192 BB 4.151 BB 4.538 BB 4.833BB 4.897 BB 4.918 BB 4.218 BB 3.703 BB 5.166 BB 4.313 BB 2.664 BB 2.664 BB 4 BB 4.729 BB 5.616 BB 4.499 BB 5.175 BB 5.834 BB 6.026 BB 5.075 BB 5.555 BB 3.401 BB 5.32 BB 4.448 BB 111 114 108.25 111 111.2 102 131.75 103.75 113.56 110.38 103.88 119.52 109.25 5.012 BB 3.545 BB 3.927 BB 5.14 BB 4.471 BB 116 113.07 112 115.75 117 105.34 111 4.143 BB 4.419 BB 5.39 BB 5.646 BB 4.279 BB 5.926 BB Submit by email: (A) the excel models; (B) a one-page written report with a summary of the major results. The submission deadline is February 27, 2022 by 11:59pm. Note: Make sure that your models are well organized. Have the inputs clustered and color coded; modularize different types of analyses or calculations in different sheets; document the models with good titles, good labels and short but informative/self- explanatory comments. Bonds and Term Structure Consider the data from Yahoo's bond screener that occurred on May 4, 2011 (Settlement Date) for non-callable BB-rated bonds. The data have been provided to you. Answer the following questions: 1. Graph the bond yield to maturity (YTM) on the y-axis of an XY-scatter plot, with the bond to maturity in years on the x-axis. 2. Use a polynomial regression of order three to determine the relation between the YTM and the bond maturity. 3. Identify two possibly mispriced bond issues, one overpriced and one underpriced. For each of these bonds estimate for how much it is mispriced relatively to the values represented by the model of the yield curve estimated in Question 2. 4. Compute the Macauley duration and the modified duration for the following bond issues using the Excel functions ISSUE CINCINNATI BELL INC PIONEER NAT RES CO NEW YORK N Y CITY INDL DEV AGY PILOT BDS 5. Assume that immediately after these calculations were done there was a parallel shift upwards in the term structure such that all rates were increased by 0.20%. a. Compute the actual percentage change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: . Ar -DX P 1+1 Price 105.26 105.21 104.92 105.33 104.99 106 106.5 104.61 105.5 108 104.57 104.79 Issue INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP SEARS ROEBUCK ACCEP CORP BOMBARDIER INC INTERNATIONAL LEASE FIN MTN BE REGIONS FINL CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE DELTA AIRLINES MAY DEPT STORES CO FORD MOTOR CREDIT CO LLC FORD MTR CR CO MIN BE KINDER MORGAN INC INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN CORP DILLARD DEPT STORES INC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP STEEL DYNAMICS INC TESORO CORP INTERNATIONAL LEASE FIN CORP VULCAN MATLS CO EL PASO ENERGY CORP MIN BE NEW YORK NY CITY INDL DEV AGY PILOT BDS INTERNATIONAL LEASE FIN MIN BE CITIZENS COMMUNICATIONS CO INTERNATIONAL LEASE FIN CORP MACYS RETAIL HLDGS INC PULTE GROUP INC STARWOOD HOTELS&RESORTS WRLDWD 104.81 104.73 105.25 109.45 108.44 107.88 107.87 105.25 104.5 109.25 106.07 105 105,64 Coupon(%) Maturity 5.5 4/15/2012 5.65 4/15/2012 5.45 4/15/2012 5.75 4/15/2012 5.5 4/15/2012 6.7 4/15/2012 6.75 5/1/2012 5.3 5/1/2012 6.375 5/15/2012 7.8 6/1/2012 5 6/15/2012 5.2 6/15/2012 5.2 6/15/2012 5 6/15/2012 6.417 7/2/2012 8 7/15/2012 7.5 8/1/2012 7 8/15/2012 6.5 9/1/2012 5.55 9/5/2012 5 9/15/2012 7.85 10/1/2012 5.1 10/15/2012 5.1 10/15/2012 4.8 | 10/15/2012 5.15 10/15/2012 4.85 10/15/2012 7.375 11/1/2012 6.25 11/1/2012 4.9 11/15/2012 5.6 11/30/2012 7.375 12/15/2012 5 1/1/2013 5.25 1/10/2013 6.25 1/15/2013 4.95 1/15/2013 5.875 1/15/2013 6.25 2/15/2013 6.25 2/15/2013 Fitch YTM(%) Ratings 0.042 BB 0.238 BB 0.332 BB 0.21 BB 0.313 BB 0.454 BB 0.29 BB 0.704BB 1.078 BB 0.445 BB 0.928 BB 0.931 BB 0.911 BB 0.792 BB 1.886 BB 0.204 BB 0.747 BB 0.892 BB 0.601 BB 1.609 BB 1.687 BB 1.278 BB 0.921 BB 1.626 BB 0.918 BB 0.925 BB 1.652 BB 1.408 BB 1.102 BB 2.294BB 1.569 BB 0.299 BB 1.743 BB 2.855 BB 1.503 BB 2.435 BB 0.723 BB 0.121 BB 0.936 BB 106.13 104.62 108.88 107.68 103.94 106.3 111.5 105.36 103.95 108 104.19 108.76 111 109.45 PHH CORP INTERNATIONAL LEASE FIN MTN BE AMSOUTH BKN A BIRMINGHAM ALA INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP WILMINGTON TRUST CORP REGIONS FINANCIAL CORP NEW DR HORTON INC INTERNATIONAL LEASE FIN CORP LIBERTY MEDIA CORP NEW SANDISK CORP RHODE ISLAND ST HEALTH & EDL B PUBLIC SCH REV BDS UNITED STATES STL CORP NEW INTERNATIONAL LEASE FIN MTN BE ROYAL CARIBBEAN CRUISES LTD VULCAN MATLS CO DETROIT MICH CTFS PARTN DETROIT RETIREMENT SYS FUN CHESAPEAKE ENERGY CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE WESTINGHOUSE AIR BRAKE CO NEW WINDSTREAM CORP LEUCADIA NATL CORP LEUCADIA NATL CORP MAY DEPT STORES CO CASE NEW HOLLAND INC HESPERIA CALIF CMNTY REDEV AGY TAX ALLOC BDS VENEZUELA REP INTERNATIONAL LEASE FIN MIN BE CENTEX CORP ECHOSTAR DBS CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE ALLEGHENY CNTY PA HOSP DEV AUT REV BDS ROYAL CARIBBEAN CRUISES LTD EL PASO CORP PHILIPPINES REP PULTE GROUP INC SUNGARD DATA SYS INC 106.75 106.24 104.25 105.55 105.28 104.95 104.88 108.75 104.88 106.55 100.88 104.97 107.34 106.09 109.12 108.5 95.44 112.25 106.28 108.3 108.38 110 112.25 110.05 110 110.3 111.26 102.89 105.5 104.12 109.5 110.38 110.45 105.14 106 104.38 110 7.125 3/1/2013 6.375 3/25/2013 4.85 4/1/2013 5.95 4/15/2013 5.7 4/15/2013 4.875 4/15/2013 4.875 4/26/2013 6.875 5/1/2013 5.875 5/1/2013 5.7 5/15/2013 1 5/15/2013 4.25 5/15/2013 5.65 6/1/2013 6 6/15/2013 7 6/15/2013 6.3 6/15/2013 4.503 6/15/2013 7.625 7/15/2013 6.4 7/15/2013 7.5 7/15/2013 7.5 7/15/2013 6.875 7/31/2013 8.125 8/1/2013 7.75 8/15/2013 7 8/15/2013 7.625 8/15/2013 7.75 9/1/2013 3.5 9/1/2013 10.75 9/19/2013 5.625 9/20/2013 5.125 10/1/2013 7 10/1/2013 7 10/1/2013 5.35 10/15/2013 6.625 11/15/2013 5 11/15/2013 6.875 12/1/2013 12 12/12/2013 8.25 1/15/2014 5.25 1/15/2014 4.875 1/15/2014 3.313 BB 2.985 BB 2.572 BB 3.015 BB 2.915 BB 2.283 BB 2.358 BB 2.387 BB 3.345 BB 2.401 BB 0.569 BB 1.764 BB 2.044 BB 3.027 BB 2.569 BB 2.193 BB 6.834 BB 1.943 BB 3.428 BB 3.564 BB 3.528 BB 2.298 BB 2.509 BB 3.176 BB 2.492 BB 2.952 BB 2.748 BB 2.227 BB 8.164 BB 3.804 BB 1.141 BB 2.559 BB 2.529 BB 3.164 BB 4.118 BB 3.197 BB 2.844BB 1.802 BB 1.7 BB 2.746 BB 3.223 BB 126 117.3 106.5 104.25 bonds estimate for how much it is mispriced relatively to the values represented by the model of the yield curve estimated in Question 2. 4. Compute the Macauley duration and the modified duration for the following bond issues using the Excel functions ISSUE CINCINNATI BELL INC PIONEER NAT RES CO NEW YORK N Y CITY INDL DEV AGY PILOT BDS 5. Assume that immediately after these calculations were done there was a parallel shift upwards in the term structure such that all rates were increased by 0.20%. a. Compute the actual percentage change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: b. Compute the actual change in the prices of the bonds listed in Question 4 by estimating the new prices for the new interest rate and compare the values with the those obtained from: 6. Perform a portfolio immunization test on the three issues in Question 4. Assume that we start with an interest rate of 7% and the planned holding period is 8 years. For simplicity, round the time maturity of the issues to the nearest whole year. Make sure to show your results graphically. Interpret your results. Open in... plied to move in to... Price 105.26 105.21 104.92 105.33 104.99 106 106.5 104.61 105.5 108 104.57 104.79 Issue INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP SEARS ROEBUCK ACCEP CORP BOMBARDIER INC INTERNATIONAL LEASE FIN MTN BE REGIONS FINL CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE DELTA AIRLINES MAY DEPT STORES CO FORD MOTOR CREDIT CO LLC FORD MTR CR CO MIN BE KINDER MORGAN INC INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN CORP DILLARD DEPT STORES INC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP STEEL DYNAMICS INC TESORO CORP INTERNATIONAL LEASE FIN CORP VULCAN MATLS CO EL PASO ENERGY CORP MIN BE NEW YORK NY CITY INDL DEV AGY PILOT BDS INTERNATIONAL LEASE FIN MIN BE CITIZENS COMMUNICATIONS CO INTERNATIONAL LEASE FIN CORP MACYS RETAIL HLDGS INC PULTE GROUP INC STARWOOD HOTELS&RESORTS WRLDWD 104.81 104.73 105.25 109.45 108.44 107.88 107.87 105.25 104.5 109.25 106.07 105 105,64 Coupon(%) Maturity 5.5 4/15/2012 5.65 4/15/2012 5.45 4/15/2012 5.75 4/15/2012 5.5 4/15/2012 6.7 4/15/2012 6.75 5/1/2012 5.3 5/1/2012 6.375 5/15/2012 7.8 6/1/2012 5 6/15/2012 5.2 6/15/2012 5.2 6/15/2012 5 6/15/2012 6.417 7/2/2012 8 7/15/2012 7.5 8/1/2012 7 8/15/2012 6.5 9/1/2012 5.55 9/5/2012 5 9/15/2012 7.85 10/1/2012 5.1 10/15/2012 5.1 10/15/2012 4.8 | 10/15/2012 5.15 10/15/2012 4.85 10/15/2012 7.375 11/1/2012 6.25 11/1/2012 4.9 11/15/2012 5.6 11/30/2012 7.375 12/15/2012 5 1/1/2013 5.25 1/10/2013 6.25 1/15/2013 4.95 1/15/2013 5.875 1/15/2013 6.25 2/15/2013 6.25 2/15/2013 Fitch YTM(%) Ratings 0.042 BB 0.238 BB 0.332 BB 0.21 BB 0.313 BB 0.454 BB 0.29 BB 0.704BB 1.078 BB 0.445 BB 0.928 BB 0.931 BB 0.911 BB 0.792 BB 1.886 BB 0.204 BB 0.747 BB 0.892 BB 0.601 BB 1.609 BB 1.687 BB 1.278 BB 0.921 BB 1.626 BB 0.918 BB 0.925 BB 1.652 BB 1.408 BB 1.102 BB 2.294BB 1.569 BB 0.299 BB 1.743 BB 2.855 BB 1.503 BB 2.435 BB 0.723 BB 0.121 BB 0.936 BB 106.13 104.62 108.88 107.68 103.94 106.3 111.5 105.36 103.95 108 104.19 108.76 111 109.45 PHH CORP INTERNATIONAL LEASE FIN MTN BE AMSOUTH BKN A BIRMINGHAM ALA INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN CORP WILMINGTON TRUST CORP REGIONS FINANCIAL CORP NEW DR HORTON INC INTERNATIONAL LEASE FIN CORP LIBERTY MEDIA CORP NEW SANDISK CORP RHODE ISLAND ST HEALTH & EDL B PUBLIC SCH REV BDS UNITED STATES STL CORP NEW INTERNATIONAL LEASE FIN MTN BE ROYAL CARIBBEAN CRUISES LTD VULCAN MATLS CO DETROIT MICH CTFS PARTN DETROIT RETIREMENT SYS FUN CHESAPEAKE ENERGY CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE WESTINGHOUSE AIR BRAKE CO NEW WINDSTREAM CORP LEUCADIA NATL CORP LEUCADIA NATL CORP MAY DEPT STORES CO CASE NEW HOLLAND INC HESPERIA CALIF CMNTY REDEV AGY TAX ALLOC BDS VENEZUELA REP INTERNATIONAL LEASE FIN MIN BE CENTEX CORP ECHOSTAR DBS CORP FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP INTERNATIONAL LEASE FIN MIN BE ALLEGHENY CNTY PA HOSP DEV AUT REV BDS ROYAL CARIBBEAN CRUISES LTD EL PASO CORP PHILIPPINES REP PULTE GROUP INC SUNGARD DATA SYS INC 106.75 106.24 104.25 105.55 105.28 104.95 104.88 108.75 104.88 106.55 100.88 104.97 107.34 106.09 109.12 108.5 95.44 112.25 106.28 108.3 108.38 110 112.25 110.05 110 110.3 111.26 102.89 105.5 104.12 109.5 110.38 110.45 105.14 106 104.38 110 7.125 3/1/2013 6.375 3/25/2013 4.85 4/1/2013 5.95 4/15/2013 5.7 4/15/2013 4.875 4/15/2013 4.875 4/26/2013 6.875 5/1/2013 5.875 5/1/2013 5.7 5/15/2013 1 5/15/2013 4.25 5/15/2013 5.65 6/1/2013 6 6/15/2013 7 6/15/2013 6.3 6/15/2013 4.503 6/15/2013 7.625 7/15/2013 6.4 7/15/2013 7.5 7/15/2013 7.5 7/15/2013 6.875 7/31/2013 8.125 8/1/2013 7.75 8/15/2013 7 8/15/2013 7.625 8/15/2013 7.75 9/1/2013 3.5 9/1/2013 10.75 9/19/2013 5.625 9/20/2013 5.125 10/1/2013 7 10/1/2013 7 10/1/2013 5.35 10/15/2013 6.625 11/15/2013 5 11/15/2013 6.875 12/1/2013 12 12/12/2013 8.25 1/15/2014 5.25 1/15/2014 4.875 1/15/2014 3.313 BB 2.985 BB 2.572 BB 3.015 BB 2.915 BB 2.283 BB 2.358 BB 2.387 BB 3.345 BB 2.401 BB 0.569 BB 1.764 BB 2.044 BB 3.027 BB 2.569 BB 2.193 BB 6.834 BB 1.943 BB 3.428 BB 3.564 BB 3.528 BB 2.298 BB 2.509 BB 3.176 BB 2.492 BB 2.952 BB 2.748 BB 2.227 BB 8.164 BB 3.804 BB 1.141 BB 2.559 BB 2.529 BB 3.164 BB 4.118 BB 3.197 BB 2.844BB 1.802 BB 1.7 BB 2.746 BB 3.223 BB 126 117.3 106.5 104.25 2 102.3 89.48 98.05 111.5 110.75 130 121.08 94.17 112 112.45 109.25 AMSOUTH BKN A BIRMINGHAM ALA DETROIT MICH GEN OBLIG BDS DETROIT MICH GO BDS FORD MOTOR CREDIT CO LLC FRONTIER COMMUNICATIONS CORP FORD MOTOR CREDIT CO LLC PNM RES INC HARRISBURG PA PKG AUTH PKG REV PKG REV BDS ECHOSTAR DBS CORP MAY DEPT STORES CO CENTEX CORP INTERNATIONAL LEASE FIN MTN BE INTERNATIONAL LEASE FIN MTN BE REGIONS FINANCIAL CORP NEW MACYS RETAIL HLDGS INC ROYAL CARIBBEAN CRUISES LTD FORD MOTOR CREDIT CO LLC INTERNATIONAL LEASE FIN CORP LEUCADIA NATL CORP ZIONS BANCORPORATION ETHAN ALLEN GLOBAL INC AES CORP PENNEY JC INC MTN BE ITT CORP NEV ZIONS BANCORPORATION CENTRAL PLAINS ENERGY PROJ NEB GAS PROJECT REV BDS CMS ENERGY CORP GEON CO KINDER MORGAN FINANCE CORP ULC CASE CORP DR HORTON INC MOHAWK INDS INC BANCO CRUZEIRO DO SUL SA ECHOSTAR DBS CORP EL PASO CORP SERVICE CORP INTL DETROIT MICH GO BDS DETROIT MICH GO REF BDS AES CORP DR HORTON INC HEALTH MGMT ASSOC INC NEW 107.5 105.58 105.38 119.2 127.25 108 114.5 113.88 105.75 102 110.75 111 114.45 107 5.2 4/1/2015 4.53 4/1/2015 5 4/1/2015 7 4/15/2015 7.875 4/15/2015 12 5/15/2015 9.25 5/15/2015 3.7 5/15/2015 7.75 5/31/2015 7.5 6/1/2015 5.25 6/15/2015 6.75 6/15/2015 6.5 6/15/2015 5.75 6/15/2015 8.375 7/15/2015 11.875 7/15/2015 5.625 9/15/2015 8.625 9/15/2015 8.125 9/15/2015 6 9/15/2015 5.375 10/1/2015 7.75 10/15/2015 6.875 10/15/2015 7.375 11/15/2015 5.5 11/16/2015 5 12/1/2015 6.875 12/15/2015 7.5 12/15/2015 5.7 1/5/2016 7.25 1/15/2016 5.625 1/15/2016 6.875 1/15/2016 8.25 1/20/2016 7.125 2/1/2016 8.25 2/15/2016 6.75 4/1/2016 5 5 4/1/2016 5.25 4/1/2016 9.75 4/15/2016 6.5 4/15/2016 6.125 4/15/2016 4.552 BB 7.683 BB 5.559 BB 3.844BB 4.86 BB 3.912 BB 3.606 BB 5.319 BB 4.502 BB 4.159 BB 2.859 BB 4.729 BB 4.987 BB 4.314 BB 3.44BB 4.672 BB 3.632 BB 4.904 BB 4.59 BB 4.536 BB 4.866 BB 5.03 BB 4.15 BB 3.877 BB 3.809 BB 4.991 BB 3.35 BB 5.281 BB 4.044 BB 4.54 BB 4.726 BB 4.439 BB 7.859 BB 4.918 BB 3.974 BB 4.303 BB 6.476 BB 4.228 BB 5.656 BB 5.115 BB 4.643 BB 100.03 115 109 107 111.38 103.75 110.25 101.5 109.25 118.5 110.75 93.85 104.5 117.5 106 106.5 104.05 108 113 91.42 109.5 112.35 116.5 108 114.5 111.25 108.97 112 115 115.38 111 STORA ENSO CORP CENTEX CORP OWENS-BROCKWAY GLASS CONTAINER HARRISBURG PA PKG AUTH PKG REV PKG REV BDS ROYAL CARIBBEAN CRUISES LTD UNIVERSAL HLTH SVCS INC JABIL CIRCUIT INC PIONEER NAT RES CO PENNEY JC INC CONSTELLATION BRANDS INC SEAGATE TECHNOLOGY HDD HLDGS MAY DEPT STORES CO PEABODY ENERGY CORP FEDERATED RETAIL HLDGS INC SPRINT NEXTEL CORP FORD MOTOR CREDIT CO LLC NATIONAL POWER CORP NATIONAL POWER CORP NEW YORK N Y CITY INDL DEV AGY PILOT BDS PHILIPPINE LONG DIS TEL MTN BE INTERNATIONAL LEASE FIN CORP PIONEER NAT RES CO PENNEY J CINC GOL FINANCE FRONTIER COMMUNICATIONS CORP CONSTELLATION BRANDS INC FIRST INDL LP REPUBLICA ORIENTAL DEL URUGUAY UNITED STATES STL CORP NEW EL PASO CORP SERVICE CORP INTL GUAM GOVT WTRWKS AUTH WTR & WA REV BDS REPUBLIC OF TURKEY LIMITED BRANDS INC MACYS RETAIL HLDGS INC CMS ENERGY CORP FORD MOTOR CREDIT CO LLC SPRINT NEXTEL CORP INTERNATIONAL LEASE FIN CORP TENNESSEE ENERGY ACQUISITION C GAS REV BDS AES CORP 104 118.25 129.81 129.81 100.5 118.33 115.5 6.404 4/15/2016 6,5 5/1/2016 7.375 5/15/2016 4 5/15/2016 7.25 6/15/2016 7.125 6/30/2016 7.75 7/15/2016 5.875 7/15/2016 7.65 8/15/2016 7.25 9/1/2016 6.8 10/1/2016 7.45 10/15/2016 7.375 11/1/2016 5.9 12/1/2016 6 12/1/2016 8 12/15/2016 8.4 12/15/2016 8.4 12/15/2016 4.1 1/1/2017 8.35 3/6/2017 8.75 3/15/2017 6.65 3/15/2017 7.95 4/1/2017 7.5 4/3/2017 8.25 4/15/2017 7.25 5/15/2017 5.95 5/15/2017 9.25 5/17/2017 6.05 6/1/2017 7 6/15/2017 7 6/15/2017 4.25 7/1/2017 7.5 7/14/2017 6.9 7/15/2017 7.45 7/15/2017 6.55 7/17/2017 6.625 8/15/2017 8.375 8/15/2017 8.875 9/1/2017 5.25 9/1/2017 8 10/15/2017 5.461 BB 4.689 BB 4.469 BB 5.994 BB 5.119 BB 4.425 BB 4.192 BB 4.151 BB 4.538 BB 4.833BB 4.897 BB 4.918 BB 4.218 BB 3.703 BB 5.166 BB 4.313 BB 2.664 BB 2.664 BB 4 BB 4.729 BB 5.616 BB 4.499 BB 5.175 BB 5.834 BB 6.026 BB 5.075 BB 5.555 BB 3.401 BB 5.32 BB 4.448 BB 111 114 108.25 111 111.2 102 131.75 103.75 113.56 110.38 103.88 119.52 109.25 5.012 BB 3.545 BB 3.927 BB 5.14 BB 4.471 BB 116 113.07 112 115.75 117 105.34 111 4.143 BB 4.419 BB 5.39 BB 5.646 BB 4.279 BB 5.926 BB