plz i want the excel models and report

--------------------------------------

Data in this file : https://docs.google.com/spreadsheets/d/1zrWv2MWjPaJViXmZNRqt4npNMEMuzuGT/edit?usp=sharing&ouid=112336579795220495374&rtpof=true&sd=true

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+r 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: Ar AP-DXP X 2- 1+r 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. 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+r 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: Ar AP-DXP X 2- 1+r 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