Question

The Rowan Corp has two bond issues outstanding. Both bonds pay $90 annual interest plus $1,000 at maturity. Bond 1 has a maturity of 30 years, and Bond 2 has a maturity of 5 year.

1. What will be the value of each of these bonds when the going rate of interest is
   1. 5%
   2. 10%
   3. 15%
   4. 20%
2. Using Microsoft Excel’s relevant functions, calculate the bond prices.
3. Create a table to summarize your results.
4. Prepare a graph displaying your results. Make sure both bonds are displayed on one graph. Make the x-axis interest rate and y-axis bond prices.
5. Prepare a graph displaying the value of the bonds over time. Make sure both bonds are displayed on one graph. Make the x-axis years and y-axis bond prices.
   1. Assuming that the interest rate in the market stays at 5%.
   2. Assuming that the interest rate in the market stays at 15%.

Answer 1

Formula:

Where,

r = Interest Rate

n = Time Period (years)

Given:

Maturity Amount = $1000, Annual Interest = $90.

Maturity time:

Bond 1 - 30 years, Bond 2 - 5 years

Solution:

Bond 1:

Rate	5%	10%	15%	20%
Year
1	85.71	81.82	78.26	75.00
2	81.63	74.38	68.05	62.50
3	77.75	67.62	59.18	52.08
4	74.04	61.47	51.46	43.40
5	70.52	55.88	44.75	36.17
6	67.16	50.80	38.91	30.14
7	63.96	46.18	33.83	25.12
8	60.92	41.99	29.42	20.93
9	58.01	38.17	25.58	17.44
10	55.25	34.70	22.25	14.54
11	52.62	31.54	19.34	12.11
12	50.12	28.68	16.82	10.09
13	47.73	26.07	14.63	8.41
14	45.46	23.70	12.72	7.01
15	43.29	21.55	11.06	5.84
16	41.23	19.59	9.62	4.87
17	39.27	17.81	8.36	4.06
18	37.40	16.19	7.27	3.38
19	35.62	14.72	6.32	2.82
20	33.92	13.38	5.50	2.35
21	32.30	12.16	4.78	1.96
22	30.77	11.06	4.16	1.63
23	29.30	10.05	3.62	1.36
24	27.91	9.14	3.14	1.13
25	26.58	8.31	2.73	0.94
26	25.31	7.55	2.38	0.79
27	24.11	6.86	2.07	0.66
28	22.96	6.24	1.80	0.55
29	21.87	5.67	1.56	0.45
30	252.20	62.47	16.46	4.59
Total	1614.90	905.73	606.04	452.32

Bond 2:

Rate	5%	10%	15%	20%
Year	Bond 2
1	85.71	81.82	78.26	75.00
2	81.63	74.38	68.05	62.50
3	77.75	67.62	59.18	52.08
4	74.04	61.47	51.46	43.40
5	854.04	676.80	541.92	438.05
Total	1173.18	962.09	798.87	671.03

Part 1:

Summary:

Interest Rate	Bond 1	Bond 2
5%	1614.90	1173.18
10%	905.73	962.09
15%	606.04	798.87
20%	452.32	671.03

Graph:

Part 2:

1. Bond Value over time at 5% interest rate:

2. Bond Value over time at 15% interest rate:

Both Combined: