Please analyze the data in the Spreadsheet 10.1 provided below and change the relevant selected input variables of bond characteristics (including annual coupon rate) but realistically, and following the market conventions for quoting bonds) for each bond separately to get the Invoice price of 77, 97 and 146 respectively for the first, the second and the third bond in the table (as close as possible due to implied limitations). Please use the trial and error method to solve the problem in the spreadsheet. Please discuss how your changes will affect for each bonds: risk, yield to maturity (YTM), investment horizon, and the annual cash flow expected for an investor holding the bond. You can use the provided Spreadsheet 10.2 for some additional help for you to practice some changes and to see the implications for YTM.

Next, how can you increase the value (Invoice price of these bonds) additionally, beyond this exercise, by adding extra information, features and covenants to these plain vanilla bonds? Please refer to the securities valuation concepts (models) and the peer-reviewed journal papers to support your ideas to address this very important issue for an issuer of exotic bonds

Spreadsheet 10.1

Spreadsheet 10.2

uses the format DATE(year,month,day). The first date is February 15, 2017, when the bond is purchased, and the second is February 15, 2047, when it matures. Notice that the coupon rate and yield to maturity in Excel must be expressed as decimals, not percentages (as they would be in financial calculators). In most cases, redemption value is 100 (i.e., 100% of par value), and the resulting price similarly is expressed as a percent of par SPREADSHEET 10.1 Valuing Bonds Using a Spreadsheet D G 3.75% coupon bond, 8% coupon bond, maturing November 2043 30-year maturity A B 1 3% coupon bond, 2 maturing Feb 2047 Formula in column B 3 4 Settlement date 2/45/2017_DATE(2017.2.15) 5 Maturity date 2/15/2047OATE(2047,2,15) 6 Annual coupon rate 0.03 7 Yield to maturity 0.03088 8 Redemption value (% of face value) 9 Coupon payments per year 2 10 11 12 Flat price (% of par) 98.2867]=PRICE(B4,B5,B6,B7,B8,B9) 13 Days since last coupon O=COUPDAYBS(B4,B5,2,1) 14 Days in coupon period 181 =COUPDAYS(B4,B5,2,1) 15 Accrued interest 0] =(B13/B14)*16*100/2 98.2867 =B12+B15 2/15/2017 11/15/2043 0.0375 0.03065 100 2. 1/1/2000 1/1/2030 0.08 0.1 100 2 100 112.4398 92 181 0.953 113.3928 81.0707 0 182 0 81.0707 16 Invoice price sing a Spreadsheet (30-year maturity bond, coupon rate = 3%, price = 100% of par) A B Semiannual coupons 1 D Annual coupons 2 3 Settlement date 4 Maturity date 5 Annual coupon rate 6 Bond price (flat) 7 Redemption value (% of face value) 8 Coupon payments per year 9 10 Yield to maturity (decimal) 171/2000 1/1/2030 0.08 127.676 37172000 1/12030 0208 127.676 100 1 100 2 0.0600 0.0599 The formula entered here is = YIELD(B3,B4,B5,B6,B7,B8) Yield to maturity differs from the current yield of a bond, which is the bond's annual coupon payment divided by its price. The current yield of the 8%, 30-year bond selling a $1,276.76 is $80/$1,276.76 =.0627, or 6.27% per year In contrast wait say that the effe- tiye annuel uses the format DATE(year,month,day). The first date is February 15, 2017, when the bond is purchased, and the second is February 15, 2047, when it matures. Notice that the coupon rate and yield to maturity in Excel must be expressed as decimals, not percentages (as they would be in financial calculators). In most cases, redemption value is 100 (i.e., 100% of par value), and the resulting price similarly is expressed as a percent of par SPREADSHEET 10.1 Valuing Bonds Using a Spreadsheet D G 3.75% coupon bond, 8% coupon bond, maturing November 2043 30-year maturity A B 1 3% coupon bond, 2 maturing Feb 2047 Formula in column B 3 4 Settlement date 2/45/2017_DATE(2017.2.15) 5 Maturity date 2/15/2047OATE(2047,2,15) 6 Annual coupon rate 0.03 7 Yield to maturity 0.03088 8 Redemption value (% of face value) 9 Coupon payments per year 2 10 11 12 Flat price (% of par) 98.2867]=PRICE(B4,B5,B6,B7,B8,B9) 13 Days since last coupon O=COUPDAYBS(B4,B5,2,1) 14 Days in coupon period 181 =COUPDAYS(B4,B5,2,1) 15 Accrued interest 0] =(B13/B14)*16*100/2 98.2867 =B12+B15 2/15/2017 11/15/2043 0.0375 0.03065 100 2. 1/1/2000 1/1/2030 0.08 0.1 100 2 100 112.4398 92 181 0.953 113.3928 81.0707 0 182 0 81.0707 16 Invoice price sing a Spreadsheet (30-year maturity bond, coupon rate = 3%, price = 100% of par) A B Semiannual coupons 1 D Annual coupons 2 3 Settlement date 4 Maturity date 5 Annual coupon rate 6 Bond price (flat) 7 Redemption value (% of face value) 8 Coupon payments per year 9 10 Yield to maturity (decimal) 171/2000 1/1/2030 0.08 127.676 37172000 1/12030 0208 127.676 100 1 100 2 0.0600 0.0599 The formula entered here is = YIELD(B3,B4,B5,B6,B7,B8) Yield to maturity differs from the current yield of a bond, which is the bond's annual coupon payment divided by its price. The current yield of the 8%, 30-year bond selling a $1,276.76 is $80/$1,276.76 =.0627, or 6.27% per year In contrast wait say that the effe- tiye annuel