Question

I’m having a very hard time doing this practice problem. Can someone show me how it’s done in excel?

The president of your firm is interested in investing some of your corporation’s money in bonds. You went to the bond screener in Yahoo! Finance and narrowed the possibilities down to two bonds both maturing on August 15, 2030. You also determined the prevailing market rate on bonds similar to these two is 6%. The two bonds you selected were:

• Makin Bakin Corporation bond with a 5.25% annual coupon and a $1,000 face value.

• Froogle Corporation bond with a 7.50% annual coupon and a $1,000 face value.

In order to compose a memo to the president, you first will calculate the price of each bond (use August 15, 2018

as your purchase or settlement date). In your memo, explain why these prices are so different and why you would ever pay more than face value for a bond.

Next calculate the price of each bond at the end of each year until the bonds have one year to maturity (e.g. 11 years, 10 years, 9 years, …, 1 year), assuming interest rates remain constant. Explain in your memo what happens to the price of each bond as they will near the maturity date of August 15, 2030,and why this happens.

You also need to address in your memo the difference between interest yield and capital gains yield. In order to do so, you need to calculate the expected interest yield for each bond for each year remaining until maturity (e.g. 11 years left to maturity down to 1 year to maturity). Recall from your notes that interest yield = this year’s coupon payment/last year’s price. For the interest yield during the first year of holding the bond, from August 15, 2018 to August 15, 2019, you would use coupon interest payment/bond dollar price for August 15, 2018. This represents the interest yielded during the first year you held the bond. Then continue on for the remainder of the years.

For the capital gains yield, you will follow a similar process working from the end of the first year down to the last year of holding the bonds. Again recall from notes, that your capital gains yield is = (this year’s price-last year’s price)/last year’s price.

In your memo by putting the interest yield and capital gains yield together, you will be able to discuss the total return (yield) for each bond. From notes, you know that the total return on a bond = interest yield + capital gains yield. Make

sure to address the difference in interest yield, capital gains yield, and total yield between the two bonds.

Your boss also asked you to determine the yield to maturity on two bonds she found that both mature on December 1, 2033. On August 15, 2018, you looked up the information for the two bonds of interest. The two bonds are:

• Cincinnati Bell Telephone Company is trading for a price of $1,141 with a 7% SEMI-ANNUAL coupon.   

• Merck and Company, Inc. is the second bond. It has a current price of $989 and SEMI-ANNUAL coupon rate of 5.50%.

In your memo, indicate what the yield to maturity for each bond is. Also address if the bonds were callable on December 1, 2025 at a call price of $1,070 for the Cincinnati Bell bond and $1,055 for the Merck bond, what would be their yields to call? Indicate for which bond would you need the yield to call rather than yield to maturity and why.

Answer 1

	Makin Baking Corporation	Froogle Corporation
Coupon Rate	5.25%	7.50%
Face Value (F)	1000	1000
Annual Coupon ('C)	52.5	75
	(1000*5.25%)	(1000*7.5%)
Market Rate(i)	6%	6%
Years to Maturity (n0	12	12
(2030-2018)
Price of the Bond=	C*(1-(1/(1+i)^n))/i + F/(1+i)^n
Price of the Bond=	(52.5*(1-(1/((1+0.06)^12)))/0.06) + (1000/(1+0.06)^12)	(75*(1-(1/((1+0.06)^12)))/0.06) + (1000/(1+0.06)^12)
Price of the Bond=	937.12	1,125.76

The price of the bond is different due to the differnt coupon rates. You would pay more than the face value of the bond because the stated interest rate on the bond is higher resulting in higher interest payments than those expected by the current bond market.

Using the above formula calculating price of the bond at the end of each year. We will keep on chaning the value of n to derive the price

	Makin Baking Corporation				Froogle Corporation
	Price of the bond	Interest Yield	Capital Gains	Total Return	Price of the bond	Interest Yield	Capital Gains Yield
1 year to maturity	992.92	0.053	0.0068	0.0596	1,014.15	0.074	-    0.0130	0.0610
2 year to maturity	986.25	0.053	0.0064	0.0597	1,027.50	0.073	-    0.0121	0.0609
3 year to maturity	979.95	0.054	0.0061	0.0597	1,040.10	0.072	-    0.0113	0.0608
4 year to maturity	974.01	0.054	0.0058	0.0597	1,051.98	0.071	-    0.0105	0.0608
5 year to maturity	968.41	0.054	0.0055	0.0597	1,063.19	0.071	-    0.0098	0.0607
6 year to maturity	963.12	0.055	0.0052	0.0597	1,073.76	0.070	-    0.0092	0.0606
7 year to maturity	958.13	0.055	0.0049	0.0597	1,083.74	0.069	-    0.0086	0.0606
8 year to maturity	953.43	0.055	0.0047	0.0597	1,093.15	0.069	-    0.0081	0.0606
9 year to maturity	948.99	0.055	0.0044	0.0598	1,102.03	0.068	-    0.0075	0.0605
10 year to maturity	944.80	0.056	0.0042	0.0598	1,110.40	0.068	-    0.0071	0.0605
11 year to maturity	940.85	0.056	0.0040	0.0598	1,118.30	0.067	-    0.0066	0.0604
12 year to maturity	937.12	0.056	-	0.0560	1,125.76	0.067	0	0.0666
Interest Yield = this year's coupon payment /last year's price
Capital Gains Yield = (this year's price-last year's price)/last year's price
Total Return = Interest Yield + Capital Gains Yield

The price of the bond with lower interest rate increases as it nears maturity and the price of the bond with higher interest rate reduces as it nears maturrity. This is because at maturity the price of the bond will be equal to its face value. Bond with lower interest rate has been issued at a discount anf Bonds with higher interest rate has been issued at a premium.

Interest yield is the interest earned on the coupon.

Capital Gain yield is the gain due to change in price

Total Yield is the sum of Interest Yield and Capital Gain yield

	Cincinnati Bell Telephone	Merck & Company
Coupon Rate	7% semi annual	5.50% semi annual
Price (P)	1141	989
Face value (F)	1000	1000
Years to Maturity (n)	15	15
Coupon ('C)	70	55
YTM=	(C+ (F-P)/n)/(F+P)/2
YTM=	(70 + (1000-1141)/15)/(1000+1141)/2	(55+((1000-989)/15))/((1000+989)/2)
	0.057	0.056

	Cincinnati Bell Telephone	Merck & Company
Coupon Rate	7% semi annual	5.50% semi annual
Call Price	1070	1055
Face value	1000	1000
Years to Call	7	7
YTC=	(C+ (F-P)/n)/(F+P)/2
YTC=	(70+((1000-1070)/7))/((1000+1070)/2)	(55+((1000-1055)/7))/((1000+1055)/2)
	0.058	0.046

If the bonds are callable then, Cincinnati Bell Telephone company is the bond that will need yield to call rather than yield to maturity as its yield to call is higher than yield to maturity.