Q1 - Please help with the following practice exam. Prof provided the answer key but REQUIRES showing the solution/analysis using Excel functions/formulas STEP-BY-STEP. Thank you.

Answer Key:

P₀ = $1,053.02 P₁ = $1,050.26 P₃ = $1,044.22 P₈ = $1,025.46 P₁₂ = $1,005.73 P₁₃ = $1,000

Question:

Bond X is a premium bond making semiannual payments. The bond pays a coupon rate of 6.8 percent, has a YTM of 6.2 percent, and has 13 years to maturity. Bond Y is a discount bond making semiannual payments. This bond pays a coupon rate of 6.2 percent, has a YTM of 6.8 percent, and also has 13 years to maturity. The bonds have a par value of $1,000.

• What is the price of each bond today?
• If interest rates remain unchanged, what do you expect the price of these bonds to be 1 year from now?
• In 3 years?
• In 8 years?
• In 12 years?
• In 13 years?
• Comment on the changes in prices of both bonds as they approach maturity.