Calculating Zero Rates, Forward Rates, and Duration
Rule: You may ask another student for help when doing this homework (although you must try to do as much of it as you can on your own). You may not take the work of another and submit it or use it as your own.
This is a difficult assignment and will take a long time to complete. It is based on chapter 4 from Hull and on lecture7 (and handout) †from Professor Evans.† You must complete this homework prior to the second exam and will use the output from this homework during the exam. Further instructions about this will be given in class.
This assignment that requires you to use an Excel Workbook labeled Calculating Zero and Forward Rates and Duration (zfd.zip). Make a copy of the workbook and keep the original as a master copy, using the new copy for this assignment.
The purpose of† this homework is to show you how the complicated formulas for are coded (using Excel as an example).†
You will want to review the material from Lecture 7 that discussed the determination of zero rates, forward rates, and duration.
This homework is done in three steps:
Step 1:† Using the worksheet labeled Zero Rates, use the default data provided and the techniques described in both the lecture and in Hull section 4.5 to calculate the Discount rate and Zero rate in the yellow cells for the components of this two year note.† Your final result should agree exactly with the result that Hull shows in Tables 4.3 and 4.4. If it does not you have made a mistake. As you do this, try to understand why you are doing it this way.† What are you calculating?† Make sure that the workbook is coded correctly (i.e. that it agrees with Hullís example) because on the exam I am going to give your different values for the price and coupon. Note: Setting up the spreadsheet to calculate the 1.5 year and 2 year maturity is, I admit, a pain in the backside. You might try to use a string of cells to calculate interim values based upon the formula provided in the lecture and then put the final resolution formula where it belongs in the zero rate column (which is what I did in my master). Regardless of how you do it, it takes time. But create something that you can rely upon for an exam because you will be asked to repeat this plugging in different coupon values and prices.
Step 2:† Using the worksheet labeled Forward Rates, where you are given 5 default zero rates, using formula 4.5 from Hull or my formula from the lecture slide labeled Calculating the Forward Rate from Zero Rates (same formula except I assume delta time in the denominator to be one, which it almost always is), calculate the four forward rates for the yellow cells.† Your final results should agree with the example that I show on the lecture slide labeled Calculating the Forward Rate from Zero Rates.† If you want to double-check your worksheet, override the default data with the data provided by Hullís example in Table 4.5.
Step 3:† Using the worksheet labeled Duration, use the default values of a 7% coupon and a 5% current yield to calculate the cash payment, present value, note value (denominator), numerator values, duration, and weight for this 5-year note. You are assuming here that this note pays interest only once per year. When entering the cash payment (obviously $7) use a formula, do not just enter a 7. Calculating the weight can be done after solving for duration and is merely provided as a check (the weights must sum to one). Compare your answer to the lecture slide entitled Calculating Duration with Excel.† After you are satisfied that you have calculated duration correctly, recalculate duration by assuming that the coupon is 5% and the current yield is 7%. This should now give you a note value that is at discount (and you should know the reason why).† What has happened to duration?
Save your completed work for the exam.