CFIN6
Spreadsheet Problem Solution
Chapter 6
a. & b.
INPUT DATA:
KEY OUTPUTS:
Years remaining to maturity
17
Current price (P0)
$847.63
Coupon rate of interest
9%
Capital gains yield this
year
0.39%
Interest payments per year
Current yield this year
Market rate (yield to maturity), rd
Total yield this year
Maturity value
MODEL-GENERATED DATA:
Years to
Maturity
Beginning of
Year Value ($)
End of
Year
Value ($)
Capital
Gains
($)
Capital
Gains Yield
(%)
Interest
($)
Current
Yield (%)
Total Yield
(%)
20
19
18
17
$847.63
$850.96
$3.33
0.39%
$90
10.62%
11.01%
16
$850.96
$854.66
$3.70
0.43%
$90
10.58%
11.01%
15
$854.66
$858.79
$4.13
0.48%
$90
10.53%
11.01%
14
$858.79
$863.38
$4.59
0.53%
$90
10.48%
11.01%
13
$863.38
$868.48
$5.10
0.59%
$90
10.42%
11.01%
12
$868.48
$874.17
$5.69
0.66%
$90
10.36%
11.02%
11
$874.17
$880.50
$6.33
0.72%
$90
10.30%
11.02%
10
$880.50
$887.54
$7.04
0.80%
$90
10.22%
11.02%
9
$887.54
$895.38
$7.84
0.88%
$90
10.14%
11.02%
8
$895.38
$904.10
$8.72
0.97%
$90
10.05%
11.03%
7
$904.10
$913.81
$9.71
1.07%
$90
9.95%
11.03%
6
$913.81
$924.62
$10.81
1.18%
$90
9.85%
11.03%
5
$924.62
$936.65
$12.03
1.30%
$90
9.73%
11.03%
4
$936.65
$950.04
$13.39
1.43%
$90
9.61%
11.04%
3
$950.04
$964.95
$14.91
1.57%
$90
9.47%
11.04%
2
$964.95
$981.54
$16.59
1.72%
$90
9.33%
11.05%
1
$981.54
$1,000.00
$18.46
1.88%
$90
9.17%
11.05%
0
CFIN6
c.
INPUT DATA:
KEY OUTPUTS:
Years remaining to maturity
10
Current price (P0)
$1,067.95
Coupon rate of interest
9%
Capital gains yield
this year
–0.44%
Interest payments per year
Current yield this
year
Market rate (yield to maturity), rd
8%
Total yield this year
Maturity value
MODEL-GENERATED DATA:
Years to
Maturity
Beginning of
Year Value ($)
End of
Year
Value ($)
Capital
Gains
($)
Capital
Gains
Yield (%)
Interest
($)
Current
Yield (%)
Total Yield
(%)
20
19
18
17
16
15
14
13
12
11
10
$1,067.95
$1,063.30
($4.65)
–0.44%
$90
8.43%
7.99%
9
$1,063.30
$1,058.26
($5.04)
–0.47%
$90
8.46%
7.99%
8
$1,058.26
$1,052.82
($5.44)
–0.51%
$90
8.50%
7.99%
7
$1,052.82
$1,046.93
($5.89)
–0.56%
$90
8.55%
7.99%
6
$1,046.93
$1,040.55
($6.38)
–0.61%
$90
8.60%
7.99%
5
$1,040.55
$1,033.66
($6.89)
–0.66%
$90
8.65%
7.99%
4
$1,033.66
$1,026.21
($7.45)
–0.72%
$90
8.71%
7.99%
3
$1,026.21
$1,018.15
($8.06)
–0.79%
$90
8.77%
7.98%
2
$1,018.15
$1,009.43
($8.72)
–0.86%
$90
8.84%
7.98%
1
$1,009.43
$1,000.00
($9.43)
–0.93%
$90
8.92%
7.98%
0
$1,000.00
CFIN6
d.
INPUT DATA:
KEY OUTPUTS:
Original maturity (years)
15
Yields to new investor (buyer):
Coupon rate of interest
7%
Yield to maturity, rd
8.00%
Interest payments per year
Maturity value
Purchase price
Number of years held
Yields to the original investor (Assumes the current market rate, YTM, remains the same each year the bond is held as
when the bond was purchased, except in the final year.)
# Years Bond
was Held
Beginning
of Year
Value ($)
End of
Year Value
($)
Capital
Gains ($)
Capital
Gains Yield
(%)
Interest
($)
Current
Yield (%)
Total Yield
(%)
1 $913.54 $916.68 $3.14 0.34% $70 7.66% 8.01%
2 $916.68 $920.09 $3.40 0.37% $70 7.64% 8.01%
e. INPUT DATA: KEY OUTPUTS
Original maturity (years) 13 Yield to maturity, rd 6.60%
Coupon rate of interest 7%
Interest payments per year 2
CFIN6
# Years Bond
was Held
Beginning of
Year Value ($)
End of
Year Value
($)
Capital
Gains ($)
Capital
Gains Yield
(%)
Interest
($)
Current
Yield (%)
Total Yield
(%)
1 $1,034.55 $1,032.80 ($1.75) –0.17% $70 6.77% 6.60%
2 $1,032.80 $1,030.94 ($1.87) –0.18% $70 6.78% 6.60%