Problema de en excel

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