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.