Assume that a 10-year, 6% coupon bond with semiannual payments has a par value of $1,000 and may be called in 5 years at a call price of $1,050. The bond currently sells for $980. Employ the excel file to answer the following questions: Part 1: Bond Yield A) Calculate the Periods to Maturity, Periodic Payment, and Periods until Callable (8 Points) B) Calculate the periodic Yield to Maturity using the Excel function and the Annualized Yield to Maturity. The Annualized Yield is equal to the periodic YTM times the number of periods (8 Points) C) Calculate the Annual Coupon Payment, Current Price, and the Current Yield. The current yield is defined as Annual Coupon payment/Current Price (8 Point) D) Calculate the Capital Gain or Loss Yield. Capital Gain or Loss Yield = Annualized YTM - Current Yield (8 Points) E) Calculate the Periodic Yield to Call using the Excel function and the Annualized Yield to Call (8 Points) Part 2: Relation between Bond Value and Market Interest Rate F) Calculate the periodic rate and use the Excel function to calculate the present value of the callable and noncallable bonds (points) G) Fill in the table values and employ the Excel function to calculate bond present values for different interest rates (8 Points) H) Copy present values from the previous step and fill in the table. A bond will only be called if it is cheaper for the issuer. I) Graph the actual bond present value (y-axis) against the annual market interest rate (x-axis). Label the y-axis, x-axis, and chart title (8 Points)

Part 1:

A) Basic Input Data

Years to maturity:

Hint: N

Periods per year:

Hint: M

Periods to maturity:

Hint: (N)(M)

Coupon rate:

6%

Par value:

$1,000

Periodic payment:

Hint: Annual coupon payment / M

Current price

$980

Call price:

$1,050

Years until callable:

5

Periods until callable:

Hint: (Years to call)(M)

B) Yield to Maturity

Number of Periods

Payment

PV

FV

(See https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE)

Peridodic YTM =

Hint: Employ the function "=Rate(Nper,Pmt,-PV,FV)"

Annualized YTM =

Hint: (Periodic YTM)(M)

C) Current Yield

Annual Coupon Payment =

Current Price =

Current yield =

Hint: Annual Coupon payment / Current Price

D) Capital Gain or Loss Yield

Annualized YTM =

Current Yield =

Capital Gain or Loss yield =

Hint: Capital Gain or Loss Yield = Annualized YTM - Current Yield

E) Yield to Call

Number of Periods

Payment

PV

FV

(See https://support.office.com/en-US/article/RATE-function-9F665657-4A7E-4BB7-A030-83FC59E748CE)

Peridodic YTC =

Hint: Employ the function "=Rate(nper,pmt,-pv,fv)"

Annualized YTC =

Hint: (Periodic YTC)(M)

Part 2)

F) Bond Value

Annual Market Rate:

9%

Periodic Market Rate:

Hint: Periodic interest rate = Annual market rate / M

PV of noncallable bond:

Periodic Rate

Nper

Pmt

FV

Present Value of bond in 10 years

Hint: Employ the function "PV(Rate, Nper, -Pmt, -FV)"

(See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415)

PV of callable bond:

Periodic Rate

Nper

Pmt

FV

Present value of bond if it is called in 5 years:

Hint: Employ the function "PV(Rate, Nper, -Pmt, -FV)"

(See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415)

G) Bond Value Table

Noncallable Bond:

Annual Market Rate

Periodic Market Rate

Number of Periods

Periodic Payment

Future Value (Par)

Present Value

Hint: Employ the function "PV(Rate, Nper, -Pmt, -FV)"

0%

2%

4%

6%

8%

10%

12%

14%

16%

Callable Bond:

Annual Market Rate

Periodic Market Rate

Number of Periods

Periodic Payment

Future Value (Call Price)

Present Value

0%

2%

4%

6%

8%

10%

12%

14%

16%

H) Bond Value Table

Annual Market Rate

PV of Noncallable Bond

PV of Callable Bond

Will the Bond be Called?

Actual Present Value

0%

2%

4%

6%

8%

10%

12%

14%

16%

I) Graph