You work for a bond fund. Your boss has asked you to value the following bonds for the portfolio. Construct an Excel worksheet to answer the following questions. Clearly label the solution to each question.

The two bonds in question:

Bond X: A zero coupon bond with a face value of $1,000 and exactly 8 years remaining that is selling for $560

Bond Y: A 6% coupon bond with a face value of $1,000 selling for $650 with coupon payments made every 6 months. The last coupon payment was made yesterday, and the bond matures in 4 years, 6 months

Now assume that your fund buys both of these bonds today. You expect that interest rates will rise to 10% for both bonds in 2 years. If you sell at the end of 2 years (right after the coupon payment for Bond Y), what is the return on your investment, expressed as the effective annual rate?