Question

roduce a table showing bond values and interest rate risk over the duration of a bond and a diagram demonstrating the link between interest rate risk and time to maturity. The bond has a face value of $1,000, pays a coupon rate of 9% and is issued with 10 years to maturity. All calculations should be executed in excel. Your table should show the following:

•  The value of the bond, year by year, from date of issue until its maturity, assuming all other things remain the same.

•  The value of the bond, year by year, from date of issue until its maturity, assuming that market interest rate increases by 2%, all other things remain the same.

•  The potential capital gain/ loss, year by year, from the date of issue until its maturity

•  The interest rate risk (%), year by year, for each year of maturity.
  From your table produce a diagram that demonstrates the relationship between interest rate risk and time to maturity.

  The market interest rate (yield to maturity) to be used is 12%, assume that interest rates don't change

Answer 1

here we calculated interest rate risk by having 1 basis point change in interest rate.

To calculate the value of bond =PV(0.12,F2,90,1000) here time to maturity is variable from 10 to 1

To calculate the value of bond with increasing interest rate =PV(0.12+0.02*(C1),F2,90,1000) here time to muturity and interest rate both are variable.

to calculate profit = subtract the price of bond by previous year's price

price when 1bps change in interest rate  =PV(0.1199,F2,90,1000)

interest rate risk = (price of bond after interest rate change - original price)/original price