1
2
3
4
5
6
11
12
16
17
18
19
20
21
22
23
24
25
26
A B C D E F G H I
Chapter 5 Spreadsheet Problem Solutions (C05)
1. There are a number of instructions with which you should be familiar
to use these computerized models. These instructions appear in a
2. The input data are entered in specified cells in the INPUT DATA
section. When you change an input item, the model automatically
3. Graphs that show the yield curves will be displayed when you click on
the worksheets labeled “Yield Curve” and “Corporate.”
INPUT DATA:
Current year (end of year) 0
Real risk free rate 2.0%
Expected inflation Year
16.0%
24.0%
33.5%
Yield Curve
32
33
34
35
36
37
38
65
66
67
68
73
74
75
76
77
78
79
80
A B C D E F G H I
Years to Real risk-free Inflation Maturity Risk Treasury
Maturity rate (r*) Premium (IP) Premium (MRP) Yield
12.00% 6.00% 2.00% 10.00%
22.00% 5.00% 3.00% 10.00%
32.00% 4.50% 3.00% 9.50%
42.00% 4.13% 3.00% 9.13%
To this point, we have constructed yield curves based upon hypothetical data. The first yield curve operates under
the simple assumption that inflation is expected to rise in the future. To some extent, actual yield curves are
CORPORATE BONDS
Default Risk Premiums and the Liquidity Premium
The construction of corporate yields is a process of beginning with the appropriate Treasury yield curve and adding in
these additional yield premiums. However, the determination of these premiums can be tricky. It seems logical that
87
88
89
90
91
92
93
100
101
102
103
104
105
106
107
108
109
138
139
140
141
A B C D E F G H I
Bond Rating Default spread
AAA 1.00%
B2.80%
This tells us the average default spreads of corporate securities with various bond ratings. We will use this data as the
starting point for our corporate yield curves. Naturally, the first question that arises is, “Does the default risk premium
change, or does it always stay the same?”. Logically, the idea of a time-varying default risk premium seems fairly
With all of that having been said, we can step forward and try to construct corporate yield curves.
Naturally, yield curves can be created for corporate bonds of any rating. However, we have chosen to create curves for
only AAA and B rated bonds. This exercise is for purely illustrative purposes, so rather than complicate the graph
with a lot of curves, we will create two curves to show the relationship between yield curves.
Years to Real risk-free Inflation Maturity Risk Treasury AAA-rating AAA-rated B-rating B-rated
Maturity rate Premium (IP) Premium (MRP) yield DRP bond yield DRP bond yield
12.00% 6.00% 2.00% 10.00% 1.00% 11.00% 2.80% 12.80%
22.00% 5.00% 3.00% 10.00% 1.02% 11.02% 2.86% 12.86%
Looking at the yield curve we have constructed, we see a relationship that we should have expected. We see that at any
length to maturity, the yield on corporate bonds is always greater than the yield on Treasuries. This is logical because
corporate securities carry a default risk, and Treasuries do not. Furthermore, we observe that at any length to maturity
142
147
A B C D E F G H I
the corporate security with the lower rating always has a higher yield than a corporate bond with a higher rating. Once
7%
8%
9%
10%
11%
8%
10%
12%
14%
We have already entered the base case data for each model in this
file, and the models have performed the analysis for preceding parts
of the problem. You will need to enter the data for each of the
remaining parts of the problem–we indicate in each problem the parts
that should be done using the spreadsheet. However, there are several
points worth noting before you go into a model:
1. The input data are entered in specified cells in the INPUT DATA
section. When you change an input item, the model automatically
2. The key output data are displayed to the right of the INPUT DATA
3. Input data items that you can change are distinguished from the
ones you should not change. The items that you can change are
highlighted in color (blue) whereas the other items are printed in black.
4. All percentages must be entered as decimals. Dollars and other
numbers must be entered without dollar signs or commas.
5. Instructions and comments concerning specific models accompany
GENERAL INSTRUCTIONS FOR COMPUTERIZED PROBLEM SOLUTIONS