Diana Voorman, portfolio manager at Gigantico Inc. is looking at two different b

Question

Diana Voorman, portfolio manager at Gigantico Inc. is looking at two different bonds and intends to purchase only one of them for her firm's intermediate bond portfolio. The first bond is from The Zelio Film Company.

* The ask price is 102.50 firm.

* The bond was issues on November 15th, 2010 and matures on November 15th, 2025.

* The bond has a coupon rate of 9.25% payable on a semiannual basis.

* The face value of the bonds is $10,000 and the required rate of return for similar bonds is 9.10% as determined by Diana's investment committee.

* If purchased, the bond would settle on May 1st, 2017.

a) Analyze the bond and calculate its respective intrinsic values. Use the Excel "Formulas" then "Financial" drop down menu, use the PRICE function to solve for the intrinsic value.

b) With the expected settlement date of May 1st, 2017, calculate the amount of accrued interest that Gigantico would pay upon acquiring the bond. Alas, the Excel function here requires manipulating the date format and a workaround, so the recommendation is to manually calculate being aware of the "Basis" information and when the bond was issued, or last paid interest.

c) Evaluate the bonds and determine the yield to maturity of both bonds based on the current price for the bonds.

Provide Excel syntax (formulas)

Explanation / Answer

The part a) of the question deals with calculating the intrinsic value of the bond issued by Zelio Film Company.

The following information has been given about the bond:

1.Market price(Ask Price) M1= 102.5

2.Coupon rate C = 9.25% payable semi annually this means 9.25/2 = 4.625% would be paid every 6 months

3 The required rate of return kd = 9.10%

4 the date of purchase will be 1st May 2017

interest payments would happen on 15th of May and 15th of November every year

This implies that the next cashflow will occur on 15th may 2017 and then every 6 months thereafter till 15th Nov 2025

Using the price function of Excel the following syntax will have to be inserted

1.Settlement : The settlement date or the date of purchase in this case 1st May 2017

2 Maturity : the maturity date in this case 15th Nov 2025

3.Rate : the coupon rate in this case it will be 9.25% as annual coupon rate is asked

4 Yld : the annual yield which in this case will be 9.10%

5 Redemption : is the securities redemption value per 100 nominal since this is not specified it is assumed to be at par i.e 100

6 Frequency : the number of coupon payments per year. Here it comes to 2 as coupons will be paid semi annually

7 Basis : this specifies the basis of calculating the days since nothing is specified we will mention 1 which means actual days are counted.Other basis values are 0,2,3,4 which specify other build in day counting conventions. This can also be omitted in which case 0 will be taken as the default value

putting the above specified values in excel will return the price of the bond as 100.87 multiplying this by $100 as the face value of the bond is $10000

the answer to the question in part a) is $10087

Note : 1. the information about the ask price which is given in the question would be used in part c) of the question.

2. The answer would not have changed if the basis of 0 was used.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.