Question

6.60% 7.30% *Called on date in cell B8. *Put on the date in cell B10 Inputs Settlement Maturity Annual Coupon rate Maturity value, % of par Current price, % of par Call price, % of par First callable on Put price, % of par Putable on Coupons per year Basis Yield change (for E7) 9/30/2019 9/30/2031 7.00% 100 103.25 105 9/30/2022 95 9/30/2024 Output Yield to maturity Yield to first call Yield to put Macaulay Duration Modified Duration New Price if YTM changes by amount in B13 % Price Change if YTM changes by % in B13 90 -58*Actual (not estimated by dur-n) 0.65% Information to compute total return Number of reinvested coupons Annual coupon reinvest.rate Selling price, % of par Total return computation FV of reinvested coupons, % of par Total accumulated, % of par Annualized total return 4.60% 102.50 Use Excel functions whenever possible. Refer to cells rather than input numerical values! I grade procedures, not answers; you will lose points if your output (answers) does not change when the relevant inputs change. You should not have anything in cells other than the shaded ones (i.e., no intermediate computations are needed)!

What excel functions are used to find the Yield to maturity and Yield to first call? Those are answers, just not sure how to get them.

Answer 1

The yield to maturity is calculated using YIELD function in Excel with these inputs :

settlement = settlement date

maturity = maturity date

rate = coupon rate

pr = current price of bond as % of par

redemption = maturity value of bond as % of par

frequency = coupons per year

basis = basis

The yield to first call is calculated using YIELD function in Excel with these inputs :

settlement = settlement date

maturity = first call date

rate = coupon rate

pr = current price of bond as % of par

redemption = call price of bond as % of par

frequency = coupons per year

basis = basis

The yield to put is calculated using YIELD function in Excel with these inputs :

settlement = settlement date

maturity = put date

rate = coupon rate

pr = current price of bond as % of par

redemption = put price of bond as % of par

frequency = coupons per year

basis = basis

Macaulay duration is calculated using DURATION function in Excel with these inputs :

settlement = settlement date

maturity = maturity date

coupon = coupon rate

yld = yield to maturity

frequency = coupons per year

basis = basis

1 settlment 2 maturity 3 coupon 4 maturity value 5 price 6 call price 7 first call date 8 put price 9 put date 10 coupons per year 11 basis 12 13 yield to maturity 14 yield to first call 15 yield to put 16 macaulay duration 9/30/2019 9/30/2031 7.00% 100.00 103.25 105.00 9/30/2022 95.00 9/30/2024 2.00 1.00 6.60% 7.30% 5.37% 8.38 17

1 settlment 2 maturity 3 coupon 4 maturity value 5 price 6 call price 7 first call date 8 put price 9 put date 10 coupons per year 11 basis 12 13 yield to maturity 14 yield to first call 15 yield to put 16 macaulay duration =DATE(2019,9,30) =DATE(2031,9,30) 0.07 100 103.25 105 =DATE(2022,9,30) 95 =DATE(2024,9,30) =YIELD(B1,B2,B3,B5,B4,B10,811) =YIELD(B1,B7,B3,B5, B6,B10, B11) =YIELD(B1, B9,B3,B5, B8,B10,B11) =DURATION(B1,B2,B3,B13,B10, B11)