Problema de en excel

Topics: Bond, Yield curve, Investment Pages: 9 (547 words) Published: March 30, 2014
Michelle_Perez_ADCO5190_CH1_P08

4/2/2003

Chapter 1. Ch 01 P08 Build a Model
a. Suppose you are considering two possible investment opportunities, a 12-year Treasury bond and a 7-year, A-rated corporate bond. The current real risk-free rate is 4%. Inflation is expected to be 2% for the next two years, 3% for the following four years, and 4% thereafter. The maturity risk premium is estimated by this formula:MRP = 0.1% ( t-1) %. The liquidity premium for the corporate bond is estimated to be 0.7%. Finally, you may determine the default risk premium, given the company’s bond rating, from the default risk premium table in the text. What yield would you predict for each of these two investments? Treasury Bond

Risk-free rate

=

4.00%

Maturity:
Expected inflation:
Expected inflation:
Expected inflation:

12
for the next
for the next
for the next

Inflation premium =

2
years =
4
years =
6
years =
12
((G17*D17)+(G18*D18)+(G19*D19))/D20

3.33%

Maturity risk premium

=

1.1%

0.1*(C16-1)%

12-year Treasury yield=

2%
3%
4%

8.43%

7-year corporate bond
Rating :
A
Risk-free rate

=

Maturity:
Expected inflation:
Expected inflation:
Expected inflation:
Inflation premium =

4%
7
for the next
for the next
for the next

2
years =
4
years =
1
years =
7
((G33*D33)+(G34*D34)+(G35*D35))/D36

2%
3%
4%
2.86%

Maturity risk premium= 0.1*(C32-1)%
0.60%
Liquidity premium=
0.7%
Default risk premium= IF(B28=H38,I38,IF(B28=H39,I39,IF(B28=H40,I40,IF(B28=H41,I41)))) 1.5%
(see screen to right for an alternative way to find the
default risk premium.)
7 year Corporate yield=
9.66%
Yield Spread = Corporate - Treasury =
R:econciliation:
Default premium
Liquidity premium
Inflation premium
Maturity premium

1.224%
1.500%
0.700%
-0.476%
-0.500%
1.224%

Default Risk from
text table:
Rating
DRP
AAA
1.0%
AA
1.2%
A
1.5%
BBB
1.9%
BB+
3.7%

b. Given the following Treasury bond yield information from the September 28, 2001, Federal Reserve Statistical Release, construct a graph of the yield curve as of that date.
Maturity
Periods
Years
Yield
3 month
0.25
1.16%
6 month
0.50
1.17%
1 year
1.00
1.25%
2 year
2.00
1.62%
3 year
3.00
2.05%
5 year
5.00
2.92%
7 year
7.00
3.50%
10 year
10.00
3.95%
20 year
20.00
4.96%
Now we can use Excel's chart wizard to construct a yield curve.

Yield Curve
6.00%
4.00%
Yield

2.00%
0.00%
0.25

0.50

1.00

2.00

3.00

5.00

7.00

10.00 20.00

c. Based on the information about the corporate bond that was given in Part a, calculate yields and then construct a new graph that shows both the Treasury and the corporate bonds. The real risk-free rate would be the same for the corporate and treasury bonds. Similarly, without information to the contrary, we would assume that the maturity and inflation premiums would be the same for bonds with the same maturities. However, the corporate bond would have a liquidity premium and a default premium. If we assume that these premiums are constant across maturities, then we can use the LP and DRP premiums as determined above and add them to the T-bond yields to find the corporate yields. This procedure was used in the table below.

Years
0.25
0.50
1.00
2.00
3.00
5.00
7.00
10.00
20.00

Treasury A-Corporate
1.16%
3.36%
1.17%
3.37%
1.25%
3.45%
1.62%
3.82%
2.05%
4.25%
2.92%
5.12%
3.50%
5.70%
3.95%
6.15%
4.96%
7.16%

Spread
2.20%
2.20%
2.20%
2.20%
2.20%
2.20%
2.20%
2.20%
2.20%

LP
0.7%
0.7%
0.7%
0.7%
0.7%
0.7%
0.7%
0.7%
0.7%

DRP
1.5%
1.5%
1.5%
1.5%
1.5%
1.5%
1.5%
1.5%
1.5%

Now we can graph the data in the first 3 columns of the above table to get the Treasury and corporate (A-rated) yield

curves:

Treasury and Corporate Yield Curves
8.00%
7.00%
6.00%
5.00%
4.00%

Treasury

3.00%

A-Corporate

2.00%
1.00%

0.00%
0.25

0.50

1.00

2.00...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • ENS 303 Essay
  • Excel Essay
  • Prise En Main De Dropbox Essay
  • Essay on excel amortization
  • Excel Essay
  • Essay on Metodos de Análisis de Problemas
  • Essay on L'Aventures Des Langues En Occident (Grec)
  • Las Frases En Investigación de Mercados Essay

Become a StudyMode Member

Sign Up - It's Free