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 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.
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 15year 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
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentPROBLEM
Suppose the going interest rate changed from 10%, falling to 5% or rising to 15%.
How would those changes
affect the value of the bond?
We could go to the input data section above and change the value for k from 10% to 5% and then 15%, and observe
the change in value.
Alternatively, we can set up a data table to show the bond's value at a range of rates, i.e. to
show the bond's sensitivity to changes in interest rates.
Bond Value
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '09
 EyupCetin
 Management, Interest Rates, Valuation

Click to edit the document details