Bonds and Their Valuation Calculation

123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 A B C D E F G 4/21/2004 Finding the "Fair Value" of a Bond First, we list the key features of the bond as "model inputs": Years to Mat: 15 Coupon rate: 10% Annual Pmt: $100 Par value = FV: $1,000 Going rate, r: 10% The easiest way to solve this problem is to use Excel's PV function. Click fx, then financial, then PV. Then fill in the menu items as shown in our snapshot in the screen shown just below. Chapter 6. Tool Kit for Bonds and Their Valuation The value of any financial asset is the present value of the asset's expected future cash flows. The key inputs are (1) the expected cash flows and (2) the appropriate discount rate, given the bond's risk, maturity, and other characteristics. The model developed here analyzes bonds in various ways. Bond valuation requires keen judgment with regard to assessing the riskiness of the bond, i.e., what is the likelihood that the promised coupon and maturity payments will actually be made at the scheduled times? Also, investing in bonds requires one to make implicit forecasts of future interest rates--you don't want to buy long-term bonds just before a sharp increase in interest rates. We do not deal with these important but subjective issues in this spreadsheet. Rather, we concentrate on the actual calculations used, given the inputs. Note that bond calculations are just arithmetic exercises, and that problems can be set up and solved in a number of different ways. This is especially true for spreadsheets models, which can be set up using the function wizard or not, and using different algebraic formulations. So, if you were making your own models, you might well set things up differently than our setups. Note too that in much of this spreadsheet we work with annual payment bonds, though most bonds pay interest semiannually. It is simpler to work with annual payments when discussing basic concepts. Problem: A bond has a 15-year maturity, a 10% annual coupon, and a $1,000 par value. The required rate of return (or the yield to maturity) on the bond is 10%, given its risk, maturity, liquidity, and other rates in the economy. What is a fair value for the bond, i.e., its market price? Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 A B C D E F G Value of bond = $1,000.00 Thus, this bond sells at its par value. That situation always exists if the going rate is equal to the coupon rate. The PV function can only be used if the payments are constant, but that is normally the case for bonds. Bond Value Going rate, r: $1,000 0% $2,500.00 5% $1,518.98 10% $1,000.00 15% $707.63 20% $532.45 Problem: Suppose the going interest rate changed from 10%, falling to 5% or rising to 15%. How would those changes affect the value of the bond? We could simply go to the input data section shown above, change the value for r from 10% to 5% and then 15%, and observe the changed values. An alternative is to set up a data table to show the bond's value at a range of rates, i.e., to show the bond's sensitivity to changes in interest rates. This is done below, and the values at 5% and 15% are boldfaced. We could set up data tables to get the data for this problem, but instead we simply inserted the PV formula into the following matrix to calculate the value of the bond over time. Note that the formula takes the interest rate from the column heads, and the value of N from the left column. Note that the N = 0 values for the 5% and 15% rates are consistent with the results in the data table above. We can also plot the data, as shown in the graph below. To make the data table, first type the headings, then type the rates in cells A68:A72, and then put the formula '=B53 in cell B67, then select the range A67:B72. Then click Data and then Table to get the menu. The input data are in a column, so put the cursor on column and enter B32, the place where the going rate is inputted. Click OK to complete the operation and get the table. We can use the data table to construct a graph that shows the bond's sensitivity to changing rates. To set up this problem, we will enter the different interest rates, and use the array of cash flows above. The following example operates under the precept that the bond is issued at par ($1,000) in year 0. From this point, the example sets three conditions for interest rates to follow: interest rates stay constant at 10%, interest rates fall to 5%, or interest rates rise to 15%. Then the price of the bond over the fifteen years of its life is determined for each of the scenarios. Suppose interest rates rose to 15% or fell to 5% immediately after the bond was issued, and they remained at the new level for the next 15 years. What would happen to the price of the bond over time? Interest Rate Sensitivity $0 $500 $1,000 $1,500 $2,000 $2,500 $3,0000% 5% 10% 15% 20% Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 A B C D E F G N 5% 10% 15% 0 $1,519 $1,000 $708 1 $1,495 $1,000 $714 2 $1,470 $1,000 $721 3 $1,443 $1,000 $729 4 $1,415 $1,000 $738 5 $1,386 $1,000 $749 6 $1,355 $1,000 $761 7 $1,323 $1,000 $776 8 $1,289 $1,000 $792 9 $1,254 $1,000 $811 10 $1,216 $1,000 $832 11 $1,177 $1,000 $857 12 $1,136 $1,000 $886 13 $1,093 $1,000 $919 14 $1,048 $1,000 $957 15 $1,000 $1,000 $1,000 Yield to Maturity (YTM) Value of Bond in Given Year: If rates fall, the bond goes to a premium, but it moves toward par as maturity approaches. The reverse hold if rates rise and the bond sells at a discount. If the going rate remains equal to the coupon rate, the bond will continue to sell at par. Note that the above graph assumes that interest rates stay constant after the initial change. That is most unlikely--interest rates fluctuate, and so do the prices of outstanding bonds. The YTM is defined as the rate of return that will be earned if a bond makes all scheduled payments and is held to maturity. The YTM is the same as the total rate of return discussed in the chapter, and it can also be interpreted as the "promised rate of return," or the return to investors if all promised payments are made. The YTM for a bond that sells at par consists entirely of an interest yield. However, if the bond sells at any price other than its par value, the YTM consists of the interest yield together with a positive or negative capital gains yield. The YTM can be determined by solving the bond value formula for I. However, an easier method for finding it is to use Excel's Rate function. Since the price of a bond is simply the sum of the present values of its cash flows, so we can use the time value of money techniques to solve these problems. Price of Bond Over Time $0 $200 $400 $600 $800 $1,000 $1,200 $1,400 $1,600 0 5 10 15 Rate Drops to 5% Rate Stays at 10% Rate Rises to 15% Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 A B C D E F G Use the Rate function to solve the problem. Years to Mat: 14 Coupon rate: 10% Annual Pmt: $100.00 Going rate, r =YTM: 5.00% Current price: $1,494.93 Par value = FV: $1,000.00 Yield to Call Use the Rate function to solve the problem. Years to call: 9 Coupon rate: 10% Annual Pmt: $100.00 Rate = I = YTC = 4.21% Current price: $1,494.93 Call price = FV $1,100.00 Par value $1,000.00 This bond's YTM is 5%, but its YTC is only 4.21%. Which would an investor be more likely to actually earn? Current Yield Problem: What is the current yield on a $1,000 par value, 10% annual coupon bond that is currently selling for $985? The yield to call is the rate of return investors will receive if their bonds are called. If the issuer has the right to call the bonds, and if interest rates fall, then it would be logical for the issuer to call the bonds and replace them with new bonds that carry a lower coupon. The yield to call (YTC) is found similarly to the YTM. The same formula is used, but years to maturity is replaced with years to call, and the maturity value is replaced with the call price. Problem: Suppose you purchase a 15-year, 10% annual coupon, $1,000 par value bond with a call provision after 10 years at a call price of $1,100. One year later, interest rates have fallen from 10% to 5% causing the value of the bond to rise to $1,494.93. What is the bond's YTC? Note that this is the same bond as in the previous question, but now we assume it can be called. Problem: Suppose that you are offered a 14-year, 10% annual coupon, $1,000 par value bond at a price of $1,494.93. What is the Yield to Maturity of the bond? The yield-to-maturity is the same as the expected rate of return only if (1) the probability of default is zero, and (2) the bond can not be called. If there is any chance of default, then there is a chance some payments may not be made. In this case, the expected rate of return will be less than the promised yield-to-maturity. This company could call the old bonds, which pay $100 per year, and replace them with bonds that pay somewhere in the vicinity of $50 (or maybe even only $42.10) per year. It would want to save that money, so it would in all likelihood call the bonds. In that case, investors would earn the YTC, so the YTC is the expected return on the bonds. The current yield is the annual interest payment divided by the bond's current price. The current yield provides information regarding the amount of cash income that a bond will generate in a given year. However, it does not account for any capital gains or losses that will be realized fi the bond is held to maturity or call. Simply divide the annual interest payment by the price of the bond. Even if the bond made semiannual payments, we would still use the annual interest. Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 A B C D E F G Par value $1,000.00 Coupon rate: 10% Current Yield = 10.15% Annual Pmt: $100.00 Current price: $985.00 Bonds with Semiannual Coupons Use the Rate function with adjusted data to solve the problem. Periods to maturity = 15*2 = 30 Coupon rate: 10% Semiannual pmt = $100/2 = $50.00 PV = $1,523.26 Current price: $1,000.00 Periodic rate = 5%/2 = 2.5% Note that the bond is now more valuable, because interest payments come in faster. Assessing the Riskiness of a Bond Use the PV function, along with a Data Table, to show the bonds' price sensitivity. Coupon rate: 10% Payment $100.00 Par value $1,000.00 Maturity 5 Going rate = r = YTM 10% Value of bond: $1,000.00 The current yield provides information on a bond's cash return, but it gives no indication of the bond's total return. To see this, consider a zero coupon bond. Since zeros pay no coupon, the current yield is zero because there is no interest income. However, the zero appreciates through time, and its total return clearly exceeds zero. Since most bonds pay interest semiannually, we now look at the valuation of semiannual bonds. We must make three modifications to our original valuation model: (1) divide the coupon payment by 2, (2) multiply the years to maturity by 2, and (3) divide the nominal interest rate by 2. Problem: What is the price of a 15-year, 10% semi-annual coupon, $1,000 par value bond if the nominal rate (the YTM) is 5%? The bond is not callable. Interest Rate Risk is the risk of a decline in a bond's price due to an increase in interest rates. Price sensitivity to interest rates is greater (1) the longer the maturity and (2) the smaller the coupon payment. Thus, if two bonds have the same coupon, the bond with the longer maturity will have more interest rate sensitivity, and if two bonds have the same maturity, the one with the smaller coupon payment will have more interest rate sensitivity. Problem: Compare the interest rate risk of two bonds, both of which have a 10% annual coupon and a $1,000 face value. The first bond matures in 5 years, the second in 30 years. Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 A B C D E F G Going rate, r $1,000.00 5 10 15 20 25 30 0% $1,500.00 $2,000.00 $2,500.00 $3,000.00 $3,500.00 $4,000.00 5% $1,216.47 $1,386.09 $1,518.98 $1,623.11 $1,704.70 $1,768.62 10% $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 15% $832.39 $749.06 $707.63 $687.03 $676.79 $671.70 20% $700.94 $580.75 $532.45 $513.04 $505.24 $502.11 Years to Maturity First, we use the PV function as set forth just above. Then, we set up a 2-variable Data Table, where we let both r and Years change, as shown below. Enter the formula =B252 in Cell A267 and select the range A267:G272. Then click Data, Table to get the menu. Our completed menu is shown below. Click OK to complete the table. We can show the sensitivity of bond's with different maturities by graphing the data shown in the data table. The graph below does this. The lines for the 5-year and 30-year bonds are boldfaced. Value of the Bond Under Different Conditions Price sensitivity to changing rates $0 $500 $1,000 $1,500 $2,000 $2,500 $3,000 $3,500 $4,0000% 5% 10% 15% 20% 5-Year 10-Year 15-Year 20-Year 25 Year 30-Year Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 A B C D E F G Date Functions and Excel's Bond Function First, click on the function wizard and then Financial and then Price to get the menu shown below: Settlement (today) 10/25/2004 Maturity 1/1/2023 Coupon rate 8.00% Going rate, r 7.00% Redemption (par value) 100 Frequency (for semiannual) 2 Basis (360 or 365 day year) 0 Thus far we have evaluated bonds assuming that we are at the beginning of an interest payment period. This is correct for new issues, but it is generally not correct for outstanding bonds. However, Excel has several date and time function, and a bond valuation function that uses the calendar, so we can get exact valuations on any given date. Problem: Assume that a $1,000 par value bond matures on January 1, 2023, that it pays an 8% coupon rate with semiannual payments, and that interest is paid each January 1 and July 1. If the going rate of interest for such bonds is 7%, what is the bond's value today? Note that "today" will vary depending on when you evaluate the bond. Notice that the equation shown has as arguments the settlement date, the maturity, and so forth. Set up an input Note (1) that par value is stated as 100 for a $1,000 bond and (2) that "0" for basis designates a 360 day year, which is the U.S convention. In our example, we shall assume that today's date is October 25, 2004. Now click OK to get the Price (of a bond) menu as shown below. Ours is filled in, but you must scroll down (yours) to see all the entries. Fill in your menu and then click OK to get the bond's value, 110.45299% of par, or $1,104.53. Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 A B C D E F G Value of bond = #VALUE! or #VALUE! Note: We entered actual values in this screen shot, but our formula in C341 refers to the input cells. Extensions: 1. If you knew the price of the bond and wanted to determine its yield, you could use Excel's "YIELD" function, which is similar to the Price function. 2. If you wanted to find the bond's value "today," you could enter =NOW() in the input section for the settlement date, either typing it in or using the date function wizard. Then, the bond's value would be recalculated each time you opened the file. This feature is useful for bond portfolio managers. 3. If you bought this bond, you would have to pay the calculated price plus interest accrued since the last interest payment date. Excel's ACCRINT function calculates the accrued interest. 10/25/2003 01/01/2022 8% 7% 100 10/02/2003 01/01/2022 0.08 0.07 100 110.1811445 110.1811445 Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.$ASQBonds and Their Valuation Calculation.xls Duration Years to Mat: 20 Coupon rate: 9% Annual Pmt: $90 Par value = FV: $1,000 Going rate, r: 9% 9% Table 6E-1 Duration t (1) CF (2) PVCF (3) PVCF/Value (4) t (PVCF/Value) (5) 1 $90 $82.57 0.0826 0.0826 2 $90 $75.75 0.0758 0.1515 3 $90 $69.50 0.0695 0.2085 4 $90 $63.76 0.0638 0.2550 5 $90 $58.49 0.0585 0.2925 6 $90 $53.66 0.0537 0.3220 7 $90 $49.23 0.0492 0.3446 8 $90 $45.17 0.0452 0.3613 9 $90 $41.44 0.0414 0.3729 10 $90 $38.02 0.0380 0.3802 11 $90 $34.88 0.0349 0.3837 12 $90 $32.00 0.0320 0.3840 13 $90 $29.36 0.0294 0.3816 14 $90 $26.93 0.0269 0.3771 15 $90 $24.71 0.0247 0.3706 16 $90 $22.67 0.0227 0.3627 17 $90 $20.80 0.0208 0.3535 18 $90 $19.08 0.0191 0.3434 19 $90 $17.50 0.0175 0.3326 20 $1,090 $194.49 0.1945 3.8898 Value = $1,000.00 Duration = 9.95011 Duration of Bond = 9.95011 9 Interest rate = 9% 10 FV in 9 years = $1,171.89 FV in 9.95011 years = $1,271.88 Chapter 6 Web Extension. Tool Kit for Duration Duration is a measure of risk for bonds. The following example illustrates its calculation. Consider the amount that would accumulate during the first 9.95 years, if all coupons are reinvested at the original interest rate of 9%. To do this, first find the amount that would be in the account at 9 years (including the 9-year coupon), and then compund for 0.95011 years. Consider the value of a bond at 9.95 years, if the interest rate is still 9%. To do this, first find the present value of the bond's Michael C. Ehrhardt Page 9 1/30/2008$ASQBonds and Their Valuation Calculation.xls Duration PV of payments beyond year 10 discounted back to year 10 = $1,090.00 PV of payments beyond year 9.95011 discounted back to year 9.90511 = $1,085.32 Value of reinvested coupons: $1,271.88 Current value of bond: $1,085.32 Total value of position = $2,357.21 Reinvested Coupons Current Price at t=Duration Total Value Change in Total Value from Interest Rate = 9% $1,271.88 $1,085.32 $2,357.21 1% $851.18 $1,846.79 $2,697.96 $340.76 2% $894.59 $1,717.08 $2,611.67 $254.47 3% $940.36 $1,599.45 $2,539.81 $182.61 4% $988.61 $1,492.62 $2,481.24 $124.03 5% $1,039.48 $1,395.47 $2,434.95 $77.74 6% $1,093.09 $1,307.00 $2,400.09 $42.88 7% $1,149.59 $1,226.33 $2,375.92 $18.71 8% $1,209.14 $1,152.67 $2,361.81 $4.60 9% $1,271.88 $1,085.32 $2,357.21 $0.00 10% $1,337.99 $1,023.68 $2,361.67 $4.46 11% $1,407.63 $967.17 $2,374.80 $17.59 12% $1,480.99 $915.30 $2,396.29 $39.09 13% $1,558.25 $867.64 $2,425.89 $68.68 14% $1,639.60 $823.79 $2,463.40 $106.19 15% $1,725.26 $783.39 $2,508.66 $151.45 16% $1,815.44 $746.13 $2,561.57 $204.36 The total value of the position at time 9.95011 is the value of the reinvested coupon and the current value of the bond. As the table below shows, the total value of a position at a future time equal to the orginal duration will not fall if interest rates change. For example, if rates go up, the value of reinvested coupons increases and the value of the bond at the future date (t=duration) falls, but the net affect is an increase in total value. If rates go down, the value of reinvested coupons goes down, but the future value of the bond goes up, for a net increase in value. Thus, if the desired time horizon is equal to the bond's duration, the value of the position will not fall if interest rates change. future payments at year 10 (including the coupon payment at year 10), then discount this amount back to time 9.95011. Michael C. Ehrhardt Page 10 1/30/2008$ASQBonds and Their Valuation Calculation.xls Duration 4/21/2004 during the first 9.95 years, if all coupons are reinvested at the original interest that would be in the account at 9 years (including the 9-year coupon), and then the interest rate is still 9%. To do this, first find the present value of the bond's Michael C. Ehrhardt Page 11 1/30/2008$ASQBonds and Their Valuation Calculation.xls Duration is the value of the reinvested coupon and the current value of the bond. position at a future time equal to the orginal duration will not fall if interest value of reinvested coupons increases and the value of the bond at the future increase in total value. If rates go down, the value of reinvested coupons goes up, for a net increase in value. Thus, if the desired time horizon is equal to the coupon payment at year 10), then discount this amount back to time 9.95011. Michael C. Ehrhardt Page 12 1/30/2008$ASQBonds and Their Valuation Calculation.xls Zero Coupon Bonds 4/21/2004 ZERO COUPON BONDS This example analyzes the after-tax cost of issuing zero coupon debt. Input Data (in thousands of dollars) Maturity value= $1,000 Pre-tax market interest rate, rd = 9% Maturity (in years) = 5 Corporate tax rate = 40% Coupon rate = 0% Coupon payment (assuming annual payments) = $0 Issue Price PV of payments at rd = $649.930 1 2 3 4 5 Remaining time until maturity 5 4 3 2 1 0 Year-end accrued value $649.93 $708.43 $772.18 $841.68 $917.43 $1,000.00 Interest payment $0.00 $0.00 $0.00 $0.00 $0.00 Implied interest deduction on discount $58.49 $63.76 $69.50 $75.75 $82.57 Tax savings $23.40 $25.50 $27.80 $30.30 $33.03 Cash flow $649.93 $23.40 $25.50 $27.80 $30.30 ($966.97) After-tax cost of debt = 5.4% Chapter 6 Web Extension: Zero Coupon Bonds Michael C. Ehrhardt Page 13 1/30/2008$ASQBonds and Their Valuation Calculation.xls Zero Coupon Bonds Michael C. Ehrhardt Page 14 1/30/2008