Im having a very hard time doing this practice problem. Can someone show me how its done in excel?

The president of your firm is interested in investing some of your corporations 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 years coupon payment/last years 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 years price-last years price)/last years 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.