Menu
Bonds
Formulas & Examples
Basic bond valuation Excel functions Examples
Copyright (c) 1997 Ian H Giddy
Page 1
Bond Val
Basic Bond Valuation Bond price (PV annuity + PV principal) PV=PMT*(1-1/(1+R)^n)/R Example Coupon rate Yield Periods PV Price 8 8.00% 10 53.7 $100.00
PV=FV/(1+R)^n FV Yield Periods PV 100 8.00% 10 46.32
Answer
Bond price with semi-annual payments PV=(PMT/m)*(1-1/(1+R/m)^n*m)/(R/m) Example Coupon rate Yield Frequency Periods PV Price 8 8.00% 2 10 54.4 $100.00
PV=FV/(1+R/m)^n*m FV Yield Periods PV 100 8.00% 2 10 45.64
Answer
Page 2
Excel functions
Bond calculations with Excel functions Use PRICE function to find a bonds price Settle date Mat date Rate Yield Frequency Price 1/1/1997 1/1/2007 8% 9% 2 93.50
Use YIELD function to find a bond's yield to maturity Settle date Mat date Rate Price Frequency Yield 1/1/1997 1/1/2007 8% 95 2 8.76%
Use MDURATION function to find a bond's modified duration Settle date Mat date Rate Price Frequency Modified duration 1/1/1997 1/1/2007 8% 8% 2 6.8
Page 3
Examples
Examples 1 Atlantic Bell issued 10-year bonds one year ago at a coupon rate of 9.25 percent. The bonds make semiannual payments. If the YTM on these bonds is 7.15 percent, what is the current bond price? Use the Excel PV function to calculate the current value of the bond. Coupon rate Yield to maturity Years till maturity Bond value 8.25% 12.00% 7 $82.57
2 IBM issued 12-year bonds two years ago at a coupon rate of 8.4 percent. The bonds make semiannual payments. If these bonds currently sell for 97.5 percent of par value, what is the YTM? Use Excel YIELD function Bond value Par value (redemption value) Coupon rate (annual) Years till maturity Today 11/7/2009 Maturity Yield to maturity
$95.88 41.16606 $100 4.00% 5 11/7/2014 4.95%
3 Stern Investments has 14 percent coupon bonds issued by Rotten Tree Inc with seven years to maturity. The bonds mak semiannual payments and currently sell for 105 percent of par. What is the current yield on the bonds? The YTM? The effective annual yield? Use Excel YIELD and EFFECT functions Bond value Par value (redemption value) Coupon rate (annual) Years till maturity Today 11/7/2009 Maturity Current yield Yield to maturity Effective annual yield
$105.00 $100 14.00% 7 11/7/2016 13.33% 12.89% 13.31%
4 The YTM on a bond is the interest rate you earn on your investment if interest rates don't change. If you actually sell the bond before it matures, your realized return is known as the holding period yield (HPY). a. Suppose that today you buy a GE 9 percent coupon bond making annual payments of $1,200. The bond has 10 years to maturity. What rate of return do you expect to earn on your investment? b. Two years from now, the YTM on your bond has declined by 2.5 percent, and you decide to sell. What price will your bond sell for? What is the HPY on your investment? Compare this to the YTM when you first bought the bond. Why are they different? Use Excel YIELD and PRICE functions Bond value Par value (redemption value) Coupon rate (annual) Years till maturity
$120.00 $100 9.00% 10
Page 4
Examples
Today Yield to maturity
11/7/2009 Maturity
11/8/2019 6.28%
2 years later New yield Par value (redemption value) Coupon rate (annual) Years till maturity Today 11/8/2011 Maturity New Price Holding period yield Bond value Par value (redemption value) Coupon rate (annual) Years till maturity Today 11/7/2009 Maturity Holding period yield
3.78% $100 9.00% 8 11/8/2019 135.77
$120.00 135.77 9.00% 2 11/8/2011 13.44%
5 An EDS Corp. bond carries an 8 percent coupon, paid semiannually. The par value is $1,000 and the bond matures in six years. If the bond currently sells for $911.37, what is its yield to maturity? What is the effective annual yield? Settle date Mat date Rate Price Frequency Yield EAY 1/1/1997 1/1/2003 8% 91.137 2 10.00% 10.25%
6 A callable bond pays annual interest of $60, has a par value of $1,000, matures in 20 years but is callable in 10 years at a price of $1,100, and has a value today of $911.90. The yield to call on this bond is: Settle date Mat date Rate Price Frequency Yield 1/1/1997 1/1/2007 6% 91.19 1 7.27%
7 A coupon bond which pays interest annually,
Page 5
Examples
has a par value of $1,000, matures in 5 years and has a yield to maturity of 12%. If the coupon rate is 9%, the value of the bond today will be: Settle date Mat date Rate Yield Frequency Price 1/1/1997 1/1/2002 9% 12% 1 89.19
8 As a portfolio manager at Putnam Management, you bought a 10-year, 8.25% semiannual-pay bond at issuance 2 1/2 years ago. At that time the yield to maturity was 8.23%; it's now fallen to 7.10%. How much has the bond's modified duration changed? Original Present 1/1/1997 7/1/1999 1/1/2007 1/1/2007 8.25% 8% 8.23% 9% 2 2 6.7 5.5
Settle date Mat date Rate Yield Frequency
Modified duration
9 You are the risk manager at a new savings bank, Lostyur Trust. Mr Edgar Lostyur, sole shareholder, has been told by the regulatory authorities to provide them with the institution's modified duration in order to evaluate its sensitivity to interest rate fluctuations. The Prime rate is 8.25%. You find that the initial balance sheet of the savings bank looks like this: Assets: Cash on deposit 2 million face value 3-month loans at Prime + 2% 13 million face value 4-year, 6% s.a. Treasury bonds yielding 6.35% 20 million face value 7-year, 7.90% s.a. IBM bonds yielding 8.23% 6 million face value Liabilities: 6-month CDs at 4% 5-year 9% s.a. bonds Equity:
3 million face value 33 million face value 100,000 shares
What can you tell the regulators the bank's modified duration of assets is? Of liabilities? And what is the net modified duration? Based on the net duration, what would be the effect on net worth of a 10bp increase in rates?
Item Face value Settle date Mat date
Cash 2 1/1/1997 1/1/1997
Loans Treas note IBM bond 13 20 6 1/1/1997 1/1/1997 1/1/1997 4/1/1997 1/1/2001 1/1/2004
6-mo CD 5-yr bond 3 33 1/1/1997 1/1/1997 7/1/1997 1/1/2002
Equity
Page 6
Examples
Rate Yield Frequency Price Value Modified duration If rates rose by and this is
100 2 0
10.25% 10.25% 2 100.00 13.00 0.2
6.00% 6.35% 2 98.78 19.76 3.5
7.90% 8.23% 2 98.27 5.90 5.3
4.00% 4.00% 2 100.00 3.00 0.5
9.00% 9.00% 2 100.00 33.00 4.0 46.52 4.65
0.10% value would change by 0.028661 million 0.62% of 4.65 net worth
Page 7
Examples
ake semiannual
years to maturity. The bonds make on the bonds?
't change. If you actually
f $1,200. The bond has
cide to sell. What YTM when you
Page 8
Examples
Page 9
Examples
ual-pay bond at issuance ow much has the bond's
shareholder, has been n order to evaluate its
Assets Liab.+Eq.
Net
Page 10
Examples
40.65 2.54
40.65 3.25 -0.71
Page 11