Question

J35 1 x ✓ fx ADVANCED PROBLEM 6-2 FOR SPREADSHEET APPLICATION Callable bond and call premium. Edward has just purchased a callable bond and wants to determine the bond's potential payoff price if it is called at any time during its callable life. The callable bond is a twenty-year semiannual bond that an issuer can call starting at year ten. It is callable every six months on the coupon payment date. The call price is declining and starts out as one extra year's coupon (a full annual coupon payment). It reduces each period (every six months by 1/20th of the annual coupon payment) until—at the bond's maturity—there is no call premium price. If the original bond is an 8% bond (with 4% coupons paid semiannually), a par value of $1,000 and an original yield to maturity of 6.5%, what is the bond price today at each potential callable date (coupon payment date) given no change in its original yield to maturity? Edward wants to compare the callable bond price at each callable date with that of a noncallable bond that is identical in every way other than the call feature (same yield to maturity, same coupon rate, and same maturity date). The difference between the callable bond price and the noncallable bond price is the call premium if called on that date. Find the call premium for each potential call date. Given Data Par Value Annual Coupon Rate Yield to Maturity Number of Payments per Year $1,000.00 8.0% 6.5%
Please type the formula of how do you get the answer, For example, D16=E2*D3 Thank you

Answer 1

I have answered the question below using excel and have attached the image below.

Please upvote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

Call date 1 (years) 2 10 3 10.5 4 11 5 11.5 6 12 7 12.5 8 13 9 13.5 10 14 11 14.5 12 15 13 15.5 14 16 15 16.5 16 17 17 17.5 18 18 19 18.5 Call Price =1000+(1000*8%) =B2-(1000*8%*1/20) =B3-(1000*8%*1/20) =B4-(1000*8%*1/20) =B5-(1000*8%*1/20) =B6-(1000*8%*1/20) =B7-1000*8%*1/20) =B8-(1000*8%*1/20) =B9-(1000*8%*1/20) =B10-(1000*8%*1/20) =B11-(1000*8%*1/20) =B12-(1000*8%*1/20) =B13-(1000*8%*1/20) =B14-(1000*8%*1/20) =B15-(1000*8%*1/20) =B16-(1000*8%*1/20) =B17-(1000*8%*1/20) =B18-1000*8%*1/20) =B19-1000*8%*1/20) =B20-(1000*8%*1/20) =B21-(1000*8%*1/20) Price if called on date =PV(6.5%/2, A2*2,1000*8%/2,B2)*-1 =PV(6.5%/2,A3*2,1000*8%/2,83)-1 =PV(6.5%/2,A4*2,1000*8%/2,B4)*-1 =PV(6.596/2, A5*2,1000*8%/2,85)*-1 =PV(6.5%/2,A6*2, 1000*8%/2,B6)-1 =PV(6.5%/2,A7*2,1000*8%/2,87)-1 =PV(6.596/2,A8*2,1000*8%/2,88)*-1 =PV(6.5%/2,49*2,1000*8%/2,89)-1 =PV(6.5%/2, A10*2,1000*8%/2,810)-1 =PV(6.596/2,A112,1000*8%/2,811)*-1 =PV(6.5%/2, A12*2,1000*8%/2,812)*-1 =PV(6.5%/2,A13*2,1000*8%/2,813)*-1 =PV(6.5%/2,A14*2,1000*8%/2,814)*-1 =PV(6.5%/2, A15*2,1000*8%/2,815)-1 =PV(6.5%/2, A16*2,1000*8%/2,816)*-1 =PV(6.5%/2, A17*2,1000*8%/2,B17)-1 =PV(6.5%/2,A18*2,1000*8%/2,818)*-1 =PV(6.5%/2, A19*2,1000*8%/2,819)*-1 =PV(6.5%/2, A20*2,1000*8%/2,B20)*1 =PV(6.5%/2,A21*2,1000*8%/2,821)*-1 =PV(6.5%/2,A22*2,1000*8%/2,822)*-1 Price of non-callable bond =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)*-1 EPV(6.5%/2,20*2,1000*8%/2, 1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)*-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)*-1 Call premium =D2-C2 =D3-C3 =D4-C4 =D5-C5 =D6-C6 =D7-C7 =D8-C8 =D9-09 =D10-C10 =D11-C11 =D12-C12 =D13-C13 =D14-C14 =D15-C15 =D16-C16 =D17-C17 =D18-C18 =D19-C19 =D20-020 =D21-C21 =D22-C22 20 19 =PV(6.5%/2,20*2,1000*8%/2,1000)-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 =PV(6.5%/2,20*2,1000*8%/2, 1000)-1 =PV(6.5%/2,20*2,1000*8%/2,1000)*1 =PV(6.5%/2,20*2,1000*8%/2,1000)*-1 21 19.5 22 20

Here is the solution,

A B Call date 1 (years) Call Price 10.0 $ 1,080.00 10.5 $ 1,076.00 11.0 $ 1,072.00 11.5 $ 1,068.00 12.0 $ 1,064.00 12.5 $ 1,060.00 13.0 $ 1,056.00 13.5 $ 1,052.00 14.0 $ 1,048.00 14.5 $ 1,044.00 15.0 $ 1,040.00 15.5 $ 1,036.00 16.0 $ 1,032.00 16.5 $ 1,028.00 17.0 $ 1,024.00 17.5 $ 1,020.00 18.0 $ 1,016.00 18.5 $ 1,012.00 19.0 $ 1,008.00 19.5 $ 1,004.00 20.0 $ 1,000.00 C D E Price if Price of non- called on callable Call date bond premium $1,151.24 $ 1,166.56 $ 15.32 $1,151.70 $ 1,166.56 $ 14.86 $1,152.21 $ 1,166.56 $ 14.35 $1,152.77 $ 1,166.56 $ 13.80 $1,153.37 $ 1,166.56 $ 13.20 $1,154.01 $ 1,166.56 $ 12.56 $1,154.68 $ 1,166.56 $ 11.88 $1,155.39 $ 1,166.56 $ 11.17 $1,156.13 $ 1,166.56 $ 10.44 $1,156.89 $ 1,166.56 $ 9.67 $1,157.69 $ 1,166.56 $ 8.88 $1,158.50 $ 1,166.56 $ 8.06 $1,159.34 $ 1,166.56 $ 7.22 $1,160.20 $ 1,166.56 $ 6.37 $1,161.07 $ 1,166.56 $ 5.49 $1,161.96 $ 1,166.56 $ 4.60 $1,162.86 $ 1,166.56 $ 3.70 $1,163.77 $ 1,166.56 $ 2.79 $1,164.69 $ 1,166.56 $ 1.87 $1,165.63 $ 1,166.56 $ 0.94 $1,166.56 $ 1,166.56 $ .

Working:

Price of callable bond Price of a bond is the present value of its cash flows. The cash flows are the coupon payments and the face value receivable on maturity Price of bond is calculated using PV function in Excel : rate = 6.5%/2 (Semiannual YTM of bonds = annual YTM / 2) nper = number of years to call date * 2 (years remaining until call date with 2 semiannual coupon payments each year) pmt = 1000 * 8% / 2 (semiannual coupon payment = face value * coupon rate / 2) fv = call price receivable on call date Price of non-callable bond Price of a bond is the present value of its cash flows. The cash flows are the coupon payments and the face value receivable on maturity Price of bond is calculated using PV function in Excel : Rate = 6.5%/2 (Semiannual YTM of bonds = annual YTM / 2) nper = 20 * 2 (20 years remaining until maturity with 2 semiannual coupon payments each year) pmt = 1000 * 8% / 2 (semiannual coupon payment = face value * coupon rate / 2) fv = 1000 (face value receivable on maturity)