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)