Question

Your boss is back. This time he/she provides you a partial model to a bond valuation. This bond is a 20-year, 8% semiannual coupon bond with a par value of $1,000 may be called in 5 years at a call price of $1,040. The bond sells for $1,100. (Assume that the bond has been issued.) She needs you to complete the partial model for her. She needs the following to be answered.

• What is the bond's yield to maturity?
• What is the bond's current yield?
• What is the bond's capital gain or loss yield?
• What is the bond's yield to call?
• How would the price of the bond be affected by a change in the going market interest rate? (Hit: Conduct a sensitivity analysis of price to changes in the going market rate for the bond. Assume the bond will be called if and only if the going rate of interest falls below the coupon rate. This is an oversimplification, but assume it for the purpose of this problem.)
• Now assume the date is October 25, 2017. Assume further that a 12%, 10-year bond was issued on July 1, 2017, pays interest semiannually (on January 1 and July 1), and sells for $1,00. Use the attached spreadsheet to find the bond's yield.
  Assignment 3 Home Insert Draw Page Layout Formulas Data Review View Cut AutoSum v General Copyv Fill v Paste Format B 1 uv田v 오ν 스 国Merge & Contor v $ v % , 'il 웨 conditional Forable Styles insert Delete Format X Clear v C9 A 20-year, 8% semiannual coupon bond with a par value of $1,000 may be called in 5 years at a call price osi 040 The bond sells for $1,100 Assume that the bond has just been issued.) Basic Input Data ar o maturty: Pernods per yoar Peñods to malurity Coupon sate Parvalue Perodia payment: Cument pice Call price: $1,000 $1,040 calabk: What is the bond's yield to maturity7 Peridodic YTM 21 Annualized Nominal YTMHint: This is a nominal rabo, not tho etlactivo rato. Nominal rates ano genaralty quotac 23 b. What is the bond's current yield? 26 27 28 Current yield Curront yiold Curront yiold Hnt Wite formula in words Hint Col fomulas chould rufor to Input Socton 30 What is the bond's capital gain or loss yield? 33Cap. Gaivloes yield Hnt Wito fomua in words Hint Col omulas should rofor to Input Socton Cap. Gain/loss yiold- Cap. Galloss ylold Nose that this is an economic loss, not a loss for tax purposes is the bond's yield to yield to call? Hane we can again use tho Ratefunction, but with data reated to the cal Peridodic YTC Annuslized Nominal YTCL This is a nominal rato, not tho aftodivo rato. Nominal ratos aro gunoraly quoiod Tho YTC lower than the YTM bocauso rf the bond is calod. 1ho buyorwil loDo tho dittorunce botweon tho cal pnce and tho curent proo ī)·st 4 yuars. and 1hat loss wi omsat much of the i torst income. Noto too that the bond likely to be caked and replacad, hana that the YTC wl probably be eamed. Build a Model Solution Sheet3 囲回凹-
  Please try and fill in all yellow spaces and show how you got the answers.
• Thank You

Answer 1

As HOMEWORKLIB RULES rule i will be solving four subparts (a to d)
Basic input date:
Years to maturity=20
Period per year=2
Periods to maturity=20*2=40
coupon rate=8%
par value=1000
periodc payment=(1000*8%/2)=40
current price=1100
call price=1040
periods if callable=5*2=10

a)Periodic YTM
=rate(nper,pmt,pv,fv,type)
=rate(40,400,-1100,1000,0)=4.09%
Annualozed nominal YTM=2*4.09%=8.19%

b)Current yield= periodic payment/price of bond
=80/1100
=7.27%

C)Capital gain/loss yield=YTM-current yield
=8.19%-7.27%
=0.92%

d)periodic YTC=
=rate(nper,pmt,pv,fv,type)
=rate(10,40,-1100,1040,0)=4.30%
Annualized nominal YTC=2*4.30%=8.59%