Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
Can you please explain using excel. Please show how I would be able to enter into excel myself. Thank you 2. YTM, YTC, YTW, Current
Can you please explain using excel. Please show how I would be able to enter into excel myself. Thank you
2. YTM, YTC, YTW, Current Yield - (input Issue Date, Trading Day, Price of The bond, redemption, frequency, coupon rate) YIELD TO MAURITY (YTM), YIELD TO CALL (YTC), YIELD TO WORSE (YTW) and CURRENT YIELD (CY) EXCEL FORMULAS YTM YTC1 YTC2 YTC3 YTC4 YTC5 Issuance Date 1/16/2017 1/16/2017 1/16/2017 1/16/2017 1/16/2017 1/16/2017 Trading Date Wednesday, July 11, 2018 7/11/2018 7/11/2018 7/11/2018 7/11/2018 7/11/2018 Settlement Date (T+3) (SD) Monday, July 16, 2018 7/16/2018 7/16/2018 7/16/2018 7/16/2018 7/16/2018 Maturity Date / Call Date (MD) 1/16/2027 1/16/2018 1/16/2019 1/16/2020 1/16/2021 1/16/2022 Coupon Rate (CR) 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% Market Price (MP) 98.50 98.50 98.50 98.50 98.50 98.50 Redemption (Final payment % of Par) (R) 100.00 105.00 104.00 103.00 102.00 101.00 Frequency (payments per year) (F) 2 2 2 2 2 2 Cal Provision 105.00 104.00 103.00 102.00 101.00 YTM YTC YIW a YTM= 8.249% YTC= NA 19.289% 11.006% 9.415% 8.757% YTW= 8.249% CY= 8.1218% HPR%=CF CP/MV =YIELD/J9,19,J11,J12,J13) =YIELD(SD,MD,CR,MP.RF) Face Value Coupon Payments Years (Term) ) $1,000 $40 +E10'E212 10 Years 3. Price, Duration, Convexity - Spreadsheet that you need to build) BOND PRICE, DURATION & CONVEXITY Sensitivity to interest rate movements 1.000 8.00% Face Value Coupon Rate Life in Years Yield Frequency Bond Price 10.00% $922.78 PV(ESE10.EB-E10,E7'EVE 10.E6) Macaulay Duration 4.18 Modified Duration 4.1%+E13/1+E9/ES)/100 P + 2 Convexity 17.83 1.93% +E17/11 PV Cash Flow Weighted Duration Calc Convexity Calc Factor years Period 0 0 1 1 2 3 4 38.10 362 Cush Flow (5922.78) 40.00 40.00 40,00 40.00 40.00 40.00 40.00 40.00 40.00 1.040.00 S 6 7 S 3455 32.91 31.34 29.85 28.43 4128% 3.932% 3.744% 3.588% 3.396% 3.235% 3.081% 2.931 2794% 69.190% 100.000% 0.04128 m+F21"B21 0.07889 0.11233 -821B2112 0.14265 0.16992 0.19408 0.21564 0.23471 0.25148 6.91896 8.35959 SUM/G21:630) 30 2.000 6.000 12.000 20.000 30.000 42.000 56.000 72.000 90.000 110.000 76.19-121 D21 217.69 414.64 658.16 940.23 1.253.64 1,591.93 1,949 30 2.320.99 20.231.68 79,654.05 SUM 20:30) 27.07 9 25.78 638.47 PRICE 922.78 -SUMD20:30) DURATION 4.18-G31/2 CONVEXITY 17.83 - (31/(1+E9)^2)D33E102)) The formula for calculating the convexity is as follows: Where is the periodic yield, t is the time period, CF is the cash flow payment or the coupon payment, n is the number is periods, and is the frequency of payments per year 2. YTM, YTC, YTW, Current Yield - (input Issue Date, Trading Day, Price of The bond, redemption, frequency, coupon rate) YIELD TO MAURITY (YTM), YIELD TO CALL (YTC), YIELD TO WORSE (YTW) and CURRENT YIELD (CY) EXCEL FORMULAS YTM YTC1 YTC2 YTC3 YTC4 YTC5 Issuance Date 1/16/2017 1/16/2017 1/16/2017 1/16/2017 1/16/2017 1/16/2017 Trading Date Wednesday, July 11, 2018 7/11/2018 7/11/2018 7/11/2018 7/11/2018 7/11/2018 Settlement Date (T+3) (SD) Monday, July 16, 2018 7/16/2018 7/16/2018 7/16/2018 7/16/2018 7/16/2018 Maturity Date / Call Date (MD) 1/16/2027 1/16/2018 1/16/2019 1/16/2020 1/16/2021 1/16/2022 Coupon Rate (CR) 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% Market Price (MP) 98.50 98.50 98.50 98.50 98.50 98.50 Redemption (Final payment % of Par) (R) 100.00 105.00 104.00 103.00 102.00 101.00 Frequency (payments per year) (F) 2 2 2 2 2 2 Cal Provision 105.00 104.00 103.00 102.00 101.00 YTM YTC YIW a YTM= 8.249% YTC= NA 19.289% 11.006% 9.415% 8.757% YTW= 8.249% CY= 8.1218% HPR%=CF CP/MV =YIELD/J9,19,J11,J12,J13) =YIELD(SD,MD,CR,MP.RF) Face Value Coupon Payments Years (Term) ) $1,000 $40 +E10'E212 10 Years 3. Price, Duration, Convexity - Spreadsheet that you need to build) BOND PRICE, DURATION & CONVEXITY Sensitivity to interest rate movements 1.000 8.00% Face Value Coupon Rate Life in Years Yield Frequency Bond Price 10.00% $922.78 PV(ESE10.EB-E10,E7'EVE 10.E6) Macaulay Duration 4.18 Modified Duration 4.1%+E13/1+E9/ES)/100 P + 2 Convexity 17.83 1.93% +E17/11 PV Cash Flow Weighted Duration Calc Convexity Calc Factor years Period 0 0 1 1 2 3 4 38.10 362 Cush Flow (5922.78) 40.00 40.00 40,00 40.00 40.00 40.00 40.00 40.00 40.00 1.040.00 S 6 7 S 3455 32.91 31.34 29.85 28.43 4128% 3.932% 3.744% 3.588% 3.396% 3.235% 3.081% 2.931 2794% 69.190% 100.000% 0.04128 m+F21"B21 0.07889 0.11233 -821B2112 0.14265 0.16992 0.19408 0.21564 0.23471 0.25148 6.91896 8.35959 SUM/G21:630) 30 2.000 6.000 12.000 20.000 30.000 42.000 56.000 72.000 90.000 110.000 76.19-121 D21 217.69 414.64 658.16 940.23 1.253.64 1,591.93 1,949 30 2.320.99 20.231.68 79,654.05 SUM 20:30) 27.07 9 25.78 638.47 PRICE 922.78 -SUMD20:30) DURATION 4.18-G31/2 CONVEXITY 17.83 - (31/(1+E9)^2)D33E102)) The formula for calculating the convexity is as follows: Where is the periodic yield, t is the time period, CF is the cash flow payment or the coupon payment, n is the number is periods, and is the frequency of payments per yearStep by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started