Download FIN4303 Project (PDF) and answer the questions using Excel. An example for each problem and an Excel template (XLS) is provided to you. Read through Bond Example (XLS) and Interest Rate Risk Example (PDF)before you attempt Problems 1 and 2.

FIN 4303 Project

Answer the questions below using Excel. An excel template and an example for Problem 1 is provided to you. This project weighs 12.5% of you final grade. You must submit the project by uploading an excel file on Canvas by the end of June 14th, no late submission is accepted for this project.

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. (10 pts)

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? (30 pts)

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. (10 pts)

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? (30 pts)

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? (5 pts)

B)

What is the FI's repricing gap if the planning period is one year? (5 pts)

C)

What is the FI's duration gap? (5 pts)

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? (10 pts)

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? (10 pts)

F)

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