Docstoc

Sheet1 - ExcelModels

Document Sample
Sheet1 - ExcelModels Powered By Docstoc
					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)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:11/17/2011
language:English
pages:5