Use Excel to solve the following problems

Problem 1: The Basics of Bond Valuation

A bond with $1000 face value, 9% coupon rate, 20 years to maturity, interest rate of 15%, and quarterly coupon payments.

A)

Calculate the price and duration of the bond

B)

As interest rate deviates from 15%, how does the duration model predict price change? How does the true price change? Plot a chart to illustrate. Let interest rate vary within [0%, 30%] with maximum 0.5% increments. What is the difference between the real price and predicted price called?

C)

How will the bond price change as maturity changes? Plot a graph to illustrate. Let maturity vary within [0 year, 30 years] with maximum 0.5 year increments.

D)

Plot a surface chart illustrating how bond price will change as interest rate and maturity change. Let interest rate vary within [0%, 30%] with maximum 0.5% increments. Let maturity vary within [0 year, 30 years] with maximum 0.5 year increments. How does price change with maturity when interest rate is low compared to when interest rate is high?

Problem 2: Repricing and Duration Gap

Given the balance sheet of an FI in the provided excel template, answer the following questions.

A)

What is the FI's repricing gap if the planning period is six months?

B)

What is the FI's repricing gap if the planning period is one year?

C)

What is the FI's duration gap?

D)

How will the net interest income change over the next six months if interest rates on assets decrease 50 basis points and on liabilities decrease 35 basis points?

E)

What is the change in equity value over the next year if interest rates on assets decrease 35 basis points and on liabilities decrease 50 basis points?

F)

What is the change in equity value if market interest rates decrease 50 basis points?