FV 100 coupon rate 0.08 coupon 8 yield y 0.1 number of period n 10 annualy n 1 2 3 4 5 6 7 8 9 10 10 CF 8 8 8 8 8 8 8 8 8 8 100 PV CF 7.272727273 6.611570248 6.010518407 5.464107643 4.967370584 4.51579144 4.105264946 3.732059042 3.392780947 3.084346315 38.55432894 Price P 87.71086579 semi annualy FV 1000 coupon 4 coupon rate 0.04 yield y 0.05 number of period n 20 n 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 20 CF 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 100 PV CF 3.80952381 3.628117914 3.455350394 3.290809899 3.134104666 2.984861587 2.842725321 2.707357448 2.578435665 2.455653014 2.338717156 2.227349673 2.121285403 2.020271812 1.924068392 1.832446088 1.74518675 1.662082619 1.582935828 1.507557931 37.68894829 P 87.53778966 SUM of the Geomertic sequence for example1 SUM of the Geometric sequence for example 2 -0.614456711 -0.623110517 -0.090909091 -0.047619048 6.759023816 13.08532086 540.7219053 523.4128344 491.5653685 49.15653685 498.4884137 49.84884137 plus plus PV FV PV FV 38.55432894 37.68894829 Price 87.71086579 87.53778966 ##### Sheet/List 2 ##### A B 2 Data Description 3 "February 15, 2008" Settlement date Required. The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. 4 "November 15, 2016" Maturity date Required. The security's maturity date. The maturity date is the date when the security expires 5 5.75% Percent coupon Required. The security's annual coupon rate. 6 95.04287 Price  Required. The security's price per $100 face value. 7 $100 Redemption value  Required. The security's redemption value per $100 face value. 8 2 Frequency is semiannual (see above) "Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4." 9 0 30/360 basis (see above)  Optional. The type of day count basis to use. Basis Day count basis 0 or omitted US 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360 FV 100 c 0.08 coupon 8 k 0.1 n 10 annualy 1/1/2015 Settlement date 12/31/2025 Maturity date 8% Percent coupon 87.71 Price 100 Redemption value 1 Frequency is annual (see above) 1 30/360 basis (see above) YIELD 0.098820568 semiannualy 1/1/2015 Settlement date 12/31/2025 Maturity date 8% Percent coupon 87.53 Price 100 Redemption value 2 Frequency is semiannual (see above) 1 30/360 basis (see above) YIELD 0.098849679 ##### Sheet/List 3 ##### y P Price change percentage change in y Bond price elasticity FV 100 0.01 166.2991 coupon rate 0.08 coupon 8 0.02 153.8955 -7.46% 100 -0.000745861 yield 0.91 0.03 142.651 -7.31% 50 -0.001461316 n 10 0.04 132.4436 -7.16% 33.33333333 -0.002146652 0.05 123.1652048 -7.01% 25 -0.002802218 annualy 0.06 114.7201741 -6.86% 20 -0.003428335 0.07 107.0235815 -6.71% 16.66666667 -0.004025408 0.08 100 -6.56% 14.28571429 -0.004593854 n 1 2 3 4 5 6 7 8 9 10 10 0.09 93.5823423 -6.42% 12.5 -0.005134126 CF 8 8 8 8 8 8 8 8 8 8 100 0.1 87.71086579 -6.27% 11.11111111 -0.005646716 PV CF 4.188481675 2.192922343 1.148126881 0.60111355 0.314719136 0.164774417 0.086269328 0.045167188 0.023647742 0.012381017 0.154762711 0.11 82.33230397 -6.13% 10 -0.00613215 P 8.932365989 0.12 77.39910789 -5.99% 9.090909091 -0.006590992 0.13 72.86878262 -5.85% 8.333333333 -0.007023841 0.14 68.70330612 -5.72% 7.692307692 -0.00743133 0.15 64.86861962 -5.58% 7.142857143 -0.007814123 y P Price change 0.5 17.45668851 0.51 17.05440905 -2.30% y P Price change 0.9 9.037494543 0.91 8.932365989 -1.16% ##### Sheet/List 4 ##### FV 100 period = t CF "PV CF y = 0,1" t * PV CF t^2 +t (t^2 + t)* PV CF y P coupon rate 0.08 coupon 8 1 8 7.272727273 7.272727273 2 14.54545455 0.01 166.2991 y 0.1 2 8 6.611570248 13.2231405 6 39.66942149 0.02 153.8955 n 10 3 8 6.010518407 18.03155522 12 72.12622089 0.03 142.651 4 8 5.464107643 21.85643057 20 109.2821529 0.04 132.4436 annualy 5 8 4.967370584 24.83685292 30 149.0211175 0.05 123.1652048 6 8 4.51579144 27.09474864 42 189.6632405 0.06 114.7201741 7 8 4.105264946 28.73685462 56 229.894837 0.07 107.0235815 8 8 3.732059042 29.85647233 72 268.708251 0.08 100 9 8 3.392780947 30.53502852 90 305.3502852 0.09 93.5823423 0.140109599 10 8 3.084346315 30.84346315 110 339.2780947 0.1 87.71086579 0.066941267 10 100 38.55432894 385.5432894 110 4240.976184 0.11 82.33230397 -0.0613215 sum 180 87.71086579 617.8305632 5958.515259 0.12 77.39910789 -0.117565342 0.13 72.86878262 DUR 7.043945555 in years 0.826446281 0.14 68.70330612 mod DUR 7.143945555 in percent 4924.392776 0.15 64.86861962 56.14347472 convexity 56.14347472 100 basis points +/- "0,1+/-0,01" effect of duration 0.064035869 effect of convexity 0.002319978 both effects 0.066355847 -0.06171589 200 basis points "0,1+/-0,02" 0.128071737 0.009279913 0.137351651 -0.118791824 semiannualy period = t CF "PV CF y = 0,05" t * PV CF t^2 + T (t^2 + t)* PV CF FV 100 1 4 3.80952381 3.80952381 2 7.619047619 coupon rate 0.04 coupon 4 2 4 3.628117914 7.256235828 6 21.76870748 y 0.05 3 4 3.455350394 10.36605118 12 41.46420473 n 20 4 4 3.290809899 13.1632396 20 65.81619798 5 4 3.134104666 15.67052333 30 94.02313998 6 4 2.984861587 17.90916952 42 125.3641866 7 4 2.842725321 19.89907724 56 159.1926179 8 4 2.707357448 21.65885958 72 194.9297363 9 4 2.578435665 23.20592098 90 232.0592098 10 4 2.455653014 24.55653014 110 270.1218316 11 4 2.338717156 25.72588872 132 308.7106646 12 4 2.227349673 26.72819607 156 347.4665489 13 4 2.121285403 27.57671023 182 386.0739433 14 4 2.020271812 28.28380537 210 424.2570805 15 4 1.924068392 28.86102589 240 461.7764142 16 4 1.832446088 29.31913741 272 498.4253359 17 4 1.74518675 29.66817476 306 534.0271456 18 4 1.662082619 29.91748715 342 568.4322559 19 4 1.582935828 30.07578073 380 601.5156147 20 4 1.507557931 30.15115863 420 633.1743312 20 100 37.68894829 753.7789657 420 15829.35828 sum 87.53778966 1197.581462 21805.5765 DUR 6.840368409 in years mod DUR 6.51463658 in percent convexity 56.48503564 100 basis points +/- "0,05+/-0,01" effect of duration 0.065146366 effect of convexity 0.00256168 both effects 0.067708045 -0.062584686 200 basis points "0,05+/-0,02" 0.118447938 0.010246718 0.128694656 -0.108201219