Question

b) You are required to produce 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 996 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 5%. Assume that interest rates do not change.

Answer 1

Base case
YTM	5%
FV of bond	1000
Coupon rate	9%
Tenure of bond (yrs)	10
Settlement date	4/25/2019
Maturity date	4/25/2029

Scenario 1
Change in price of bond every year until maturity. The settlement date will change for each calculation. We will use the excel function to find the value of the bond at each settlement date as follows-
=PRICE(settlement date= 4/25/19 to 4/25/29,maturity date= 4/25/29,coupon rate= 9%,YTM= 5%,redemption value= 1000, coupon frequency= semiannual= 2,1)

Scenario 2
We will proceed in this scenario in a similar way as a previous case but change the YTM to 7% instead of 5%.

Scenario 3
Capital gain/ loss is calculated by subtracting the price of bond under scenarios 1 from that in scenario 2, for every year

Scenario 4
Interest rate risk (%) is calculated by dividing the capital gain/ loss calculated in the previous case with the price of the bond in scenario 1, for every year.

	Scenario 1- 5% YTM	Scenario 2- 7% YTM	Scenario 3	Scenario 4
Years to maturity	Price of bond	Price of bond	Capital gain/ (loss)	Interest rate risk (%)	Settlement date
10	131.178	114.212	(16.97)	-12.93%	4/25/2019
9	128.707	113.190	(15.52)	-12.06%	4/25/2020
8	126.110	112.094	(14.02)	-11.11%	4/25/2021
7	123.382	110.921	(12.46)	-10.10%	4/25/2022
6	120.516	109.663	(10.85)	-9.00%	4/25/2023
5	117.504	108.317	(9.19)	-7.82%	4/25/2024
4	114.340	106.874	(7.47)	-6.53%	4/25/2025
3	111.016	105.329	(5.69)	-5.12%	4/25/2026
2	107.524	103.673	(3.85)	-3.58%	4/25/2027
1	103.855	101.900	(1.96)	-1.88%	4/25/2028
0	100.000	100.000	-	0.00%	4/25/2029

Interest rate risk (%) 0.00% 10 0 2.00% 4.00% こ -6.00% -8.00% 10.00% 12.00% 14.00% Years to maturity