PDA

View Full Version : Bond evaluation?


zacharie101
Oct 29, 2012, 08:23 PM
I probably did these wrong but these are done using excel please tell me if I did them right


1. The A Company has 8% coupon bonds on the market that have 10 years left to maturity. The bonds make annual payments. If the YTM on these bonds is 6%, what is the current bond price?

=PRICE("01-jan-12","01-jan-22",0.08,0.06,100,1) = 114.7201741


2. The B Company has 9% coupon bonds on the market with nine years left to maturity. The bonds make annual interest payments and sells for $884.50. What is the YTM?

=YIELD("1-jan-00","1-jan-09",0.09,884.5/100,100,1) = 1.035353469


3. The C Company needs to raise capital for a plant expansion and has decided to issue 20 year zero coupon bonds to raise the money. If the required rate is 8%, what will the bonds sell for at issuance?

=PV(8%,20,0,-1000) = 214.55


4. The D Company issued 11-year bonds one year ago at a coupon rate of 8.2%. The bonds make annual payments of interest. If the YTM is 7.4%, what is the current bond price?

=PRICE("01-jan-00","01-jan-11",8.2%,7.4%,100,1) = 105.8812166


5. You are considering the purchase of the bonds of ABC on July 15, 2000. They pay semi-annual interest, will mature on December 31, 2008, have a coupon rate of 7.5% and are selling for $900. What is the YTM of these bonds?

=YIELD("15-jul-00","31-dec-08",7.5%,900/100,100,2) = .850279649


6. You want to buy some bonds of the XYZ Corp. on February 15, 2010. They mature on April 15, 2014. The coupon rate is 5% and the yield to maturity is 4.375%. What would you be willing to pay for ten of those bonds?

=PRICE("15-Feb-10","15-Apr-14",5%,4.375%,100,1) = 102.3194361

ArcSine
Oct 30, 2012, 04:31 AM
1) Correct
2) Incorrect fraction in your formula. They're selling for 884.5 per 1,000 face amount bond. Note that since they're selling at a discount, the yield must be greater than their coupon rate of 9%.
3) Correct
4) Re-check the distance between your dates. The bonds were issued a year ago.
5) Wrong fraction in your formula. Note that .85 is not a reasonable yield, as this is equivalent to 85%.
6) You're correct as to the price of a single $100 bond. But note carefully what the question is asking for. You'll also need to ascertain whether your book assumes bonds have a $100 or a $1,000 face amount (almost certainly the latter).

zacharie101
Oct 30, 2012, 02:46 PM
1) Correct
2) Incorrect fraction in your formula. They're selling for 884.5 per 1,000 face amount bond. Note that since they're selling at a discount, the yield must be greater than their coupon rate of 9%.
3) Correct
4) Re-check the distance between your dates. The bonds were issued a year ago.
5) Wrong fraction in your formula. Note that .85 is not a reasonable yield, as this is equivalent to 85%.
6) You're correct as to the price of a single $100 bond. But note carefully what the question is asking for. You'll also need to ascertain whether your book assumes bonds have a $100 or a $1,000 face amount (almost certainly the latter).

4) =PRICE("01-jan-00","01-jan-10",8.2%,7.4%,100,1) = 105.5164266 is that right?

5) =YIELD("15-jul-00","31-dec-08",7.5%,900/1000,1000,2) = 7.012348951 is that right?

6) =PRICE("15-Feb-10","15-Apr-14",5%,4.375%,100,1) = 102.3194361
then multiply by 10 102.3194361*10=1023.194361?

zacharie101
Oct 30, 2012, 03:06 PM
When you get a chance can you take a look at my other question


https://www.askmehelpdesk.com/finance-accounting/check-homework-bond-evaluation-using-excel-712775.html

ArcSine
Oct 31, 2012, 01:28 PM
You're good on (4) and (6).

Regarding (5), Excel wants you to get the syntax exactly right when it comes to expressing the prices in your function. Specifically, prices need to be expressed as percentages of face amount, and also with this important requirement: show percentages as whole numbers. For example, 15% should be entered in the function as 15, and 108% would be entered as 108 rather than 1.08.


Thus your correct syntax for (5) is

=YIELD("15-jul-00", "31-dec-08", 7.5%, 90, 100, 2)

... since the bonds are selling (according to the question) at 90% of their face (which is clearly $1,000 in this question), and we assume they mature at 100% of their face (as bonds almost always do).

I'll have a peek at your other question when I have a chance.