March 14, 2014

Bonds Pricing in MS Excel

Please help. I got the assignment wrong in my first attempt. Here is the question:
“Five years ago, Highland, Inc. issued a corporate bond with an annual coupon of $6,000, paid at the rate of $3,000 every six months, and a maturity of 10 years. The par (face) value of the bond is $1,000,000. Recently, however, the company has run into some financial difficulty and has restructured its obligations.Today‚Äôs coupon payment has already been paid, but the remaining coupon payments will be postponed until maturity. The postponed payments will accrue interest at an annual rate of 5% per year and will be paid as a lump sum amount at maturity along with the face value. The discount rate on the renegotiated bonds, now considered much riskier, has gone from 7% prior to the re-negotiations to 15% per annum with the announcement of the restructuring. What is the price at which the new renegotiated bond should be selling today? (Recall that the compounding interval is 6 months and the YTM, like all interest rates, is reported on an annualized basis.)”

1. We create a proper time-line for 10 years divided into 20 periods because of the 6-months nature of coupons
2. We determine the start of the default period i.e. when the company is unable to pay the coupons
3. We treat the final lumpsum payment as coupons using 10 periods and 5%/2 as interest rate and calculate their future value
4. We add the $1Million face value to the above value in point 3
5. Finally we calculate the Present Value or PV of $1,033,610.15 6. The result is $501,501.37 which was confirmed as correct.

Watch the training video below to learn about the pricing of bonds in MS-Excel:

Watch the video on YouTube