# Sheet1 - ExcelModels by linzhengnd

VIEWS: 8 PAGES: 5

• pg 1
```									Chapter 9 Homework Solutions

Problem 1         Conifer Coal Company
Time to Maturity         8
Coupon Rate            7.5%
Required Return         9%
Frequency                2
Face Value            \$1,000
Redemption              100
Current Price          \$900

Part A       Fair Market Value:     \$915.74

Part B       Current Yield:           8.3%
1-Year Yield:           10.9%        found by:

The two Yield numbers are different because you must take into account apprec
If you ignore the appreciation in bond price, the Current Yield would, by definit

Part C      Two wasy to calculate Yield to Maturity:
Method 1: Use RATE ( ) function for Annuities

Method 2: YIELD ( ) with dates 8 years apart:
Settlement      Maturity
7/30/2012     7/30/2020

Part G               Yield/Price Data

There are three ways to calculate the bond's price given its expected yield:
Using PRICE( ) function is the easiest, followed by the PV( ) function. Use the
Price
Yield:    1: Use PV( )   2: PRICE( )
0%         \$1,600.00     \$1,600.00
1%         \$1,498.55     \$1,498.55
2%         \$1,404.74     \$1,404.74
3%         \$1,317.95     \$1,317.95
4%         \$1,237.61     \$1,237.61
5%         \$1,163.19     \$1,163.19
6%         \$1,094.21     \$1,094.21
7%         \$1,030.24     \$1,030.24
8%          \$970.87       \$970.87
9%            \$915.74        \$915.74
10%           \$864.53        \$864.53
11%           \$816.91        \$816.91
12%           \$772.62        \$772.62
13%           \$731.39        \$731.39
14%           \$692.98        \$692.98
15%           \$657.19        \$657.19

*Recall from Chapter 9 that:
Payment
PV of a bond                 =           Amount
*
Where "rate" = required rate of return, (i.e. bond's Yield to Maturity)

Problem 2                               Bond A                Bond B
Settlement Date         2/15/2010             2/15/2010
Maturity Date           4/15/2014             6/15/2025
Coupon Rate                 5%                   9.5%
Price                      \$890                 \$1,040
Required Return           7.25%                 9.25%
Face Value                \$1,000                \$1,000
Redemption                  100                   100
Frequency                    2                     2

Part A     Intrinsic Prices:            \$920                \$1,020    ← Remember the results of the Price( ) function mus
Value                     Undervalued           Overvalued

Part B     Yield                        8.16%                  9.01%        ← Remember the Pr parameter (Face Value) must b
Appreciation +           Interest =      Total Return
\$23.33                   \$75           \$98.33

Notice the Time to Maturity (i.e. # Years) in the PV function is reduced by 1

because you must take into account appreciation when calculating the Yield one year later.
d price, the Current Yield would, by definition, equal the value received after 1 year.

9.30%            ← Note: Multiply formula result by 2 because it only returns the semiannual rate

9.30%            ← Note: Must divide Price parameter (4th number in Yield function) by 10
(for the same reason we multiply results of Price function by 10... just a quirk of Excel)

bond's price given its expected yield:
st, followed by the PV( ) function. Use the Annuity equation as a last resort!
Price
3: Annuity formula*
n/a (Div by 0)
\$1,498.55
Bond Price vs. Yield
\$1,800
\$1,404.74
\$1,317.95                       \$1,600
\$1,237.61
\$1,400
\$1,163.19
\$1,094.21                       \$1,200
Price

\$1,030.24
\$1,000
\$970.87
\$915.74                        \$800
\$864.53
\$816.91                        \$600
\$772.62                                 0%   2%   4%     6%         8%      10%       12%   14%   16%
\$731.39                                                  Yield
\$692.98
\$657.19

1 -                     1
Future Value
(1 + rate) ^ Periods             +              (1 + rate) Periods
rate
bond's Yield to Maturity)

Price Function Parameters            Yield Function Parameters
Parameter 1: (Settlement)            Parameter 1: (Settlement)
Parameter 2: (Maturity)              Parameter 2: (Maturity)
Recall the parameters       Parameter 3: (Rate)                  Parameter 3: (Rate)
for the two functions:      Parameter 4: (Yield)                 Parameter 4: (Price)
Parameter 5: (Redemption)            Parameter 5: (Redemption)
Parameter 6: (Frequency)             Parameter 6: (Frequency)
Parameter 7: (Basis)                 Parameter 7: (Basis)

← Remember the results of the Price( ) function must be multiplied by 10, and

← Remember the Pr parameter (Face Value) must be divided by 10 in the Yield( ) function
s the semiannual rate

function) by 10
quirk of Excel)

```
To top