I need to graph the result in CH 9 , in the second sheet as an extra credit

it should looks like the last graph in homework 6

Typical (Straight) Bond Time 0: Time N: Times 1 though N: Present: The bond has a value V or price P Maturity: The bond is expected to pay M (maturity value = par value, face Coupon periods: The bond is expected to pay interest payments INT calcu N = number of years before the bond matures INT = coupon payment = dollars of interest paid each year (Coupon rate x Par value) M = par or face, value of the bond to be paid off at maturity What does rd depend on? value = par value, face value) est payments INT calculated as Coupon rate times Par value Par value) PROBLEM 1: Value a straight bond on coupon dates using PV Consider a 30-year corporate bond that has 20 years remaining to maturity, 8% fixed coupon rate, pays coupons semi-annually What should be the maximum price you are willing to pay for this bond if your required return is 9% annually? What should be that price half a year from now if your required return does not change? Confirm that your return for the next half a year given price changes equals to your required return. Time to maturity Coupons Rate Coupons Frerquency Face Value Required Return Bond Value ( price ) 20 years 8% annual 2 times a year 1000 9% annual Time to maturity Coupons Rate Coupons Frerquency Face Value Required Return $907.99 Bond Value ( price ) 19.5 years 8% annual 2 times a year 1000 9% annual $908.85 How does the value of this bond change as it approaches maturity? Construct a data table to show the value against various tim Time to maturity 20 Bond Value ( price ) 18 16 14 12 10 $907.99 Return from holding this bond for half a year return from change in price 0.095% return from coupon 4.405% PROBLEM 2: Value a straight bond on coupon dates and outside of coupon dates using PRICE Consider the same bond as in the example above. Assume a settlement date (date when you buy the bond) as today's date. What should be the maximum price you are willing to pay for this bond if your required return is 9% annually? What should be that price two months from the settlement if your required return does not change? Notice that the PRICE function returns what we call a "Clean price"--the price without accrued interest. Calculate the "Dirty price"--the invoice price with accrued interest--the price you would actually pay two months from today. in two months Settelment date 6/2/2016 Settelment date 8/2/2016 Maturity date 6/2/2036 Maturity date 6/2/2036 coupon rate 8% coupon rate 8% Coupons Frerquency 2 a year Coupons Frerquency 2 Face Value 1000 Face Value 1000 Required Return 9% annual Required Return 9% Bond price Bond value (price ) 90.799208 price per $100 Face Value 907.99208 price per $1000 Face Value Bond price 90.807929 Bond value (price ) 908.07929 The calculated price is what we call clean price Accrued coupon = daily coupon times number o Daily coupon 0.2222222 Days from last coupon Accrued coupon 60 13.333333 Dirty price 921.41262 te, pays coupons semi-annually, and $1000 face value. s 9% annually? times a year Extra Credit how the value against various times to maturity. Then graph your results. 8 6 4 uy the bond) as today's date. s 9% annually? y pay two months from today. a year annual price per $100 Face Value price per $1000 Face Value hat we call clean price = daily coupon times number of days from last coupon 2 0 price with accrued coupon or invoice price Calculate Various Expected Returns on a Bond Using the bond examples from the previous worksheet, calculate the current yield and the yield to maturity. Current Yield Yield to Maturity (YTM) Time to maturity Coupons Rate Coupons Frerquency Face Value Required Return Bond Value ( price ) Rough measure of the return earned over the next year Annual coupon divided by current price Compound annual rate to be expected if the bond is held until maturity Calculate using RATE and YIELD functions. 20 years 8% annual 2 times a year 1000 9% annual Settelment date Maturity date coupon rate Coupons Frerquency Face Value Required Return $907.99