Bond Pricing and sensitivity analysis using EXCEL

1, Please develop workbooks of bond pricing with integer number of periods to Maturity,

do bond sensitivity analysis, calculate bond duration, do duration sensitivity analysis, and

calculate portfolio duration. (10 marks)

Table A: Inputs of details of Bond (1 marks)

? Settlement date

? Maturity

? Yield to Maturity ( %)

? Coupon Rate (%)

Table B: Calculate bond price(1 marks)

? No. of Periods to Maturity

? Discount Rate per Period

? Coupon per Period ($)

? Present Value Annuity Factor

? Face value ($)

? Present value factor

? Settlement Price ($)

Table C: See the inverse relationship between yield and price, and the relationship between price

and face value when coupon>, =, and < yield to maturity. For example, (1 marks)

Graph D: Please draw a graph to show the above relationship. (1 marks)

Yield Coupon Present

To No. of Discount Per Value Face Present SETTLEMENT

Maturity Periods Rate Period Annuity Value Value PRICE

(%) to Maturity Per Period ($) Factor ($) Factor ($)

YTM t r CPP PVA(r,t) FV PVF(r,t) P0

2.5% 15 1.25% 3.250 13.6005 100 0.8300 127.201

4.5% 15 3.250 100

6.5% 15 3.250 100

8.5% 15 3.250 100

10.5% 15 3.250 100

12.5% 15 3.250 100

14.5% 15 3.250 100

16.5% 15 3.250 100

18.5% 15 3.250 100

20.5% 15 10.25% 3.250 7.4988 100 0.2314 47.509

Table E: Price sensitivity to coupon rate, to see the percentage change of the bond price with the

percentage change of the coupon rate. (1 marks)

Table F: Price sensitivity to Yield to Maturity. (1 marks)

Table G: Price sensitivity to Time to Maturity. (1 marks)

Table H: Computing the duration of a bond (1 marks)

Table I: Duration sensitivity to Coupon rate (1 marks)

Table J: Duration sensitivity to Yield to Maturity (1 marks)