LE Paste B A $ %? 28 Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. B10 x fx 3 pts 2 3 c F G 1 K L M N Fill in the blanks using excel formulasAssume these are Corporate Bonds for Day Count. For credit you must use: Mice function for Bond b. PV for Bond 2 Using Bond 1 for analysis, Calculate the price of the band at the various Yield-to-maturity required retums) listed using the P function. Then show the actual price change verses the base case yield-to-maturity of 6% Next show the predicted Price change based on Macaulay Duration and Modified Duration Hist: Anchoring the correct cell references will make copying formula's down much easier. Create a line chart showing the change in actual change in price, change in price predicted by Mac Duration and Mod Duration. Be sure to include and label legends, 2 20pts 2 3 200ts 70pes 9 10 11 12 13 12 Bond 2 Bond 6/30/20 6/30/30 15 Band 3 6/30/20 6/30/40 ne 20.0 2 S1.000.00 10.0 ne 30.0 2 $1,000.00 mc 7.00% 8.00% $1,000.00 Bond 4 6/30/20 6/30/30 6/30/25 100 2 $1.000.00 $1.040.00 200% Settlement Date Maturity Date First Call Date Time to Maturity Frequency Face Value Call Price Coapon Rate Ruited Return (YIM) Outed clear pried Current Yield Meild To Call Macaulay Duration Modited Duration 5.00% 6.000 S1,240.00 $1,150.00 19 20 21 22 22 24 25 26 27 28 29 30 31 nd Prise Actual Price ht Predicted By: Mae Duc Med Dur 32 Chartarea Bond Analysis Med To Maturity 1.0% 15% 2.0% 2.39 3.0% 34 LE Paste B A $ %? 28 Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. B10 x fx 3 pts 2 3 c F G 1 K L M N Fill in the blanks using excel formulasAssume these are Corporate Bonds for Day Count. For credit you must use: Mice function for Bond b. PV for Bond 2 Using Bond 1 for analysis, Calculate the price of the band at the various Yield-to-maturity required retums) listed using the P function. Then show the actual price change verses the base case yield-to-maturity of 6% Next show the predicted Price change based on Macaulay Duration and Modified Duration Hist: Anchoring the correct cell references will make copying formula's down much easier. Create a line chart showing the change in actual change in price, change in price predicted by Mac Duration and Mod Duration. Be sure to include and label legends, 2 20pts 2 3 200ts 70pes 9 10 11 12 13 12 Bond 2 Bond 6/30/20 6/30/30 15 Band 3 6/30/20 6/30/40 ne 20.0 2 S1.000.00 10.0 ne 30.0 2 $1,000.00 mc 7.00% 8.00% $1,000.00 Bond 4 6/30/20 6/30/30 6/30/25 100 2 $1.000.00 $1.040.00 200% Settlement Date Maturity Date First Call Date Time to Maturity Frequency Face Value Call Price Coapon Rate Ruited Return (YIM) Outed clear pried Current Yield Meild To Call Macaulay Duration Modited Duration 5.00% 6.000 S1,240.00 $1,150.00 19 20 21 22 22 24 25 26 27 28 29 30 31 nd Prise Actual Price ht Predicted By: Mae Duc Med Dur 32 Chartarea Bond Analysis Med To Maturity 1.0% 15% 2.0% 2.39 3.0% 34