Bond Valuation

Assume that a 10-year, 2.5% annual coupon bond with semiannual (two periods per year) payments has a par value of $1,000. Assume the bond can be called at the five-year mark (i.e., in 5 years) at a call price of $1,100. The bond currently sells for $950. Employ the Excel file to answer the following questions:

Part 1: Bond Yield

1. Fill in and calculate the basic bond data. Note that the periods to maturity is equal to the years to maturity times the periods per year; the periods until callable is equal to years until callable times the periods per year; the periodic coupon payment is equal to the annual coupon payment divided by the periods per year.

2. Using the Excel Rate function, calculate the periodic Yield to Maturity. Using the Excel Rate function, calculate the annualized Yield to Maturity.

3. Calculate the current yield. The current yield is defined as the Annual Coupon payment divided by Current Price.
4. Calculate the Capital Gain or Loss Yield. Capital Gain or Loss Yield = Annualized YTM - Current Yield.
5. Using the Excel Rate function, calculate the periodic Yield to Call. Using the Excel Rate function, calculate the annualized Yield to Call.

A) Basic Bond Data Years to maturity: Periods per year: Periods to maturity: Annual coupon rate: Par value: Annual coupon payment: Periodic coupon payment: Current price: Call price: Years until callable: Periods until callable: B) Yield to Maturity Periodic YTM Number of Periods (periods to maturity) Payment (periodic coupon payment) PV (current price of bond) FV (par value of bond) (See https://support.office.com/en-us/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE) Peridodic YTM= Annualualized YTM Number of Years (years to maturity) Payment (annual coupon payment) PV (current price of bond) FV (par value of bond) (See https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE) Annualized YTM = C) Current Yield Annual Coupon Payment = Current Price = Current yield = D) Capital Gain or Loss Yield Annualized YTM = Current Yield = Capital Gain or Loss yield = E) Yield to Call Periodic YTC Number of Periods (periods untill callable Payment (periodic coupon payment) PV (current price of bond) FV (call price of bond) (See https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE) Peridodic YTC = Annualized YTC Number of Years (years untill callable) Payment (annual coupon payment) PV (current price of bond) FV (call price of bond) (See https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE) Annualized YTC =