Problems Using Excel

1.
Complete the table in the
spreadsheet “Problem 1” to graph the prices of a zero-coupon
bond, and two coupon bonds, one with a coupon rate of 4% and the other with a
coupon rate of 12%, over time at a yield to maturity of 8%. All three
bonds have the same maturity date (30 years from today: 01/01/2020) and same
face value ($5,000.00).

a.
What explains the fact that the
zero-coupon bond curve in the graph is, at any point in time, below the curve
of the coupon bonds?

2.
From the information given in the
spreadsheet “Problem 2”, use a data table to show how the price of a 30-year
maturity bond changes for different coupon rates and different yields to
maturity. Use this table as an input for table 2, which shows percentage
changes in price (price sensitivity), to illustrate that interest rate risk is
inversely related to the bond’s coupon rate: “Prices of low-coupon bonds are
more sensitive to changes in interest rates than prices of high-coupon bonds”.

3.
Show in the spreadsheet “Problem 3”,
the profit for an option strategy that consists of a long position in one
European call option and a long position in two European puts, all with the
same expiration date and strike price. The strike price is $60 and the price
(per share) of both call and put is $4.

a.
Graph the payoff for this strategy
in excel for different spot prices as defined in the table. Explain, by looking
at the diagram, what is the rationale behind this strategy. What do you think
is the goal of an investor that adopts this particular option strategy?

4.
Complete the spreadsheet “Problem
4”. It compares a 12.75 year maturity zero-coupon bond selling at a yield to
maturity of 8% (effective annual yield) with a convexity of 150.3; with a 30-year
maturity 6% coupon bond making annual coupon payments and also selling at a
yield to maturity of 8% with a considerably higher convexity of 231.2. Find the
prices, durations, modified durations, and the corresponding percentage changes
in prices – actual and using the duration with convexity rule – (The formula of
the latter is shown in the spreadsheet!)

a.
Compare the performance of the two
bonds in the two scenarios, one involving an increase in rates (to 10%), the
other a decrease (to 6%). Based on the comparative investment performance,
explain the attraction of convexity.

b.
In view of your answer to (a), do
you think it would be possible for two bonds with equal duration but different
convexity to be priced initially at the same yield to maturity if the yields on
both bonds always increased or decreased by equal amounts, as in this example?
Would anyone be willing to buy the bond with lower convexity under these
circumstances?

Things to keep in mind

·
Make sure to understand what the
question is about, to identify the information that has been given to you, and
to recognize what the question or problem is asking you to find (or
solve).

·
To get full points, you must show
all your work. In excel, it is necessary to work with references to
well-identified cells from your own file. Don’t use as an input any typed
number in any excel formula.

·
Be organized in the way you present
your calculations and answers. Think of the person who is going to read your file.
Bear in mind that only in this manner it will be easier for you to check your
work.

Get Higher Grades Now

Tutors Online