|
New Member
|
|
Oct 29, 2012, 08:37 PM
|
|
Check my homework on bond evaluation using excel
I probably did these wrong but these are done using excel please tell me if I did them right
Problem #1- On August 1, 2004, you are offered the following bond:
Face value: $1,000
Coupon rate: 12%
Coupon payments: Once a year starting on August 1, 2005
Maturity date: August 1, 2012
Bond price: $1,252.00
Compute the bond’s yield to maturity (YTM)
=YIELD("01-Aug-04","01-Aug-12",12%,1252/1000,1000,1) = 9.584713065
Problem #2- On September 10, 2003, you are offered the following bond:
Face value: $1,000.00
Coupon rate: 12%
Coupon payments: Once a year starting on August 1, 2004
Maturity date: August 1, 2012
Bond price: $1,252.00
Compute the bond’s yield to maturity (YTM)
=YIELD("10-sep-03","01-Aug-12",12%,1252/1000,1000,1) = 5.786300818
Problem #3- You have been offered a U.S. Treasury bill on July 15. The face value of the bill is $10,000 and the price is $9,925. The bill matures on January 14.. Compute the yield on the bill.
I dont know why but I get #NUM when I do:
=YIELDDISC("15-jul","14-jan",9925/10000,10000)
15-Jul - 14 Jan = 183 days between
Rate is 2%
=RATE(183/365,0,9925,-10000,1)
Problem #4- On February 26, 2001 an 8.2% Duke Energy Corp. bond maturing January 15, 2007 is priced at 103.790 per face value (this price does not include the accrued interest). The bond was originally issued in 1992. The bond pays interest semiannually, on January 15th and July 15th of each year. Compute the accrued interest and the yield to maturity of the bond.
Today's date 26-Feb-01
Last coupon date 15-Jan-92 I made up the Jan 15
Next coupon date 16-Jan-07
Days since last coupon 3330 Todays date - Last coupon date
Days between coupons 5480 Next coupon date - Last coupon date
Semi-annual coupon =PRICE(B14,B16,8.2%,B21,100,2) = 1.0379
Accrued interest .630694708 3330/5480*1.0379
YTM 5.67226615 =YIELD(26-Feb-01,16-Jan-07,8.2%,103.79/100,100,2)
Problem #5- The ABC Corp. needs to raise funds to finance a plant expansion, and it has decided to issue 20-year zero coupon bonds to raise the capital. If YTM’s are currently 9%, what will these bonds sell for at issuance?
=PV(9%,20,0,-1000) = 178.43
Problem #6- HIJ has 10 % coupon bonds with 9 years left to maturity. The bonds make annual interest payments. If the bond currently sells for $1075.25, what is the YTM?
=YIELD("01-jan-00","01-jan-09",10%,1075.25,1000,1) = .001572991
Problem #7- The EFG Corp. issued 12-year bonds 2 years ago at a coupon rate of 7.8%. The bonds make semi-annual payments. If the bonds sell at 108 (108% of par) what is the YTM?
=YIELD("01-jan-00","01-jan-12",7.8%,108/100,100,2) = 7.22222
Problem #8- what would you pay for a bond that pays an annual coupon of $45, has a face value of $1,000, matures in 11 years, and has a YTM of 10%?
=PRICE("01-jan-00","01-jan-11",45/1000,10%,1000,1) = 379.721674
|