Question

14. Consider a bond that has a coupon of 8 percent paid semiannually and has a maturity of 5 years. The bond is currently selling for $1,047.25. Use Excel to do the following analysis.

a. What is its yield-to-maturity?

b. Compute its duration.

c. If interest rates are expected to increase by 75 basis points, what is the expected dollar change in price? What is the expected percentage change in price?

I only need b. I know how to do c and I already did a on paper, I just need a confirmation. Also, if you could tell me how to correctly input these in excel, that would be nice too. Please respond ASAP

Answer 1

a. Yield to maturity means Expected or Desired return

and the formula is Interest amount / Current selling price

so yield to maturity = (80 /1047.25) * 100 = 7.639%

Note: Interest is Calculated @8% on $1000 (Face Value)

b. Duration of bond is the weighted average time period upto which our initial investment is totally recovered.

Formula of duration of bond = Total Weighted average amount of interest and principal / Total Discounted value of interest and principal amount.

Year Total Discounted value Total weighted average value of interest and principal

1 80*.929= 74.32 74.32

2 80* .863=69.04 138.08

3 80* .802=64.16 192.48

4 80* .745=59.60 238.40

5 80* .692=55.36 276.80

5 1000* .692 =.692 3460

Total 1014.48 4380.08

So Duration of Bond= 4380.08 / 1014.48 = 4.32 Years

Assumptions: It is assumed redeemable value is $1000 at the end of 5th year

Discount factor is calculated on the basis of yield in point a .

c. new Yield to maturity = 7.639% + .75% =8.389%

New Price of Bond = Interest* PVAF (5 yrs,8.389%) + Maturity amount * PVF (5th year, 8.389%)

So new Price of Bond= 80* 3.952 + 1000* .668 = 984.16

  Expected Dollar change in price = 1047.25 - 984.16 = 63.09

  Percentage change in price = (63.09 / 1047.25) * 100 = 6.024%