07 Chapter model
12/10/2008
Chapter 7.
Bonds and Their Valuation
INPUT DATA
Years to Maturity
15
Coupon rate
10%
You know that 10% = 0.10, so rates can be shown as percents
Annual Payment
$100
or as fractions. Excel works with fractions, so it reads 10% as
Par value
$1,000
0.10.
10%
The value of any financial asset is the present value of the asset's expected future cash flows. The key
inputs are (1) the expected cash flows and (2) the appropriate discount rate, given the bond's risk,
maturity, and other characteristics. The model developed here analyzes bonds in various ways.
Bond valuation requires keen judgment with regard to assessing the riskiness of the bond, i.e., what is
the likelihood that the promised coupon and maturity payments will actually be made at the scheduled
times? Also, investing in bonds requires one to make implicit forecasts of future interest ratesyou
don't want to buy longterm bonds just before a sharp increase in interest rates.
We do not deal with
these important but subjective issues in this spreadsheet.
Rather, we concentrate on the actual
calculations used, given the inputs.
BOND VALUATION
(Section 73)
A bond has a 15year maturity, a 10% annual
coupon, and a $1,000 par value.
The required rate of return
on the bond is 10%, given its risk, maturity, liquidity, and other rates in the economy. What is a fair
value for the bond, i.e., its market price?
Required rate, r
d
We find the bond's price using Excel's PV function.
First, put the pointer on cell C42, where we want to
put the bond's value.
Then click fx on the Formula Line, then Financial, and then PV to get the following
dialog box. Fill the box in as shown below.
A
B
C
D
E
F
G
H
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Value of bond =
$1,000.00
Thus, this bond sells at its par value.
That situation always exists if the required rate is equal to
the coupon rate.
Required
Bond
Type in the labels as shown,
Rate
Value
(r)
1000
Enter =C42
here to display the value calculate in C42.
0%
$2,500.00
Type in the interest rates as shown in Column A.
5%
$1,518.98
Highlight the area A54:B59.
10%
$1,000.00
Click on Data > Table to get this dialog box.
The interest rate is the
15%
$707.63
input variable which enters the
20%
$532.45
model in cell C17.
Enter it and
then click OK. That causes
Excel to calculate the bond's
value at the rates shown in
Cells A55:A59 and then display
those values in B55:B59.
Suppose the required rate r
d
(a) fell from 10% to 5% or (b) rose to 15%.
How would those changes affect
the value of the bond?
First, change cell C17 from 10% to 5% and observe that the bond's value rises to $1,518.98. Then
change C17 to 15% and note that the value declines to $707.63. You can see that the bond's price falls
when the going interest rate (or required rate of return) rises, and the price rises when the interest rate
falls.
Finish by resetting C17 to 10%.
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '10
 na
 Interest Rates

Click to edit the document details