07model
10/6/2009 8:16
12/2/2002
Chapter 7.
Spreadsheet Models for Analyzing Bonds
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.
Note that bond calculations are just arithmetic exercises, and that problems can be set up and solved in a number of
different ways.
This is especially true for spreadsheets models, which can be set up using the function wizard or
not, and using different algebraic formulations.
So, if you were making your own models, you might well set things
up differently than our setups.
Also note that many of the bonds in this spreadsheet pay annual coupons, though
most bonds pay interest semiannually.
It is simpler to work with annual payments when discussing basic concepts.
BOND VALUATION
return (or the yield to maturity) 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?
INPUT DATA
Years to Mat:
15
Coupon rate:
10%
Annual Pmt:
$100
Par value = FV:
$1,000
Going rate, k:
10%
the menu items as shown in our snapshot in the screen shown just below.
Value of bond =
$1,000.00
Thus, this bond sells at its par value.
That situation always exists if the going
rate is equal to the coupon rate.
The PV function can only be used if the payments are constant, but that is normally the case for bonds.
A bond has a 15-year maturity, a 10% annual
coupon, and a $1,000 par value.
The required rate of
The easiest way to solve this problem is to use Excel's PV function.
Click f
x
, then financial, then PV.
Then fill in
A
B
C
D
E
F
G
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62