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 rates--you
don't want to buy long-term 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 7-3)
A bond has a 15-year 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