Sheet1
Bond Valuation
Bond A Bond B Bond C
Years to maturity 12 12 12
Number of coupon payment per year 1 1 1
Coupon rate 10% 6% 14%
Par value $1,000 $1,000 $1,000
Yield to maturity 10% 10% 10%
b. Calculating the price of each of the three bonds Formulas
Bond A Bond B Bond C Bond A Bond B Bond C
VB0 ERROR:#N/A ERROR:#N/A ERROR:#N/A
c. Calculating the cu
ent yield for each of the three bonds
Bond A Bond B Bond C Bond A Bond B Bond C
Cu
ent yield ERROR:#N/A ERROR:#N/A ERROR:#N/A
d. Calculating the price of each bond 1 year from now, the expected capital gains yield for each bond,
and the expected total return for each bond
Bond A Bond B Bond C
Years to maturity 11 11 11
Bond A Bond B Bond C
VB1 ERROR:#N/A ERROR:#N/A ERROR:#N/A
Expected CG Yield ERROR:#N/A ERROR:#N/A ERROR:#N/A
Expected Total Return ERROR:#N/A ERROR:#N/A ERROR:#N/A
e. Mr. Clark is considering another bond, Bond D.
Years to maturity 8
Number of coupon payment per year 2
Coupon rate 9%
Par value $1,000
Coupon payment $45
Cu
ent price $1,110
Call price $1,020
Years until bond is callable 5
Formulas
(1) Calculating the bond's nominal yield to maturity ERROR:#N/A
(2) Calculating the bond's nominal yield to call ERROR:#N/A
f. Determining which of the bonds has the most price risk and which has the most reinvestment risk
Bond 1 Bond 2 Bond 3 Bond 4 Bond 5
Years to maturity 1 5 5 10 10
Number of coupon payment per year 1 1 — 1 —
Coupon rate 10% 10% — 10% —
Par value $1,000 $1,000 $1,000 $1,000 $1,000
Bond 1 Bond 2 Bond 3 Bond 4 Bond 5
Price at YTM = 10% ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A
Price at YTM = 11% ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A
% Price Change ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A ERROR:#N/A
g. Calculating the price of each bond (A, B, and C) at the end of each year until maturity, assuming interest rates remain constant
Years Remaining
Until Maturity Bond A Bond B Bond C Bond A Bond B Bond C
12 ERROR:#N/A ERROR:#N/A ERROR:#N/A
11 ERROR:#N/A ERROR:#N/A ERROR:#N/A
10 ERROR:#N/A ERROR:#N/A ERROR:#N/A
9 ERROR:#N/A ERROR:#N/A ERROR:#N/A
8 ERROR:#N/A ERROR:#N/A ERROR:#N/A
7 ERROR:#N/A ERROR:#N/A ERROR:#N/A
6 ERROR:#N/A ERROR:#N/A ERROR:#N/A
5 ERROR:#N/A ERROR:#N/A ERROR:#N/A
4 ERROR:#N/A ERROR:#N/A ERROR:#N/A
3 ERROR:#N/A ERROR:#N/A ERROR:#N/A
2 ERROR:#N/A ERROR:#N/A ERROR:#N/A
1 ERROR:#N/A ERROR:#N/A ERROR:#N/A
0 ERROR:#N/A ERROR:#N/A ERROR:#N/A
Creating a graph showing the time path of each bond's value
(1) Calculating the expected cu
ent yield for each bond in each yea
Years Remaining
Until Maturity Bond A Bond B Bond C Bond A Bond B Bond C
12 ERROR:#N/A ERROR:#N/A ERROR:#N/A
11 ERROR:#N/A ERROR:#N/A ERROR:#N/A
10 ERROR:#N/A ERROR:#N/A ERROR:#N/A
9 ERROR:#N/A ERROR:#N/A ERROR:#N/A
8 ERROR:#N/A ERROR:#N/A ERROR:#N/A
7 ERROR:#N/A ERROR:#N/A ERROR:#N/A
6 ERROR:#N/A ERROR:#N/A ERROR:#N/A
5 ERROR:#N/A ERROR:#N/A ERROR:#N/A
4 ERROR:#N/A ERROR:#N/A ERROR:#N/A
3 ERROR:#N/A ERROR:#N/A ERROR:#N/A
2 ERROR:#N/A ERROR:#N/A ERROR:#N/A
1 ERROR:#N/A ERROR:#N/A ERROR:#N/A
(2) Calculating the expected capital gains yield for each bond in each yea
Years Remaining
Until Maturity Bond A Bond B Bond C Bond A Bond B Bond C
12 ERROR:#N/A ERROR:#N/A ERROR:#N/A
11 ERROR:#N/A ERROR:#N/A ERROR:#N/A
10 ERROR:#N/A ERROR:#N/A ERROR:#N/A
9 ERROR:#N/A ERROR:#N/A ERROR:#N/A
8 ERROR:#N/A ERROR:#N/A ERROR:#N/A
7 ERROR:#N/A ERROR:#N/A ERROR:#N/A
6 ERROR:#N/A ERROR:#N/A ERROR:#N/A
5 ERROR:#N/A ERROR:#N/A ERROR:#N/A
4 ERROR:#N/A ERROR:#N/A ERROR:#N/A
3 ERROR:#N/A ERROR:#N/A ERROR:#N/A
2 ERROR:#N/A ERROR:#N/A ERROR:#N/A
1 ERROR:#N/A ERROR:#N/A ERROR:#N/A
(3) Calculating the total return for each bond in each yea
Years Remaining
Until Maturity Bond A Bond B Bond C Bond A Bond B Bond C
12 ERROR:#N/A ERROR:#N/A ERROR:#N/A
11 ERROR:#N/A ERROR:#N/A ERROR:#N/A
10 ERROR:#N/A ERROR:#N/A ERROR:#N/A
9 ERROR:#N/A ERROR:#N/A ERROR:#N/A
8 ERROR:#N/A ERROR:#N/A ERROR:#N/A
7 ERROR:#N/A ERROR:#N/A ERROR:#N/A
6 ERROR:#N/A ERROR:#N/A ERROR:#N/A
5 ERROR:#N/A ERROR:#N/A ERROR:#N/A
4 ERROR:#N/A ERROR:#N/A ERROR:#N/A
3 ERROR:#N/A ERROR:#N/A ERROR:#N/A
2 ERROR:#N/A ERROR:#N/A ERROR:#N/A
1 ERROR:#N/A ERROR:#N/A ERROR:#N/A